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,

CASE

WHEN event_name = 'EnterMoon' THEN 'Moon'

WHEN event_name = 'EnterDoom' THEN 'Doom'

END AS prediction_label,

'ETHUSD' AS Type,

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 ('EnterMoon', 'EnterDoom')

AND block_timestamp >= '2024-02-29'

AND tx_status = 'SUCCESS'

date,

prediction_label,

SUM(Amount_USD) AS total_usd

FROM Yologames