Query editor
WITH LiquidationsBASEV3 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_v3_base.L2Pool_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 LiquidationsBASEV3),p AS (SELECTDATE_TRUNC('year', minute) AS year,contract_address,AVG(price) AS avg_priceFROM prices.usdWHEREminute > CURRENT_TIMESTAMP - INTERVAL '1500' DAY AND blockchain = 'base'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