RECORDED_HOUR::date as day,

avg(close) as price

from solana.core.fact_token_prices_hourly

where symbol ='SOL'

group by 1)

BLOCK_TIMESTAMP::date as date,

count(distinct tx_id) as no_hacks,

count(distinct SIGNERS[0]) as no_victims,

count(distinct INNER_INSTRUCTION:instructions[0]:parsed:info:destination) as wallets_collected,

sum(INNER_INSTRUCTION:instructions[0]:parsed:info:lamports/1e9) as volume_sol,

sum((INNER_INSTRUCTION:instructions[0]:parsed:info:lamports/1e9)*price) as volume_usd,

avg((INNER_INSTRUCTION:instructions[0]:parsed:info:lamports/1e9)*price) as avg_volume_usd,

sum(no_victims) over (order by date) as total_victims,

sum(volume_sol) over (order by date) as total_SOL,

sum(volume_usd) over (order by date) as total_USD

from solana.core.fact_events s left join t1 a on s.BLOCK_TIMESTAMP::date=a.day

where PROGRAM_ID='3VtjHnDuDD1QreJiYNziDsdkeALMT6b2F9j3AXdL4q8v'

and SUCCEEDED='TRUE'

group by 1 having volume_usd is not null

order by 1