Query editor
WITH Liquidations AS (SELECTdate_trunc('hour', evt_block_time) as day,t.symbol as token_col,t2.symbol as token_debt,collateralAsset as col_contract_address,liquidatedCollateralAmount/POW(10,t.decimals) as collateral_amount,liquidator,debtAsset as debt_contract_address,(debtToCover/POW(10,t2.decimals)) as debt_amount,evt_tx_hash AS txsFROM seamlessprotocol_base.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 '1000' DAY),p AS (SELECTdate_trunc('hour', minute) as day,contract_address,AVG(price) AS avg_priceFROM prices.usdWHERE minute > now() - interval '1000' DAYAND blockchain = 'base'GROUP BY 1, 2)SELECT DISTINCTLiquidations.day,collateral_amount * p.avg_price AS collateral_amount_usd,token_debt,debt_amount * p2.avg_price AS debt_amount_usd,collateral_amount * p.avg_price - debt_amount * p2.avg_price AS extracted,txsFROM LiquidationsLEFT JOIN p ON Liquidations.day = p.day AND p.contract_address = Liquidations.col_contract_addressLEFT JOIN p AS p2 ON Liquidations.day = p2.day AND p2.contract_address = Liquidations.debt_contract_addressORDER BY 1 DESC;