Query editor
WITHvalidated_strategies(borrower, fixed_beneficiary, route_family) AS (VALUES(0xDF5A4AA468311F70BFD61FACE904BACB5DBCA1F5, 0xDF5A4AA468311F70BFD61FACE904BACB5DBCA1F5, 'DODO / secondary-pool arbitrage'),(0x49177C391CB71677D5AC9C14BFDB7260F35A4527, 0x49177C391CB71677D5AC9C14BFDB7260F35A4527, 'DODO / secondary-pool arbitrage'),(0x6FE749508C3991B9CAAB35D49066EF8E7882D6A7, 0x6FE749508C3991B9CAAB35D49066EF8E7882D6A7, 'DODO / secondary-pool arbitrage'),(0x41B722E002ECD1E9AD70A6CFB01E30B8738715BC, 0x4502B8E2332E2076945D33B1C561D3CD6A138040, 'Uniswap V4 / V3 arbitrage'),(0x52477C72741B5DF2DBA46EBB6A96601931D3280F, 0x03FAFA0B33BB05056DDC156DB1A4DF47D681539A, 'Kyber-routed arbitrage'),(0xB3C21B3CAB3DF9294910286DE6C84F3CADCF8C0A, CAST(NULL AS VARBINARY), 'Kyber-routed arbitrage')),morpho 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_flashloan WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' 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_flashloan WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNIONSELECT evt_tx_hash, evt_index, evt_block_time, evt_block_date, initiator, asset, amount, premiumFROM aave_v3_base.pool_evt_flashloan WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAY),aave AS (SELECT 'Aave V3', evt_tx_hash, evt_block_time, evt_block_date, initiator, asset, amount, premium FROM aave_raw),priced_legs AS (SELECT l.*,CAST(l.amount_raw AS DOUBLE) / POW(10, COALESCE(p.decimals, 18)) * p.price AS borrowed_usd,CAST(l.fee_raw AS DOUBLE) / POW(10, COALESCE(p.decimals, 18)) * p.price AS provider_fee_usdFROM (SELECT * FROM morpho UNION ALL SELECT * FROM aave) lLEFT JOIN prices.day p ON 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, SUM(l.borrowed_usd) AS borrowed_usd, SUM(l.provider_fee_usd) AS provider_fee_usd,t."from" AS tx_sender, v.route_family,CASE WHEN v.borrower IS NULL THEN NULL WHEN v.fixed_beneficiary IS NOT NULL THEN v.fixed_beneficiary ELSE t."from" END AS beneficiary,(CAST(t.gas_used AS DOUBLE) * CAST(t.gas_price AS DOUBLE) + CAST(t.l1_fee AS DOUBLE)) / 1e18 * ep.price AS gas_cost_usdFROM priced_legs lJOIN base.transactions t ON t.hash = l.tx_hash AND t.block_date >= CURRENT_DATE - INTERVAL '30' DAYLEFT JOIN validated_strategies v ON v.borrower = l.borrowerLEFT JOIN prices.day ep ON ep.blockchain = 'base' AND ep.contract_address = 0x0000000000000000000000000000000000000000AND ep.timestamp = CAST(l.block_date AS TIMESTAMP)GROUP BY 1, 2, 8, 9, 10, 11),hashes AS (SELECT tx_hash FROM flash_tx),dex_tx AS (SELECT d.tx_hash FROM dex.trades d JOIN hashes h ON h.tx_hash = d.tx_hashWHERE d.blockchain = 'base' AND d.block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '30' DAY)AND d.block_date >= CURRENT_DATE - INTERVAL '30' DAY GROUP BY 1),liq AS (SELECT evt_tx_hash AS tx_hash FROM morpho_blue_base.morphoblue_evt_liquidate WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT evt_tx_hash FROM aave_v3_base.l2pool_evt_liquidationcall WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT evt_tx_hash FROM aave_v3_base.pool_evt_liquidationcall WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAY),borrow AS (SELECT evt_tx_hash AS tx_hash FROM morpho_blue_base.morphoblue_evt_borrow WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT evt_tx_hash FROM aave_v3_base.l2pool_evt_borrow WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAY