SELECT

DATE_TRUNC('day', hour) AS day,

AVG(price) AS price

FROM

solana.price.ez_prices_hourly

WHERE

token_address = '2zMMhcVQEXDtdE6vsFS7S7D5oUodfJHE8vd1gnBouauv'

GROUP BY

day

),

pengu AS (SELECT

ft.tx_id,

fe.instruction:accounts[4] AS claimer,

ft.block_timestamp,

ft.amount,

dap.price AS avg_daily_price,

ft.amount * dap.price AS usd_value

FROM

solana.core.fact_events fe

INNER JOIN

solana.core.fact_transfers ft

ON fe.tx_id = ft.tx_id

AND fe.block_timestamp = ft.block_timestamp

LEFT JOIN

daily_avg_price dap

ON DATE_TRUNC('day', ft.block_timestamp) = dap.day

WHERE

fe.program_id = 'CUEB3rQGVrvCRTmyjLrPnsd6bBBsGbz1Sr49vxNLJkGR'

--AND fe.tx_id = '4gWXLifBgPdHqcisbp411Lt8yoDCDWir3UQ5AJLBNjUAvAAbQVmxDPfxMsksBzyuFhtUcFjtWfCbRi6MGj9Pmxcb'

AND fe.succeeded = TRUE

AND fe.block_timestamp >= '2024-12-17'

AND ft.mint = '2zMMhcVQEXDtdE6vsFS7S7D5oUodfJHE8vd1gnBouauv'

AND ft.tx_to = fe.instruction:accounts[4]