Query editor
WITH Prices1 AS (SELECTDATE_TRUNC('week', minute) AS week, contract_address, symbol, AVG(price) AS PriceFROM prices.usdWHERE blockchain = 'base'GROUP BY 1,2,3),Base AS (SELECTDATE_TRUNC('week', a.Date) AS week, 'Base' AS Chain, SUM(ABS(a.Amount * b.Price)) AS Volume, COUNT(DISTINCT hash) AS Nb_LiqFROM dune.silo_finance.result_silo_finance_all_base_transaction aLEFT JOIN Prices1 b ON DATE_TRUNC('week', a.Date) = b.week AND a.Replace_Asset = b.contract_addressWHERE a.Date >= CAST('2024-09-11 00:00' AS TIMESTAMP) AND a.action = 'Liquidate'GROUP BY 1,2),Prices2 AS (SELECTDATE_TRUNC('week', minute) AS week, contract_address, symbol, AVG(price) AS PriceFROM prices.usdWHERE blockchain = 'optimism'GROUP BY 1,2,3),Optimism AS (SELECTDATE_TRUNC('week', a.Date) AS week, 'Optimism' AS Chain, SUM(ABS(a.Amount * b.Price)) AS Volume, COUNT(DISTINCT hash) AS Nb_LiqFROM dune.silo_finance.result_silo_finance_all_optimism_transaction aLEFT JOIN Prices2 b ON DATE_TRUNC('week', a.Date) = b.week AND a.Replace_Asset = b.contract_addressWHERE a.action = 'Liquidate'GROUP BY 1,2),Prices3 AS (SELECTDATE_TRUNC('week', minute) AS week, contract_address, symbol, AVG(price) AS PriceFROM prices.usdWHERE blockchain = 'ethereum'GROUP BY 1,2,3),Ethereum_Main AS (SELECTDATE_TRUNC('week', a.Date) AS week, 'Ethereum Main' AS Chain, SUM(ABS(a.Amount * b.Price)) AS Volume, COUNT(DISTINCT hash) AS Nb_LiqFROM dune.silo_finance.result_silo_finance_all_ethereum_main_transaction aLEFT JOIN Prices3 b ON DATE_TRUNC('week', a.Date) = b.week AND a.Replace_Asset = b.contract_address