Query editor
WITH Liquidations 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_avalanche_c.Pool_evt_LiquidationCall liquidationsLEFT JOIN tokens.erc20 t ON liquidations.collateralAsset = t.contract_addressLEFT JOIN tokens.erc20 t2 ON liquidations.debtAsset = t2.contract_addressWHERE evt_block_time > now() - interval '1500' DAY),p AS (SELECTdate_trunc('year', minute) as year,contract_address,AVG(price) AS avg_priceFROM prices.usdWHERE minute > now() - interval '1500' DAYAND blockchain = 'avalanche_c'GROUP BY 1, 2)SELECTLiquidations.year,SUM(collateral_amount*p.avg_price) as TotalLiquidated,SUM(collateral_amount*p.avg_price - debt_amount*p2.avg_price) As extractedFROM LiquidationsLEFT JOIN p ON Liquidations.year = p.year AND p.contract_address = Liquidations.col_contract_addressLEFT JOIN p AS p2 ON Liquidations.year = p2.year AND p2.contract_address = Liquidations.debt_contract_addressGROUP BY 1ORDER BY 1 DESC;