Query editor
WITH CollateralAggregated AS (SELECTevt_tx_hash,evt_block_time,SUM(usdValue) / POWER(10, 8) AS total_col_value -- Sum of all collateral values for each tx hashFROMcompound_v3_scroll.cUSDCv3_evt_AbsorbCollateralGROUP BYevt_tx_hash, evt_block_time),DebtAggregated AS (SELECTevt_tx_hash,SUM(usdValue) / POWER(10, 8) AS total_debt_value -- Sum of all debt values for each tx hashFROMcompound_v3_scroll.cUSDCv3_evt_AbsorbDebtGROUP BYevt_tx_hash)SELECTagg.evt_tx_hash,agg.evt_block_time,agg.total_col_value,debt.total_debt_value,CASEWHEN agg.total_col_value >= debt.total_debt_valueTHEN ((agg.total_col_value - debt.total_debt_value) * 0.6) -- No need to scale againELSE 0 -- Handle cases where underflow would occurEND AS liquidator_incentive,CASEWHEN agg.total_col_value >= debt.total_debt_valueTHEN ((agg.total_col_value - debt.total_debt_value) * 0.4) -- No need to scale againELSE 0 -- Handle cases where underflow would occurEND AS protocol_revFROMCollateralAggregated AS aggINNER JOINDebtAggregated AS debtON agg.evt_tx_hash = debt.evt_tx_hashORDER BYliquidator_incentive DESC;