sum(amount_in_usd)*-1 as eth_sell_volume_dex

FROM ethereum.core.ez_dex_swaps

WHERE symbol_in = 'WETH'

GROUP BY 1),

eth_dex_bought AS (SELECT block_timestamp::date as day_time,

sum(amount_out_usd) as eth_buy_volume_dex

FROM ethereum.core.ez_dex_swaps

WHERE symbol_out = 'WETH'

GROUP BY 1),

eth_cex_sold AS (

SELECT block_timestamp::date as day_time,

sum(amount_usd)*-1 as eth_sell_volume_cex

FROM ethereum.core.ez_eth_transfers

WHERE eth_to_address IN (SELECT address FROM ethereum.core.dim_labels WHERE label_type = 'cex')

GROUP BY 1

),

eth_cex_bought AS (

SELECT block_timestamp::date as day_time,

sum(amount_usd) as eth_buy_volume_cex

FROM ethereum.core.ez_eth_transfers

WHERE eth_from_address IN (SELECT address FROM ethereum.core.dim_labels WHERE label_type = 'cex')

GROUP BY 1

),

info AS (SELECT ds.day_time,

zeroifnull(eth_sell_volume_dex) + zeroifnull(eth_sell_volume_cex) as total_eth_sold,

zeroifnull(eth_buy_volume_dex) + zeroifnull(eth_buy_volume_cex) as total_eth_bought

FROM eth_dex_sold ds

FULL JOIN eth_cex_sold cs

ON ds.day_time = cs.day_time

FULL JOIN eth_dex_bought db

ON ds.day_time = db.day_time