with trades as (
select
date(block_timestamp) as day,
sum(ETH_VALUE) as amt,
count(distinct TX_HASH) as trades_cnt,
count(distinct FROM_ADDRESS) as sellers_cnt,
count(distinct TO_ADDRESS) as buyers_cnt
from
ethereum.core.fact_transactions
where
block_timestamp > CURRENT_DATE-90
group by 1
)
select
day,
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