BLOCK_TIMESTAMP::date as date,

--concat(SYMBOL_IN, '-->', SYMBOL_OUT) as "Swap Pairs",

case

when BLOCK_TIMESTAMP between '2022-11-02' and '2022-11-07' then 'CoinDesk report'

when BLOCK_TIMESTAMP between '2022-11-07' and '2022-11-09' then 'Binance decided to sell its FTT holding'

when BLOCK_TIMESTAMP between '2022-11-09' and '2022-11-11' then 'Binance announced plans to acquire FTX but...'

when BLOCK_TIMESTAMP like '2022-11-11%' then 'FTX filed for Chapter 11 bankruptcy protection'

when BLOCK_TIMESTAMP between '2022-11-12' and '2022-11-14' then 'FTX Hacked'

when BLOCK_TIMESTAMP like '2022-11-14%' then 'Softbank wrote down a $100 million investment in FTX'

else 'Not Special News'

end as status,

count(distinct tx_hash) as "# TXs",

avg("# TXs") over(order by date rows between 3 preceding and current row) as "3D MA - # TXs",

sum(AMOUNT_IN_USD) as "Volume-USD",

avg("Volume-USD") over(order by date rows between 3 preceding and current row) as "3D MA - Volume-USD",

count(distinct ORIGIN_FROM_ADDRESS) as "# Users",

avg("# Users") over(order by date rows between 3 preceding and current row) as "3D MA - # Users"

from optimism.velodrome.ez_swaps

where BLOCK_TIMESTAMP >= CURRENT_DATE - 17

group by 1,2