with trades as (
select
date(block_timestamp) as day,
sum(AMOUNT/1e24) as amt,
count(distinct TX_ID) as trades_cnt,
count(distinct SENDER) as sellers_cnt,
count(distinct RECIPIENT) as buyers_cnt
from
flow.core.ez_token_transfers
where
block_timestamp > '2022-11-01'
group by 1
)
select
day,
case
when day between '2022-11-06' and '2022-11-14' then 'FTX Crisis'
when day between '2022-11-20' and '2022-11-22' then 'Recent Price Drop'
else 'Other'
end as day_label,
amt as "Daily Volume",
avg("Daily Volume") over (order by day asc rows between 7 preceding and current row) as "7-Day Moving Average",
sellers_cnt/buyers_cnt as ratio
from
trades
order by
day asc