date_trunc('day', block_timestamp) as day,

count(distinct(tx_id)) as tx_num,

avg(close) as avg_price,

avg_price * sum(fee/ pow (10, 9)) as total_amount,

(total_amount / count(distinct(block_id))) as avg_amount_per_block,

(total_amount / tx_num) as avg_amount_per_txn,

sum(total_amount) over (order by day asc) as comulative_total_amount,

sum(tx_num) over (order by day asc) as comulative_tx_num,

avg (avg_price) over (order by day rows between 6 preceding and current row) as moving_avg_7_days,

avg (avg_price) over (order by day rows between 29 preceding and current row) as moving_avg_30_days

from solana.core.fact_token_prices_hourly

join solana.core.fact_transactions

on block_timestamp::date = recorded_hour::date

where 1 = 1

and day > '2022-10-01'

and symbol = 'SOL'

group by day

order by day asc