with price as (

select

date_trunc(day, HOUR) as day,

SYMBOL,

avg(PRICE) as avg_price

from base.price.ez_hourly_token_prices

where SYMBOL in ('DAI', 'USDbC', 'cbETH')

group by 1,2

union all

select

date_trunc(day, HOUR) as day,

SYMBOL,

avg(PRICE) as avg_price

from ethereum.price.ez_hourly_token_prices

where SYMBOL in ('WETH', 'MAV')

group by 1,2

date_trunc(day, block_timestamp) as date

,count(distinct TX_HASH) as "Swap Count"

,sum("Swap Count") over(order by date) as "Cum. Swap Count"

,count(DISTINCT ORIGIN_FROM_ADDRESS) as "User Count"

,sum("User Count") over(order by date) as "Cum. User Count"

,"Swap Count" / "User Count" as "Avg # Swap per User"

,sum(case

when AMOUNT_IN_USD is not null then AMOUNT_IN_USD

when AMOUNT_IN_USD is null

and AMOUNT_OUT_USD is not null then AMOUNT_OUT_USD

else (AMOUNT_IN * avg_price)

end) as "Swap Volume"

,"Swap Volume" / "User Count" as "Swap Volume per User"

,"Swap Volume" / "Swap Count" as "Swap Volume per TX"