WITH uniswap AS ( SELECT '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'::BYTEA AS token, evt_block_time, eth AS amt FROM (( SELECT evt_block_time, eth_sold AS eth FROM uniswap. "Exchange_evt_TokenPurchase") UNION ALL ( SELECT evt_block_time, eth_bought AS eth FROM uniswap. "Exchange_evt_EthPurchase")) ether ), kyber_volume_mapping AS ( SELECT evt_block_time, CASE WHEN reserve1 IN ('\x2295fc6BC32cD12fdBb852cFf4014cEAc6d79C10', '\x57f8160e1c59D16C01BbE181fD94db4E56b60495', '\x0000000000000000000000000000000000000000') THEN 0 ELSE "ethWeiValue" END AS reserve1_eth_volume, CASE WHEN reserve2 IN ('\x2295fc6BC32cD12fdBb852cFf4014cEAc6d79C10', '\x57f8160e1c59D16C01BbE181fD94db4E56b60495', '\x0000000000000000000000000000000000000000') THEN 0 ELSE "ethWeiValue" END AS reserve2_eth_volume FROM kyber."Network_evt_KyberTrade" trade ), kyber AS ( SELECT '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'::BYTEA AS token, date_trunc('hour', evt_block_time) AS evt_block_time, SUM(reserve1_eth_volume+reserve2_eth_volume) as amt FROM kyber_volume_mapping GROUP BY evt_block_time ), zeroex AS ( SELECT CASE -- Set Deversifi ETHWrapper to WETH WHEN SUBSTRING("takerAssetData" FROM 17) IN ('50cb61afa3f023d17276dcfb35abf85c710d1cff', 'aa7427d8f17d87a28f5e1ba3adbb270badbe1011') THEN 'c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- Set Deversifi USDCWrapper to USDC WHEN SUBSTRING("takerAssetData" FROM 17) IN ('69391cca2e38b845720c7deb694ec837877a8e53') THEN 'a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ELSE SUBSTRING("takerAssetData" FROM 17) END AS token, evt_block_time, "takerAssetFilledAmount" AS amt FROM zeroex_v2."Exchange2.0_evt_Fill" UNION ALL SELECT CASE -- Set Deversifi ETHWrapper to WETH WHEN SUBSTRING("takerAssetData" FROM 17) IN ('50cb61afa3f023d17276dcfb35abf85c710d1cff', 'aa7427d8f17d87a28f5e1ba3adbb270badbe1011') THEN 'c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- Set Deversifi USDCWrapper to USDC WHEN SUBSTRING("takerAssetData" FROM 17) IN ('69391cca2e38b845720c7deb694ec837877a8e53') THEN 'a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ELSE SUBSTRING("takerAssetData" FROM 17) END AS token, evt_block_time, "takerAssetFilledAmount" AS amt FROM zeroex_v2."Exchange2.1_evt_Fill" UNION ALL SELECT CASE -- Set Deversifi ETHWrapper to WETH WHEN SUBSTRING("takerAssetData" FROM 17) IN ('50cb61afa3f023d17276dcfb35abf85c710d1cff', 'aa7427d8f17d87a28f5e1ba3adbb270badbe1011') THEN 'c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- Set Deversifi USDCWrapper to USDC WHEN SUBSTRING("takerAssetData" FROM 17) IN ('69391cca2e38b845720c7deb694ec837877a8e53') THEN 'a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ELSE SUBSTRING("takerAssetData" FROM 17) END AS token, evt_block_time, "takerAssetFilledAmount" AS amt FROM zeroex_v3."Exchange_evt_Fill" ), oasis AS ( ( -- DAI bought SELECT '\x6b175474e89094c44da98b954eedeac495271d0f'::BYTEA AS token, evt_block_time, buy_amt AS amt FROM oasisdex. "eth2dai_evt_LogTrade" WHERE buy_gem = '\x6b175474e89094c44da98b954eedeac495271d0f') UNION ALL ( -- DAI sold SELECT '\x6b175474e89094c44da98b954eedeac495271d0f'::BYTEA AS token, evt_block_time, pay_amt AS amt FROM oasisdex. "eth2dai_evt_LogTrade" WHERE pay_gem = '\x6b175474e89094c44da98b954eedeac495271d0f') UNION ALL ( -- SAI bought SELECT '\x89d24a6b4ccb1b6faa2625fe562bdd9a23260359'::BYTEA AS token, evt_block_time, buy_amt AS amt FROM oasisdex. "eth2dai_evt_LogTrade" WHERE buy_gem = '\x89d24a6b4ccb1b6faa2625fe562bdd9a23260359') UNION ALL ( -- SAI sold SELECT '\x89d24a6b4ccb1b6faa2625fe562bdd9a23260359'::BYTEA AS token, evt_block_time, pay_amt AS amt FROM oasisdex. "eth2dai_evt_LogTrade" WHERE pay_gem = '\x89d24a6b4ccb1b6faa2625fe562bdd9a23260359') UNION ALL ( -- [Old contract] SAI bought SELECT '\x89d24a6b4ccb1b6faa2625fe562bdd9a23260359'::BYTEA AS token, evt_block_time, buy_amt AS amt FROM oasisdex."MatchingMarket_evt_LogTrade" WHERE buy_gem = '\x89d24a6b4ccb1b6faa2625fe562bdd9a23260359') UNION ALL ( -- [Old contract] SAI sold SELECT '\x89d24a6b4ccb1b6faa2625fe562bdd9a23260359'::BYTEA AS token, evt_block_time, pay_amt AS amt FROM oasisdex."MatchingMarket_evt_LogTrade" WHERE pay_gem = '\x89d24a6b4ccb1b6faa2625fe562bdd9a23260359') ), idex AS ( SELECT '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'::BYTEA AS token, call_block_time AS evt_block_time, CASE WHEN "tradeAddresses"[1] = '\x0000000000000000000000000000000000000000' THEN "tradeValues"[1] WHEN "tradeAddresses"[2] = '\x0000000000000000000000000000000000000000' THEN "tradeValues"[2] END AS amt FROM idex. "IDEX1_call_trade" ), airswap AS ( SELECT "takerToken" AS token, evt_block_time, "takerAmount" AS amt FROM airswap. "Exchange_evt_Filled" ), wyvern_token AS ( SELECT call_block_time as evt_block_time, call_tx_hash AS tx_hash, CASE WHEN addrs[7] = '\x0000000000000000000000000000000000000000' THEN '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' ELSE addrs[7] END AS token_address FROM wyvern."WyvernExchange_call_atomicMatch_" a ), wyvern AS ( SELECT t.token_address AS token, om.evt_block_time, price AS amt FROM wyvern."WyvernExchange_evt_OrdersMatched" om LEFT JOIN wyvern_token t ON t.tx_hash = om.evt_tx_hash ), non_dydx AS ( SELECT date_trunc('month', day) as month, SUM((amt / 10^decimals) * price.price) AS volume FROM ( SELECT * FROM uniswap UNION SELECT * FROM kyber UNION SELECT * FROM oasis UNION SELECT * FROM idex UNION SELECT * FROM airswap UNION SELECT * FROM zeroex UNION SELECT * FROM wyvern ) fills INNER JOIN ( SELECT DATE_TRUNC('day', minute) AS day, contract_address, symbol, AVG(price) AS price FROM prices.usd WHERE minute >= '2019-01-01' AND minute < date_trunc('month', now()) GROUP BY 1, 2, 3 ) price ON price.day = DATE_TRUNC('day', evt_block_time) AND price.contract_address = fills.token INNER JOIN erc20.tokens ON token = tokens.contract_address WHERE evt_block_time >= '2019-01-01' GROUP BY 1 ), dydx_txns AS ( SELECT evt_block_time AS block_time , trades.* FROM dydx."SoloMargin_evt_LogTrade" trades WHERE evt_block_time >= date_trunc('week', now()::timestamptz - interval '24 weeks') AND evt_block_time < date_trunc('month', Now()) ) , staging_dydx_txns AS ( SELECT txns.block_time , txns."takerAccountOwner" , txns."takerAccountNumber" , txns."makerAccountOwner" , txns."makerAccountNumber" , txns."inputMarket" , CASE WHEN txns."inputMarket" = 0 THEN 'WETH' WHEN txns."inputMarket" = 1 THEN 'SAI' WHEN txns."inputMarket" = 2 THEN 'USDC' WHEN txns."inputMarket" = 3 THEN 'DAI' END as "inputMarketSymbol" , CASE WHEN "inputMarket" = 0 THEN 18 WHEN "inputMarket" = 1 THEN 18 WHEN "inputMarket" = 2 THEN 8 WHEN "inputMarket" = 3 THEN 18 END as "inputMarketDecimals" , txns."outputMarket" , CASE WHEN txns."outputMarket" = 0 THEN 'WETH' WHEN txns."outputMarket" = 1 THEN 'SAI' WHEN txns."outputMarket" = 2 THEN 'USDC' WHEN txns."outputMarket" = 3 THEN 'DAI' END as "outputMarketSymbol" , CASE WHEN "outputMarket" = 0 THEN 18 WHEN "outputMarket" = 1 THEN 18 WHEN "outputMarket" = 2 THEN 8 WHEN "outputMarket" = 3 THEN 18 END as "outputMarketDecimals" , txns."takerInputUpdate" , (((txns."takerInputUpdate"::json->>'deltaWei')::json)->>'value')::text as "takerInputNumber" , CASE WHEN (((txns."takerInputUpdate"::json->>'deltaWei')::json)->>'sign')::boolean THEN -1 ELSE 1 END as "takerInputSign" , txns."takerOutputUpdate" , (((txns."takerOutputUpdate"::json->>'deltaWei')::json)->>'value')::text as "takerOutputNumber" , CASE WHEN (((txns."takerOutputUpdate"::json->>'deltaWei')::json)->>'sign')::boolean THEN -1 ELSE 1 END as "takerOutputSign" , txns."makerInputUpdate" , (((txns."makerInputUpdate"::json->>'deltaWei')::json)->>'value')::text as "makerInputNumber" , CASE WHEN (((txns."makerInputUpdate"::json->>'deltaWei')::json)->>'sign')::boolean THEN -1 ELSE 1 END as "makerInputSign" , txns."makerOutputUpdate" , (((txns."makerOutputUpdate"::json->>'deltaWei')::json)->>'value')::text as "makerOutputNumber" , CASE WHEN (((txns."makerOutputUpdate"::json->>'deltaWei')::json)->>'sign')::boolean THEN -1 ELSE 1 END as "makerOutputSign" , txns."autoTrader" , txns."contract_address" , txns.evt_tx_hash as tx_hash FROM dydx_txns txns ), clean_dydx_txns AS ( SELECT txns.block_time , txns."takerAccountOwner" , txns."takerAccountNumber" , txns."makerAccountOwner" , txns."makerAccountNumber" , txns."inputMarket" , txns."inputMarketSymbol" , txns."inputMarketDecimals" , txns."outputMarket" , txns."outputMarketSymbol" , txns."outputMarketDecimals" , (txns."takerInputNumber"::DECIMAL / (10^txns."inputMarketDecimals")) * txns."takerInputSign" as "takerInputAmount" , (txns."takerOutputNumber"::DECIMAL / (10^txns."outputMarketDecimals")) * txns."takerOutputSign" as "takerOutputAmount" , (txns."makerInputNumber"::DECIMAL / (10^txns."inputMarketDecimals")) * txns."makerInputSign" as "makerInputAmount" , (txns."makerOutputNumber"::DECIMAL / (10^txns."outputMarketDecimals")) * txns."makerOutputSign" as "makerOutputAmount" , txns."takerInputUpdate" , txns."takerOutputUpdate" , txns."makerInputUpdate" , txns."makerOutputUpdate" , txns."autoTrader" , txns."contract_address" , txns."tx_hash" FROM staging_dydx_txns txns ), cleaned_prices AS ( SELECT DATE_TRUNC('hour', minute) AS hour, contract_address, symbol, AVG(price) AS price FROM prices.usd WHERE minute >= '2019-01-01' AND minute < date_trunc('month', now()) AND symbol IN ('WETH','SAI','DAI','USDC') GROUP BY 1, 2, 3 ), clean_dydx_txns_with_prices AS ( SELECT txns.* , inputPrice.price as "inputPrice" , inputPrice.price * txns."takerInputAmount" as "takerInputAmountUSD" FROM clean_dydx_txns txns LEFT JOIN cleaned_prices inputPrice ON inputPrice.hour = date_trunc('hour', txns.block_time) AND inputPrice.symbol = txns."inputMarketSymbol" ), dydx AS ( SELECT date_trunc('month', txns.block_time) as "month" , SUM(abs("takerInputAmountUSD")) / 2 as volume FROM clean_dydx_txns_with_prices txns WHERE txns.block_time >= '2019-01-01' AND txns.block_time < date_trunc('month', now()) GROUP BY 1 ), curve AS ( select month , sum(susdv2+busd+y+usdt+compound) as volume from ( (select date_trunc('month', evt_block_time) AS month, sum(tokens_sold/(case when sold_id = 0 then 1e18 when sold_id = 1 then 1e6 when sold_id = 2 then 1e6 when sold_id = 3 then 1e18 end)) susdv2, 0 as busd, 0 as y, 0 as usdt, 0 as compound from curvefi."susd_v2_evt_TokenExchangeUnderlying" as cst WHERE evt_block_time >= '2019-01-01' AND evt_block_time < date_trunc('month', now()) group by 1 union select date_trunc('month', evt_block_time) AS month, sum(tokens_sold/(case when sold_id = 0 then 1e18 when sold_id = 1 then 1e6 when sold_id = 2 then 1e6 when sold_id = 3 then 1e18 end)) susdv2, 0 as busd, 0 as y, 0 as usdt, 0 as compound from curvefi."susd_v2_evt_TokenExchange" as cst WHERE evt_block_time >= '2019-01-01' AND evt_block_time < date_trunc('month', now()) group by 1 ) union select date_trunc('month', evt_block_time) AS month, 0 as susdv2, sum(tokens_sold/(case when sold_id = 0 then 1e18 when sold_id = 1 then 1e6 when sold_id = 2 then 1e6 when sold_id = 3 then 1e18 end)) busd, 0 as y, 0 as usdt, 0 as compound from curvefi."busd_evt_TokenExchangeUnderlying" as cst WHERE evt_block_time >= '2019-01-01' AND evt_block_time < date_trunc('month', now()) group by 1 union select date_trunc('month', evt_block_time) AS month, 0 as susdv2, 0 as busd, sum(tokens_sold/(case when sold_id = 0 then 1e18 when sold_id = 1 then 1e6 when sold_id = 2 then 1e6 when sold_id = 3 then 1e18 end)) y, 0 as usdt, 0 as compound from curvefi."y_evt_TokenExchangeUnderlying" as cst WHERE evt_block_time >= '2019-01-01' AND evt_block_time < date_trunc('month', now()) group by 1 union select date_trunc('month', evt_block_time) AS month, 0 as susdv2, 0 as busd, 0 as y, sum(tokens_sold/(case when sold_id = 0 then 1e18 when sold_id = 1 then 1e6 when sold_id = 2 then 1e6 end)) as usdt, 0 as compound from curvefi."usdt_evt_TokenExchangeUnderlying" as cst WHERE evt_block_time >= '2019-01-01' AND evt_block_time < date_trunc('month', now()) group by 1 union (select date_trunc('month', evt_block_time) AS month, 0 as susdv2, 0 as busd, 0 as y, 0 as usdt, sum(tokens_sold/(case when sold_id = 0 then 1e18 when sold_id = 1 then 1e6 end)) as compound from curvefi."compound_v2_evt_TokenExchangeUnderlying" as cst WHERE evt_block_time >= '2019-01-01' AND evt_block_time < date_trunc('month', now()) group by 1 union select date_trunc('month', evt_block_time) AS month, 0 as susdv2, 0 as busd, 0 as y, 0 as usdt, sum(tokens_sold/(case when sold_id = 0 then 1e18 when sold_id = 1 then 1e6 end)) as compound from curvefi."compound_v3_evt_TokenExchangeUnderlying" as cst WHERE evt_block_time >= '2019-01-01' AND evt_block_time < date_trunc('month', now()) group by 1) ) a group by 1 ) SELECT total.month, SUM(volume), AVG(eth_price) FROM (SELECT * FROM non_dydx UNION ALL SELECT * FROM dydx UNION ALL SELECT * FROM curve ) total INNER JOIN ( SELECT date_trunc('month', minute) as month, AVG(price) AS eth_price FROM prices.usd WHERE symbol='ETH' AND minute >= '2019-01-01' GROUP BY 1) eth ON total.month = eth.month GROUP BY 1;