Query editor
WITHloan_events AS (SELECT 'Morpho Blue' AS provider, evt_tx_hash AS tx_hash, evt_block_time AS block_time,evt_block_date AS block_date, caller AS borrower, contract_address AS provider_addressFROM morpho_blue_base.morphoblue_evt_flashloanWHERE evt_block_date >= CURRENT_DATE - INTERVAL '90' DAYUNION ALLSELECT 'Aave V3' AS provider, evt_tx_hash, evt_block_time, evt_block_date,initiator AS borrower, contract_address AS provider_addressFROM aave_v3_base.l2pool_evt_flashloanWHERE evt_block_date >= CURRENT_DATE - INTERVAL '90' DAYUNIONSELECT 'Aave V3' AS provider, evt_tx_hash, evt_block_time, evt_block_date,initiator AS borrower, contract_address AS provider_addressFROM aave_v3_base.pool_evt_flashloanWHERE evt_block_date >= CURRENT_DATE - INTERVAL '90' DAY),flash_tx AS (SELECT provider, tx_hash, MIN(block_time) AS block_time, MIN(block_date) AS block_date,MIN(borrower) AS borrower, MIN(provider_address) AS provider_addressFROM loan_eventsGROUP BY 1, 2),repayment AS (SELECT f.tx_hash, MAX(tt.evt_index) AS repayment_evt_indexFROM flash_tx fJOIN tokens.transfers ttON 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.tx_hash = f.tx_hashAND tt."from" = f.borrowerAND tt."to" = f.provider_addressAND tt.evt_index IS NOT NULLGROUP BY 1),payout AS (SELECT f.tx_hash,COUNT(*) AS payout_transfers,COUNT_IF(tt.amount_usd IS NOT NULL) AS priced_payout_transfers,SUM(COALESCE(tt.amount_usd, 0)) AS gross_payout_usd,ARRAY_JOIN(ARRAY_AGG(CONCAT(COALESCE(tt.symbol, '?'), ' ', CAST(ROUND(tt.amount, 8) AS VARCHAR), ' -> 0x', TO_HEX(tt."to")) ORDER BY tt.evt_index), ' | ') AS payout_evidence,ARRAY_JOIN(ARRAY_SORT(ARRAY_AGG(DISTINCT CONCAT('0x', TO_HEX(tt."to")))), ', ') AS payout_recipientsFROM flash_tx fJOIN repayment r ON r.tx_hash = f.tx_hashJOIN tokens.transfers ttON 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.tx_hash = f.tx_hashAND tt."from" = f.borrowerAND tt."to" NOT IN (f.borrower, f.provider_address, 0x0000000000000000000000000000000000000000)AND tt.evt_index > r.repayment_evt_indexGROUP BY 1),scored AS (