Query editor
WITHknown_boundaries(borrower, fixed_beneficiary, boundary_rule, known_strategy, known_explanation) AS (VALUES(0xDF5A4AA468311F70BFD61FACE904BACB5DBCA1F5, CAST(NULL AS VARBINARY), 'borrower', 'DODO versus another Base pool', 'Borrowed from Morpho, traded through DODO and another pool, returned to the starting token, repaid Morpho, and kept the difference.'),(0x49177C391CB71677D5AC9C14BFDB7260F35A4527, CAST(NULL AS VARBINARY), 'borrower', 'DODO versus another Base pool', 'Borrowed from Morpho, traded through DODO and another pool, returned to the starting token, repaid Morpho, and kept the difference.'),(0x6FE749508C3991B9CAAB35D49066EF8E7882D6A7, CAST(NULL AS VARBINARY), 'borrower', 'DODO versus another Base pool', 'Borrowed from Morpho, traded through DODO and another pool, returned to the starting token, repaid Morpho, and kept the difference.'),(0x41B722E002ECD1E9AD70A6CFB01E30B8738715BC, 0x4502B8E2332E2076945D33B1C561D3CD6A138040, 'fixed beneficiary', 'Uniswap V4 versus Uniswap V3', 'Borrowed from Morpho, bought through one Uniswap version, sold through the other, repaid Morpho, and sent the difference to a helper contract.'),(0x52477C72741B5DF2DBA46EBB6A96601931D3280F, 0x03FAFA0B33BB05056DDC156DB1A4DF47D681539A, 'fixed beneficiary', 'Kyber-routed multi-pool arbitrage', 'Borrowed from Morpho, used Kyber to route through several exchange pools, repaid Morpho, and sent the small difference to a recurring recipient.'),(0xB3C21B3CAB3DF9294910286DE6C84F3CADCF8C0A, CAST(NULL AS VARBINARY), 'transaction sender', 'Kyber-routed multi-pool arbitrage', 'Borrowed from Morpho, used Kyber to route through several exchange pools, repaid Morpho, and paid the difference to the transaction sender.')),morpho_legs 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, token AS token_address,assets AS amount_raw, CAST(0 AS uint256) AS fee_rawFROM morpho_blue_base.morphoblue_evt_flashloanWHERE evt_block_date >= CURRENT_DATE - INTERVAL '90' DAY),aave_raw AS (SELECT evt_tx_hash, evt_index, evt_block_time, evt_block_date, initiator, asset, amount, premiumFROM aave_v3_base.l2pool_evt_flashloanWHERE evt_block_date >= CURRENT_DATE - INTERVAL '90' DAYUNIONSELECT evt_tx_hash, evt_index, evt_block_time, evt_block_date, initiator, asset, amount, premiumFROM aave_v3_base.pool_evt_flashloanWHERE evt_block_date >= CURRENT_DATE - INTERVAL '90' DAY),aave_legs AS (SELECT 'Aave V3' AS provider, evt_tx_hash, evt_block_time, evt_block_date,initiator AS borrower, asset AS token_address, amount AS amount_raw, premium AS fee_rawFROM aave_raw),loan_legs AS (SELECT * FROM morpho_legsUNION ALLSELECT * FROM aave_legs),priced_legs AS (SELECT l.*,p.symbol AS borrowed_symbol,CAST(l.amount_raw AS DOUBLE) / POW(10, COALESCE(p.decimals, 18)) * p.price AS amount_usd,CAST(l.fee_raw AS DOUBLE) / POW(10, COALESCE(p.decimals, 18)) * p.price AS fee_usdFROM loan_legs lLEFT JOIN prices.day pON p.blockchain = 'base'AND p.contract_address = l.token_addressAND p.timestamp = CAST(l.block_date AS TIMESTAMP)),flash_tx AS (SELECT l.provider, l.tx_hash, MIN(l.block_time) AS block_time, MIN(l.block_date) AS block_date,MIN(l.borrower) AS borrower, t."from" AS tx_sender,ARRAY_JOIN(ARRAY_SORT(ARRAY_AGG(DISTINCT l.borrowed_symbol)), ', ') AS borrowed_tokens,SUM(l.amount_usd) AS borrowed_usd, SUM(l.fee_usd) AS provider_fee_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,k.fixed_beneficiary, k.boundary_rule, k.known_strategy, k.known_explanationFROM priced_legs lJOIN base.transactions tON t.hash = l.tx_hashAND t.block_date >= CURRENT_DATE - INTERVAL '90' DAYLEFT JOIN known_boundaries k ON k.borrower = l.borrowerLEFT JOIN prices.day ep