SELECT

TRUNC(hour, 'day') AS day,

AVG(price) AS price

FROM blast.price.ez_prices_hourly

WHERE token_address = LOWER('0x4300000000000000000000000000000000000004')

GROUP BY day

),

Yologames AS (

SELECT

DATE_TRUNC('day', block_timestamp) AS date,

tx_hash,

decoded_log:amount AS Amount,

Amount / 1e18 AS Amount_ETH,

decoded_log:sender AS User,

(Amount_ETH * E.price) AS Amount_USD,

FROM blast.core.ez_decoded_event_logs b

JOIN ETHprice E

ON DATE_TRUNC('day', block_timestamp) = E.day;

WHERE contract_address = '0x693b37a9859ce9465fb2aadeb03811a26a0c37c0'

AND event_name IN ('Claim')

AND block_timestamp >= '2024-02-29'

AND tx_status = 'SUCCESS'

date,

COUNT(DISTINCT tx_hash) AS total_play,

SUM(Amount_USD) AS total_usd,

SUM (total_usd) OVER (ORDER BY date) AS CumulAmountUSD

FROM Yologames

GROUP BY

date

ORDER BY

date DESC;