Query editor
WITH CollateralAggregated AS (SELECTevt_tx_hash,SUM(usdValue) / POWER(10, 8) AS total_col_value -- Sum of all collateral values for each tx hashFROMcompound_v3_optimism.cWETHv3_evt_AbsorbCollateralGROUP BYevt_tx_hash),DebtAggregated AS (SELECTevt_tx_hash,SUM(usdValue) / POWER(10, 8) AS total_debt_value -- Sum of all debt values for each tx hashFROMcompound_v3_optimism.cWETHv3_evt_AbsorbDebtGROUP BYevt_tx_hash),IncentiveCalculation AS (SELECTagg.evt_tx_hash,CASEWHEN agg.total_col_value >= debt.total_debt_valueTHEN (agg.total_col_value - debt.total_debt_value)ELSE 0 -- Handle cases where underflow would occurEND AS incentiveFROMCollateralAggregated AS aggINNER JOINDebtAggregated AS debtON agg.evt_tx_hash = debt.evt_tx_hash)SELECTSUM(incentive) * 0.7 AS total_incentive -- Sum of all incentives across all transactionsFROMIncentiveCalculation;