Query editor
WITH LiquidationsPOLYGONV2 AS (SELECTDATE_TRUNC('year', evt_block_time) AS year,collateralAsset AS col_contract_address,liquidatedCollateralAmount / POWER(10, t.decimals) AS collateral_amount,debtAsset AS debt_contract_address,(debtToCover / POWER(10, t2.decimals)) AS debt_amountFROM aave_v2_polygon.LendingPool_evt_LiquidationCall liquidationsLEFT JOIN tokens.erc20 AS t ON liquidations.collateralAsset = t.contract_addressLEFT JOIN tokens.erc20 AS t2 ON liquidations.debtAsset = t2.contract_addressWHERE evt_block_time > CURRENT_TIMESTAMP - INTERVAL '1500' DAY),LiquidationsPOLYGONV3 AS (SELECTDATE_TRUNC('year', evt_block_time) AS year,collateralAsset AS col_contract_address,liquidatedCollateralAmount / POW(10, t.decimals) AS collateral_amount,debtAsset AS debt_contract_address,(debtToCover / POW(10, t2.decimals)) AS debt_amountFROM aave_v3_polygon.Pool_evt_LiquidationCall liquidationsLEFT JOIN tokens.erc20 AS t ON liquidations.collateralAsset = t.contract_addressLEFT JOIN tokens.erc20 AS t2 ON liquidations.debtAsset = t2.contract_addressWHERE evt_block_time > CURRENT_TIMESTAMP - INTERVAL '1500' DAY),CombinedLiquidations AS (SELECT * FROM LiquidationsPOLYGONV2UNION ALLSELECT * FROM LiquidationsPOLYGONV3),p AS (SELECTDATE_TRUNC('year', minute) AS year,contract_address,AVG(price) AS avg_priceFROM prices.usdWHEREminute > CURRENT_TIMESTAMP - INTERVAL '1500' DAY AND blockchain = 'polygon'GROUP BY1, 2),CombinedData AS (SELECTCombinedLiquidations.year,SUM(collateral_amount * p.avg_price) AS TotalLiquidated,SUM(collateral_amount * p.avg_price * 0.05) AS extractedFROM CombinedLiquidationsLEFT JOIN p ON CombinedLiquidations.year = p.yearAND p.contract_address = CombinedLiquidations.col_contract_addressGROUP BY1)SELECTSUM(TotalLiquidated) AS TotalLiquidated,SUM(extracted) AS TotalExtractedFROM CombinedData