date_trunc('day', block_timestamp)::date as day,

count(distinct(tx_id)) as tx_num,

avg(price_usd) as avg_price,

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

(total_amount / count(distinct(block_height))) 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 flow.core.fact_prices a

join flow.core.fact_transactions b

on block_timestamp::date = timestamp::date

where 1 = 1

and block_timestamp::date > '2022-10-01'

and token_contract = 'A.1654653399040a61.FlowToken'

group by day

order by day asc