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 aave_v3_optimism.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 '14' DAY),p AS (SELECTdate_trunc('hour', minute) as day,contract_address,AVG(price) AS avg_priceFROM prices.usdWHERE minute > now() - interval '14' DAYAND blockchain = 'optimism'GROUP BY 1, 2)SELECTLiquidations.day,token_col,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