Query editor
WITH loan_legs AS (SELECT chain, evt_tx_hash AS tx_hash, evt_block_date AS block_date, 'Morpho Blue' AS provider, caller AS receiver, evt_tx_to AS tx_to, evt_tx_from AS tx_sender, token AS asset, assets AS amount_raw, CAST(0 AS uint256) AS fee_rawFROM morpho_blue_multichain.morphoblue_evt_flashloan WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION ALL SELECT chain, evt_tx_hash, evt_block_date, 'Aave V3', target, evt_tx_to, evt_tx_from, asset, amount, premium FROM aave_v3_multichain.pool_evt_flashloan WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION ALL SELECT chain, evt_tx_hash, evt_block_date, 'Aave V3', target, evt_tx_to, evt_tx_from, asset, amount, premium FROM aave_v3_multichain.l2pool_evt_flashloan WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION ALL SELECT chain, evt_tx_hash, evt_block_date, 'Balancer V2', recipient, evt_tx_to, evt_tx_from, token, amount, feeAmount FROM balancer_v2_multichain.vault_evt_flashloan WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION ALL SELECT chain, evt_tx_hash, evt_block_date, 'Swaap V2', recipient, evt_tx_to, evt_tx_from, token, amount, feeAmount FROM swaap_v2_multichain.vault_evt_flashloan WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION ALL SELECT chain, evt_tx_hash, evt_block_date, 'Aave V2', target, evt_tx_to, evt_tx_from, asset, amount, premium FROM aave_v2_multichain.lendingpool_evt_flashloan WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAY),loan_tx AS (SELECT chain, tx_hash, MIN(block_date) AS block_date, MIN(provider) AS provider, MIN(receiver) AS receiver, MIN(tx_to) AS tx_to, MIN(tx_sender) AS tx_sender, MIN(asset) AS asset, SUM(amount_raw) AS amount_raw, SUM(fee_raw) AS fee_raw, COUNT(DISTINCT provider) AS provider_count, COUNT(DISTINCT receiver) AS receiver_count, COUNT(DISTINCT asset) AS asset_countFROM loan_legsWHERE chain IN ('ethereum','base','arbitrum','optimism','polygon','bnb','avalanche_c','gnosis','linea','scroll','sonic','monad','unichain')GROUP BY 1, 2),receiver_sender_counts AS (SELECT chain, receiver, COUNT(DISTINCT tx_sender) AS distinct_senders FROM loan_tx GROUP BY 1, 2),entrypoint_sender_counts AS (SELECT chain, tx_to, COUNT(DISTINCT tx_sender) AS distinct_senders FROM loan_tx GROUP BY 1, 2),method_base AS (SELECT l.* FROM loan_tx lJOIN receiver_sender_counts r ON r.chain = l.chain AND r.receiver = l.receiverJOIN entrypoint_sender_counts e ON e.chain = l.chain AND e.tx_to = l.tx_toWHERE l.provider_count = 1 AND l.receiver_count = 1 AND l.asset_count = 1 AND r.distinct_senders <= 10 AND e.distinct_senders <= 10),lending_tx AS (SELECT DISTINCT chain, evt_tx_hash AS tx_hash FROM morpho_blue_multichain.morphoblue_evt_liquidate WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM morpho_blue_multichain.morphoblue_evt_borrow WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM morpho_blue_multichain.morphoblue_evt_repay WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM morpho_blue_multichain.morphoblue_evt_supply WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM morpho_blue_multichain.morphoblue_evt_withdraw WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM morpho_blue_multichain.morphoblue_evt_withdrawcollateral WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM aave_v3_multichain.pool_evt_liquidationcall WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM aave_v3_multichain.l2pool_evt_liquidationcall WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM aave_v3_multichain.pool_evt_borrow WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM aave_v3_multichain.pool_evt_repay WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM aave_v3_multichain.pool_evt_supply WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM aave_v3_multichain.pool_evt_withdraw WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM aave_v3_multichain.l2pool_evt_borrow WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM aave_v3_multichain.l2pool_evt_repay WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM aave_v3_multichain.l2pool_evt_supply WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM aave_v3_multichain.l2pool_evt_withdraw WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM aave_v2_multichain.lendingpool_evt_liquidationcall WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM aave_v2_multichain.lendingpool_evt_borrow WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM aave_v2_multichain.lendingpool_evt_repay WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM aave_v2_multichain.lendingpool_evt_deposit WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAYUNION SELECT DISTINCT chain, evt_tx_hash FROM aave_v2_multichain.lendingpool_evt_withdraw WHERE evt_block_date >= CURRENT_DATE - INTERVAL '30' DAY),aggregator_tx AS (SELECT DISTINCT a.blockchain AS chain, a.tx_hash FROM dex_aggregator.trades aINNER JOIN method_base b ON b.chain = a.blockchain AND b.tx_hash = a.tx_hashWHERE a.blockchain IN ('ethereum','base','arbitrum','optimism','polygon','bnb','avalanche_c','gnosis','linea','scroll','monad','unichain')AND a.block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '30' DAY) AND a.block_date >= CURRENT_DATE - INTERVAL '30' DAY),dex_stats AS (SELECT d.blockchain AS chain, d.tx_hash, COUNT(*) AS trade_count,MIN_BY(d.token_sold_address, d.evt_index) AS first_token_sold, MAX_BY(d.token_bought_address, d.evt_index) AS last_token_bought,COUNT_IF(d.token_sold_address = b.asset) AS loop_sell_legs, COUNT_IF(d.token_bought_address = b.asset) AS loop_buy_legs,SUM(CASE WHEN d.token_sold_address = b.asset THEN d.token_sold_amount ELSE 0 END) AS loop_sold_amount,SUM(CASE WHEN d.token_bought_address = b.asset THEN d.token_bought_amount ELSE 0 END) AS loop_bought_amount,SUM(CASE WHEN d.token_sold_address = b.asset THEN d.amount_usd ELSE 0 END) / NULLIF(SUM(CASE WHEN d.token_sold_address = b.asset THEN d.token_sold_amount ELSE 0 END), 0) AS loop_token_price,COUNT_IF(d.amount_usd IS NULL OR d.amount_usd <= 0) AS unpriced_trade_legs