CASE WHEN date_trunc('day',block_timestamp) between '2022-11-02' AND '2022-11-07' THEN 'before'

WHEN date_trunc('day',block_timestamp) between '2022-11-09' AND '2022-11-15' THEN 'after'

ELSE 'during' END as period,

COUNT(DISTINCT a.tx_hash) as n_txns,

avg(a.tx_fee) as avg_tx_fee,

COUNT (DISTINCT a.from_address) as n_users,

avg(matic_value*b.eth_matic) as avg_eth_value

FROM polygon.core.fact_transactions a

LEFT JOIN (

SELECT date_trunc('day',block_timestamp) as day,

median(amount_in/amount_out) as eth_matic

FROM ethereum.core.ez_dex_swaps

WHERE symbol_in = 'WETH' and symbol_out = 'MATIC'

GROUP BY day

) b

ON date_trunc('day',a.block_timestamp) = b.day

WHERE date_trunc('day',block_timestamp) between '2022-11-02' and '2022-11-15'

GROUP BY period

SELECT 'Ethereum' as label,

CASE WHEN date_trunc('day',block_timestamp) between '2022-11-02' AND '2022-11-07' THEN 'before'

WHEN date_trunc('day',block_timestamp) between '2022-11-09' AND '2022-11-15' THEN 'after'

ELSE 'during' END as period,

COUNT(DISTINCT tx_hash) as n_txns,

avg(tx_fee) as avg_tx_fee,

COUNT (DISTINCT from_address) as n_users,

avg(eth_value) as avg_eth_value

FROM ethereum.core.fact_transactions

WHERE date_trunc('day',block_timestamp) between '2022-11-02' and '2022-11-15'

GROUP BY period

SELECT 'Arbitrum' as label,

CASE WHEN date_trunc('day',block_timestamp) between '2022-11-02' AND '2022-11-07' THEN 'before'

WHEN date_trunc('day',block_timestamp) between '2022-11-09' AND '2022-11-15' THEN 'after'

ELSE 'during' END as period,