Query editor
WITH Liquidations AS (SELECTdate_trunc('week', evt_block_time) as week,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_scroll.L2Pool_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('week', minute) as week,contract_address,AVG(price) AS avg_priceFROM prices.usdWHERE minute > now() - interval '1500' DAYAND blockchain = 'scroll'GROUP BY 1, 2)SELECTLiquidations.week,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.week = p.week AND p.contract_address = Liquidations.col_contract_addressLEFT JOIN p AS p2 ON Liquidations.week = p2.week AND p2.contract_address = Liquidations.debt_contract_addressGROUP BY 1ORDER BY 1 DESC;