with trades as (
select
date(block_timestamp) as day,
sum(deposit/1e24) as amt,
count(distinct tx_hash) as trades_cnt,
count(distinct tx_signer) as sellers_cnt,
count(distinct tx_receiver) as buyers_cnt
from
near.core.fact_transfers
where
block_timestamp > CURRENT_DATE-7
group by 1
)
select
day,
case
when day between '2022-11-06' and '2022-11-11' 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