Query editor
WITH ctx_cex_vol AS (SELECTCAST(date(split_part("timestamp", 'T', 1)) AS TIMESTAMP) AS day,price AS CEX_Price,volume AS CEX_VolumeFROM dune.cryptexfinance.dataset_sqlmesh_ctx_volume_and_price -- Pulling in from the CoinCarp API),ctx_dex_trades AS (SELECTblockchain, project, block_time,token_bought_symbol, token_sold_symbol, token_pair,token_bought_amount, token_sold_amount, amount_usdFROM dex.tradesWHERE token_bought_symbol IN ('CTX') OR token_sold_symbol IN ('CTX')),ctx_dex_vol AS (SELECTDATE_TRUNC('day', block_time) AS day,SUM(amount_usd) AS DEX_VolumeFROM ctx_dex_tradesWHERE project IN ('sushiswap', 'uniswap')GROUP BY 1),dex_price AS (SELECTDATE_TRUNC('day', "hour") AS day,AVG(median_price) AS DEX_priceFROM dex.pricesWHERE contract_address = 0x321C2fE4446C7c963dc41Dd58879AF648838f98DGROUP BY 1),final_ctx_vol AS (SELECTCOALESCE(cc.day, cd.day, dp.day) AS day,cc.CEX_price,cc.CEX_Volume,cd.DEX_Volume,dp.DEX_priceFROM ctx_cex_vol ccFULL OUTER JOIN ctx_dex_vol cd ON cc.day = cd.dayFULL OUTER JOIN dex_price dp ON COALESCE(cc.day, cd.day) = dp.dayWHERE COALESCE(cc.day, cd.day, dp.day) >= DATE '2021-04-01'),volcte AS (SELECT *,CEX_Volume + DEX_Volume AS Total_Volume,SUM(CEX_Volume) OVER (ORDER BY day ASC) AS Rolling_CEX_Volume,SUM(DEX_Volume) OVER (ORDER BY day ASC) AS Rolling_DEX_VolumeFROM final_ctx_volORDER BY 1 DESC)SELECT *,Rolling_CEX_Volume + Rolling_DEX_Volume AS Rolling_Total_VolumeFROM volcte