SELECT

trunc(hour, 'day') AS day,

AVG(price) AS price

FROM

solana.price.ez_prices_hourly

WHERE

token_address = 'SonicxvLud67EceaEzCLRnMTBqzYUUYNr93DBkBdDES'

GROUP BY

day

),

claim_txs AS (

SELECT block_timestamp, tx_id, tx_to AS claimer, amount

FROM solana.core.fact_transfers

WHERE tx_from = 'BU7ig4oPEUhqXhGzuPjfBE5xwETqFtuBk352TXQaaXK7'

and mint = 'SonicxvLud67EceaEzCLRnMTBqzYUUYNr93DBkBdDES'

SELECT

DATE_TRUNC('hour', ct.block_timestamp) AS date,

COUNT(ct.tx_id) as total_claiming,

SUM(total_claiming) OVER (ORDER BY DATE) AS cum_claiming,

COUNT(DISTINCT ct.claimer) as total_claimer,

SUM(ct.amount * np.price) AS usd_value,

SUM(ct.amount) AS amounts,

SUM(SUM(ct.amount * np.price)) OVER (ORDER BY DATE) AS cum_usd

FROM

claim_txs AS ct

LEFT JOIN

meprice AS np

ON

DATE_TRUNC('day', ct.block_timestamp) = np.day

GROUP BY date

order by date desc