SELECT

CASE

WHEN block_timestamp >= '2022-11-07' THEN 'Post-FTX collapse'

ELSE 'Before FTX collapse'

END AS label,

block_timestamp::DATE AS date,

COUNT(*) AS swaps,

COUNT(DISTINCT origin_from_address) AS users,

SUM(NVL(amount_in_usd, amount_out_usd)) AS usd_volume

FROM

ethereum.core.ez_dex_swaps

WHERE

block_timestamp >= '2022-07-01' -- 5 months

GROUP BY

1, 2

),

cumulative AS (

SELECT

*,

SUM(swaps) OVER(ORDER BY date) AS cumulative_swaps,

SUM(usd_volume) OVER(ORDER BY date) AS cumulative_usd_volume,

AVG(swaps) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS swaps_7D_ma,

AVG(users) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS users_7D_ma,

AVG(usd_volume) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS usd_volume_7D_ma

FROM

daily_swap_data

SELECT

*

FROM

cumulative