Query editor
WITHvalidated_strategies(borrower, fixed_beneficiary, strategy, route_summary, beneficiary_rule) AS (VALUES(0xDF5A4AA468311F70BFD61FACE904BACB5DBCA1F5, 0xDF5A4AA468311F70BFD61FACE904BACB5DBCA1F5, 'Arbitrage', 'DODO versus another Base pool; surplus retained by borrower', 'fixed validated beneficiary'),(0x49177C391CB71677D5AC9C14BFDB7260F35A4527, 0x49177C391CB71677D5AC9C14BFDB7260F35A4527, 'Arbitrage', 'DODO versus another Base pool; surplus retained by borrower', 'fixed validated beneficiary'),(0x6FE749508C3991B9CAAB35D49066EF8E7882D6A7, 0x6FE749508C3991B9CAAB35D49066EF8E7882D6A7, 'Arbitrage', 'DODO versus another Base pool; surplus retained by borrower', 'fixed validated beneficiary'),(0x41B722E002ECD1E9AD70A6CFB01E30B8738715BC, 0x4502B8E2332E2076945D33B1C561D3CD6A138040, 'Arbitrage', 'Uniswap V4/V3 loop; surplus paid to validated helper', 'fixed validated beneficiary'),(0x52477C72741B5DF2DBA46EBB6A96601931D3280F, 0x03FAFA0B33BB05056DDC156DB1A4DF47D681539A, 'Arbitrage', 'Kyber routes swaps through Base liquidity venues; surplus paid to recurring recipient', 'fixed validated beneficiary'),(0xB3C21B3CAB3DF9294910286DE6C84F3CADCF8C0A, CAST(NULL AS VARBINARY), 'Arbitrage', 'Kyber routes swaps through Base liquidity venues; surplus paid to transaction sender', 'transaction sender')),loan_tx AS (SELECT m.evt_tx_hash AS tx_hash, MIN(m.evt_block_time) AS block_time,MIN(m.evt_block_date) AS block_date, m.caller AS borrower,s.strategy, s.route_summary, s.beneficiary_rule,t."from" AS tx_sender,CASE WHEN s.fixed_beneficiary IS NOT NULL THEN s.fixed_beneficiary ELSE t."from" END AS beneficiary,ARRAY_JOIN(ARRAY_SORT(ARRAY_AGG(DISTINCT COALESCE(p.symbol, CONCAT('0x', TO_HEX(m.token))))), ', ') AS borrowed_assets,SUM(CAST(m.assets AS DOUBLE) / POW(10, COALESCE(p.decimals, 18)) * p.price) AS borrowed_usd,MAX(CAST(t.gas_used AS DOUBLE) * CAST(t.gas_price AS DOUBLE) + CAST(t.l1_fee AS DOUBLE)) / 1e18 AS gas_ethFROM morpho_blue_base.morphoblue_evt_flashloan mJOIN validated_strategies s ON s.borrower = m.callerJOIN base.transactions tON t.hash = m.evt_tx_hashAND t.block_date >= CURRENT_DATE - INTERVAL '90' DAYLEFT JOIN prices.day pON 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 '90' DAYGROUP BY 1, 4, 5, 6, 7, 8, 9),beneficiary_sides AS (SELECT f.tx_hash, tt.amount_usd AS signed_usd, tt.amount_usd IS NOT NULL AS is_priced,CASE WHEN tt.amount_usd > 0 THEN tt.symbol END AS received_symbolFROM loan_tx fJOIN tokens.transfers ttON tt.tx_hash = f.tx_hashAND tt.blockchain = 'base'AND tt.block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '90' DAY)AND tt.block_date >= CURRENT_DATE - INTERVAL '90' DAYAND tt."to" = f.beneficiaryUNION ALLSELECT f.tx_hash, -tt.amount_usd AS signed_usd, tt.amount_usd IS NOT NULL AS is_priced,CAST(NULL AS VARCHAR) AS received_symbolFROM loan_tx fJOIN tokens.transfers ttON tt.tx_hash = f.tx_hashAND tt.blockchain = 'base'AND tt.block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '90' DAY)AND tt.block_date >= CURRENT_DATE - INTERVAL '90' DAYAND tt."from" = f.beneficiary),beneficiary_profit AS (SELECT tx_hash,COUNT(*) AS touched_transfers,COUNT_IF(is_priced) AS priced_transfers,SUM(COALESCE(signed_usd, 0)) AS gross_profit_usd,ARRAY_JOIN(ARRAY_SORT(ARRAY_AGG(DISTINCT received_symbol) FILTER (WHERE received_symbol IS NOT NULL)), ', ') AS profit_assets