Query editor
WITHknown_boundaries(borrower, fixed_beneficiary, boundary_rule, known_strategy) AS (VALUES(0xDF5A4AA468311F70BFD61FACE904BACB5DBCA1F5, CAST(NULL AS VARBINARY), 'borrower', 'DODO versus secondary-pool arbitrage'),(0x49177C391CB71677D5AC9C14BFDB7260F35A4527, CAST(NULL AS VARBINARY), 'borrower', 'DODO versus secondary-pool arbitrage'),(0x6FE749508C3991B9CAAB35D49066EF8E7882D6A7, CAST(NULL AS VARBINARY), 'borrower', 'DODO versus secondary-pool arbitrage'),(0x41B722E002ECD1E9AD70A6CFB01E30B8738715BC, 0x4502B8E2332E2076945D33B1C561D3CD6A138040, 'fixed beneficiary', 'Uniswap V4 versus V3 arbitrage'),(0x52477C72741B5DF2DBA46EBB6A96601931D3280F, 0x03FAFA0B33BB05056DDC156DB1A4DF47D681539A, 'fixed beneficiary', 'Kyber-routed multi-venue arbitrage'),(0xB3C21B3CAB3DF9294910286DE6C84F3CADCF8C0A, CAST(NULL AS VARBINARY), 'transaction sender', 'Kyber-routed multi-venue arbitrage')),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.*,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,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_strategyFROM 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 epON ep.blockchain = 'base'AND ep.contract_address = 0x0000000000000000000000000000000000000000