SELECT

e.block_timestamp,

e.tx_id,

amount,

upper(label) AS label,

signers [0] AS user

FROM

solana.core.fact_events AS e

JOIN solana.core.fact_transfers AS t ON e.tx_id = t.tx_id

AND e.block_timestamp = t.block_timestamp

JOIN solana.core.dim_labels ON program_id = address

WHERE 1 = 1

AND mint = 'mSoLzYCxHdYgdzU16g5QSh3i5K3z3KZK7ytfqcJm7So'

AND succeeded = True

AND label != 'solana'

AND label != 'saga monkes'

AND e.block_timestamp >= Current_date - Interval '31 days'

),

price AS (

SELECT

price

FROM crosschain.price.ez_prices_hourly

WHERE token_address = 'mSoLzYCxHdYgdzU16g5QSh3i5K3z3KZK7ytfqcJm7So'

ORDER BY hour DESC

LIMIT 1

label AS protocol,

COUNT(DISTINCT tx_id) AS transactions,

SUM(amount) AS mSOL_amount,

to_varchar((mSOL_amount * p.price), '999,999,999,999,999.00') AS amount_usd,

COUNT(DISTINCT user) AS users

FROM protocol

cross JOIN price p