Query editor
WITH CollateralAggregated_USDC AS (SELECT'Arbitrum' AS chain,evt_tx_hash,DATE_TRUNC('week', evt_block_time) AS week_start,SUM(usdValue) / POWER(10, 8) AS total_col_valueFROM compound_v3_arbitrum.cUSDCv3_evt_AbsorbCollateralGROUP BYchain, evt_tx_hash, week_start), DebtAggregated_USDC AS (SELECT'Arbitrum' AS chain,evt_tx_hash,DATE_TRUNC('week', evt_block_time) AS week_start,SUM(usdValue) / POWER(10, 8) AS total_debt_valueFROM compound_v3_arbitrum.cUSDCv3_evt_AbsorbDebtGROUP BYchain, evt_tx_hash, week_start), IncentiveCalculation_USDC AS (SELECTcoll.chain,coll.week_start,CASEWHEN coll.total_col_value >= debt.total_debt_valueTHEN (coll.total_col_value - debt.total_debt_value)ELSE 0END AS incentiveFROM CollateralAggregated_USDC AS collINNER JOIN DebtAggregated_USDC AS debtON coll.evt_tx_hash = debt.evt_tx_hashAND coll.week_start = debt.week_startAND coll.chain = debt.chain), CollateralAggregated_USDT AS (SELECT'Arbitrum' AS chain,evt_tx_hash,DATE_TRUNC('week', evt_block_time) AS week_start,SUM(usdValue) / POWER(10, 8) AS total_col_valueFROM compound_v3_arbitrum.cUSDTv3_evt_AbsorbCollateralGROUP BYchain, evt_tx_hash, week_start), DebtAggregated_USDT AS (SELECT'Arbitrum' AS chain,evt_tx_hash,DATE_TRUNC('week', evt_block_time) AS week_start,SUM(usdValue) / POWER(10, 8) AS total_debt_valueFROM compound_v3_arbitrum.cUSDTv3_evt_AbsorbDebtGROUP BYchain, evt_tx_hash, week_start), IncentiveCalculation_USDT AS (SELECTcoll.chain,coll.week_start,CASEWHEN coll.total_col_value >= debt.total_debt_valueTHEN (coll.total_col_value - debt.total_debt_value)ELSE 0END AS incentiveFROM CollateralAggregated_USDT AS coll