Query editor
WITHverified_strategies(borrower, beneficiary, strategy) AS (VALUES(0xDF5A4AA468311F70BFD61FACE904BACB5DBCA1F5, 0xDF5A4AA468311F70BFD61FACE904BACB5DBCA1F5, 'DODO + secondary pool hidden loop'),(0x49177C391CB71677D5AC9C14BFDB7260F35A4527, 0x49177C391CB71677D5AC9C14BFDB7260F35A4527, 'DODO + secondary pool hidden loop'),(0x6FE749508C3991B9CAAB35D49066EF8E7882D6A7, 0x6FE749508C3991B9CAAB35D49066EF8E7882D6A7, 'DODO + secondary pool hidden loop'),(0x41B722E002ECD1E9AD70A6CFB01E30B8738715BC, 0x4502B8E2332E2076945D33B1C561D3CD6A138040, 'Uniswap V4/V3 hidden loop')),flash_tx AS (SELECT m.evt_tx_hash AS tx_hash, MIN(m.evt_block_date) AS block_date,m.caller AS borrower, s.beneficiary, s.strategy,SUM(CAST(m.assets AS DOUBLE) / POW(10, COALESCE(p.decimals, 18)) * p.price) AS borrowed_usdFROM morpho_blue_base.morphoblue_evt_flashloan mJOIN verified_strategies s ON s.borrower = m.callerLEFT JOIN prices.day p ON p.blockchain = 'base' AND p.contract_address = m.tokenAND p.timestamp = CAST(m.evt_block_date AS TIMESTAMP)WHERE m.evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYGROUP BY 1, 3, 4, 5),beneficiary_flows AS (SELECT f.tx_hash,COUNT_IF(tt."from" = f.beneficiary OR tt."to" = f.beneficiary) AS touched_transfers,COUNT_IF((tt."from" = f.beneficiary OR tt."to" = f.beneficiary) AND tt.amount_usd IS NOT NULL) AS priced_transfers,SUM(CASE WHEN tt."to" = f.beneficiary THEN COALESCE(tt.amount_usd, 0) ELSE 0 END- CASE WHEN tt."from" = f.beneficiary THEN COALESCE(tt.amount_usd, 0) ELSE 0 END) AS beneficiary_gross_usdFROM flash_tx fJOIN tokens.transfers tt ON tt.tx_hash = f.tx_hash AND tt.blockchain = 'base'AND tt.block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '30' DAY)AND tt.block_date >= CURRENT_DATE - INTERVAL '30' DAYGROUP BY 1),profit_tx AS (SELECT f.*, b.beneficiary_gross_usd,(CAST(t.gas_used AS DOUBLE) * CAST(t.gas_price AS DOUBLE) + CAST(t.l1_fee AS DOUBLE)) / 1e18 * ep.price AS gas_cost_usd,b.beneficiary_gross_usd - (CAST(t.gas_used AS DOUBLE) * CAST(t.gas_price AS DOUBLE) + CAST(t.l1_fee AS DOUBLE)) / 1e18 * ep.price AS estimated_net_profit_usd,100.0 * b.priced_transfers / NULLIF(b.touched_transfers, 0) AS price_coverage_pctFROM flash_tx fJOIN beneficiary_flows b ON b.tx_hash = f.tx_hashJOIN base.transactions t ON t.hash = f.tx_hash AND t.block_date >= CURRENT_DATE - INTERVAL '30' DAYLEFT JOIN prices.day ep ON ep.blockchain = 'base'AND ep.contract_address = 0x0000000000000000000000000000000000000000AND ep.timestamp = CAST(f.block_date AS TIMESTAMP))SELECT strategy, CONCAT('0x', TO_HEX(borrower)) AS borrower,CONCAT('0x', TO_HEX(beneficiary)) AS validated_beneficiary,COUNT(*) AS transactions, SUM(borrowed_usd) AS borrowed_usd,SUM(beneficiary_gross_usd) AS gross_profit_usd, SUM(gas_cost_usd) AS gas_cost_usd,SUM(estimated_net_profit_usd) AS estimated_net_profit_usd,AVG(estimated_net_profit_usd) AS avg_net_profit_usd,APPROX_PERCENTILE(estimated_net_profit_usd, 0.5) AS median_net_profit_usd,APPROX_PERCENTILE(estimated_net_profit_usd, 0.95) AS p95_net_profit_usd,100.0 * COUNT_IF(estimated_net_profit_usd > 0) / COUNT(*) AS profitable_tx_pct,AVG(price_coverage_pct) AS avg_price_coverage_pctFROM profit_txWHERE price_coverage_pct = 100GROUP BY 1, 2, 3ORDER BY estimated_net_profit_usd DESC