Query editor
WITH candidates AS (SELECT * FROM dune.nathanthhr.result_crosschain_flashloan_candidates),boundary_by_token AS (SELECTt.blockchain AS chain,t.tx_hash,t.contract_address,SUM(CASE WHEN t.to = c.receiver THEN t.amount WHEN t."from" = c.receiver THEN -t.amount ELSE 0 END) AS net_amount,SUM(CASE WHEN t.to = c.receiver THEN t.amount_usd WHEN t."from" = c.receiver THEN -t.amount_usd ELSE 0 END) AS net_usd,COUNT_IF((t.to = c.receiver OR t."from" = c.receiver) AND t.amount_usd IS NULL) AS unpriced_legsFROM tokens.transfers tINNER JOIN candidates c ON c.chain = t.blockchain AND c.tx_hash = t.tx_hashWHERE t.blockchain IN ('ethereum','base','arbitrum','optimism','polygon','bnb','avalanche_c','gnosis','linea','scroll','sonic','monad','unichain')AND t.block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '30' DAY)AND t.block_date >= CURRENT_DATE - INTERVAL '30' DAYAND (t.to = c.receiver OR t."from" = c.receiver)GROUP BY 1, 2, 3),boundary_summary AS (SELECTc.chain,c.tx_hash,MAX(CASE WHEN b.contract_address = c.asset THEN b.net_amount END) AS loan_token_net_amount,MAX(CASE WHEN b.contract_address = c.asset THEN b.unpriced_legs END) AS loan_token_unpriced_legs,COUNT_IF(b.contract_address <> c.assetAND ABS(b.net_amount) > 1e-12AND (b.unpriced_legs > 0 OR ABS(COALESCE(b.net_usd, 0)) > 0.01)) AS material_other_token_balancesFROM candidates cJOIN boundary_by_token b ON b.chain = c.chain AND b.tx_hash = c.tx_hashGROUP BY 1, 2),reconciled AS (SELECTc.*,c.gross_spread_usd - c.provider_fee_usd - c.gas_usd AS net_profit_usdFROM candidates cJOIN boundary_summary b ON b.chain = c.chain AND b.tx_hash = c.tx_hashWHERE b.loan_token_net_amount IS NOT NULLAND b.loan_token_unpriced_legs = 0AND b.material_other_token_balances = 0AND ABS((b.loan_token_net_amount - c.expected_receiver_gain) * c.loop_token_price)<= GREATEST(0.01, c.gross_spread_usd * 0.01)),candidate_counts AS (SELECT chain, COUNT(*) AS strict_loop_candidatesFROM candidatesGROUP BY 1)SELECTx.chain,x.strict_loop_candidates,COUNT(r.tx_hash) AS receiver_reconciled_transactions,COUNT_IF(r.net_profit_usd > 0) AS profitable_transactions,COUNT_IF(r.net_profit_usd <= 0) AS cost_losing_transactions,CAST(COUNT(r.tx_hash) AS DOUBLE) / NULLIF(x.strict_loop_candidates, 0) AS reconciliation_coverage,SUM(r.gross_spread_usd) AS gross_spread_usd,SUM(r.provider_fee_usd) AS provider_fees_usd,