select trunc(hour, 'day') as day,

symbol, avg(price)

from ethereum.core.fact_hourly_token_prices

group by 1, 2

select

day,

case when day >= '2022-11-08' then 'After FTX/Alameda News' else 'Before FTX/Alameda News' end as timespan,

case origin_function_signature when '0xc6878519' then 'ETH -> Sol' else 'Sol -> ETH' end as type,

count(distinct tx_hash) as txn_count,

count(distinct a.from_address) as users_count,

txn_count / users_count as tx_per_user,

sum(amount_usd) as volume_usd,

volume_usd / users_count as volume_per_user,

avg(amount_usd) as volume_usd_avg,

median(amount_usd) as volume_usd_median,

sum(txn_count) over (order by day asc) as comulative_txn_count,

sum(users_count) over (order by day asc) as comulative_users_count,

sum(volume_usd) over (order by day asc) as comulative_volume_usd,

sum(volume_usd_avg) over (order by day asc) as comulative_volume_usd_avg

from ethereum.core.fact_transactions a

join ethereum.core.ez_token_transfers b using (tx_hash)

join prices

on trunc(block_timestamp, 'day') = prices.day

and prices.symbol = b.symbol

where 1 = 1

and origin_function_signature in ('0xc6878519', '0x0f5287b0','0x9981509f')

and to_address = '0x3ee18b2214aff97000d974cf647e7c347e8fa585'

and substring(input_data, 202, 1) = '1'

and status = 'SUCCESS'

group by day, type, timespan

order by day