-- forked from polygon dex activity: lp @ https://flipsidecrypto.xyz/edit/queries/20d1eeda-d07c-41e3-8e1a-7c2e22bb7c0f -- forked from polygon dex activity: swaps @ https://flipsidecrypto.xyz/edit/queries/652fe3f0-e79f-4cd4-8270-23ae5bceac8e -- this will cover swaps from -- uniswapv3; curve; balancerv2; sushi with mb as ( select min(block_number) as minblock, max(block_number) as maxblock from arbitrum.core.fact_blocks where block_timestamp > '{{start_date}}' and block_timestamp <= '{{end_date}}' ), unipools as ( with raw as ( SELECT livequery.live.udf_api( 'POST', 'https://api.thegraph.com/subgraphs/name/messari/uniswap-v3-arbitrum', {'Content-Type': 'application/json'}, {'query':'{ liquidityPools(first: 1000, orderBy: totalValueLockedUSD, orderDirection: desc) { id totalLiquidity name inputTokens { id symbol }} }', 'variables':{} } ) as rawoutput ) select value:id as pool_address, value:name as pool_name, value:inputTokens[0]:id as token0_address, value:inputTokens[1]:id as token1_address, value:inputTokens[0]:symbol as token0_symbol, value:inputTokens[1]:symbol as token1_symbol from raw, lateral flatten(input => parse_json(rawoutput:data:data:liquidityPools)) ), curvepools as ( with raw as ( SELECT livequery.live.udf_api( 'POST', 'https://api.thegraph.com/subgraphs/name/messari/curve-finance-arbitrum', {'Content-Type': 'application/json'}, {'query':'{\n liquidityPools(first: 50, orderBy: totalValueLockedUSD, orderDirection: desc) { \n id\n totalValueLockedUSD\n name\n inputTokens { \n id \n symbol \n }}\n}', 'variables':{} } ) as rawoutput ) select value:id as pool_address, value:name as pool_name, value:inputTokens[0]:id as token0_address, value:inputTokens[1]:id as token1_address, value:inputTokens[2]:id as token2_address, value:inputTokens[0]:symbol as token0_symbol, value:inputTokens[1]:symbol as token1_symbol, value:inputTokens[2]:symbol as token2_symbol from raw, lateral flatten(input => parse_json(rawoutput:data:data:liquidityPools)) ), sushipools as ( with raw as ( SELECT livequery.live.udf_api( 'POST', 'https://api.thegraph.com/subgraphs/name/messari/sushiswap-arbitrum', {'Content-Type': 'application/json'}, {'query':'{ liquidityPools(first: 1000, orderBy: totalValueLockedUSD, orderDirection: desc) { id totalValueLockedUSD name inputTokens { id symbol } } }', 'variables':{} } ) as rawoutput ) select lower(value:id) as pool_address, value:name as pool_name, value:inputTokens[0]:id as token0_address, value:inputTokens[1]:id as token1_address, value:inputTokens[0]:symbol as token0_symbol, value:inputTokens[1]:symbol as token1_symbol from raw, lateral flatten(input => parse_json(rawoutput:data:data:liquidityPools)) ), -- swap txns uniswaps as ( select tx_hash, case when substr(event_inputs:amount0,0,3)::integer > 0 then event_inputs:amount0::float else event_inputs:amount1::float end as amount_in, case when substr(event_inputs:amount1,0,3)::integer < 0 then event_inputs:amount1::float * -1 else event_inputs:amount0::float * -1 end as amount_out, 'uniswap' as platform from arbitrum.core.fact_event_logs logs where logs.topics[0] = '0xc42079f94a6350d7e6235f29174924f928cc2ac818eb64fed8004e115fbcca67' and contract_address in (select lower(pool_address) from unipools) and logs.block_number > (select minblock from mb) and logs.block_number <= (select maxblock from mb) ), curveswaps as ( select tx_hash, event_inputs:tokens_sold::float as amount_in, event_inputs:tokens_bought::float as amount_out, 'curve' as platform from arbitrum.core.fact_event_logs where topics[0] in ( '0x8b3e96f2b889fa771c53c981b40daf005f63f637f1869f707052d15a3dd97140', -- exchange '0xb2e76ae99761dc136e598d4a629bb347eccb9532a5f8bbd72e18467c3c34cc98', -- exchange '0xd013ca23e77a65003c2c659c5442c00c805371b7fc1ebd4c206c41d1536bd90b' -- exchange underlying ) and block_number > (select minblock from mb) and block_number <= (select maxblock from mb) and contract_address in (select lower(pool_address) from curvepools) ), balancerswaps as ( select tx_hash, event_inputs:amountIn::float as amount_in, event_inputs:amountOut::float as amount_out, 'balancer' as platform from arbitrum.core.fact_event_logs where topics[0] = '0x2170c741c41531aec20e7c107c24eecfdd15e69c9bb0a8dd37b1840b9e0b207b' and block_number > (select minblock from mb) and block_number <= (select maxblock from mb) and contract_address = lower('0xBA12222222228d8Ba445958a75a0704d566BF2C8') ), sushiswaps as ( SELECT tx_hash, case when event_inputs:amount0In::float > 0 then event_inputs:amount0In::float else event_inputs:amount1In::float end as amount_in, case when event_inputs:amount1Out::float > 0 then event_inputs:amount1Out::float else event_inputs:amount0Out::float end as amount_out, 'sushi' as platform from arbitrum.core.fact_event_logs where block_number > (select minblock from mb) and block_number <= (select maxblock from mb) and event_name = 'Swap' and contract_address in (select pool_address from sushipools) ), allswaps as ( select * from uniswaps UNION select * from curveswaps UNION select * from balancerswaps UNION select * from sushiswaps ), allpools as ( select pool_address::VARCHAR as pool_address, pool_name::VARCHAR as pool_name, 'uniswap' as platform from unipools UNION select pool_address::VARCHAR as pool_address, pool_name::VARCHAR as pool_name, 'sushiswap' as platform from sushipools UNION select pool_address::VARCHAR as pool_address, pool_name::VARCHAR as pool_name, 'curve' as platform from curvepools UNION select lower('0xba12222222228d8ba445958a75a0704d566bf2c8')::VARCHAR as pool_address, 'balancer router'::VARCHAR as pool_name, 'balancer' as platform from dual ), eth_deposits as ( SELECT tx_hash, ORIGIN_FROM_ADDRESS as USER_ADDRESS, ETH_TO_ADDRESS as pool_address, NULL as CONTRACT_ADDRESS, 'ETH' as symbol, AMOUNT as amount_dep_lp, AMOUNT_USD as amount_dep_usd FROM arbitrum.core.ez_eth_transfers xfers where block_number > (select minblock from mb) and block_number <= (select maxblock from mb) AND ETH_TO_ADDRESS IN (SELECT POOL_ADDRESS FROM allpools) AND -- NOTE: This extreme removal may impact multicalls that transfer & trade in same tx. -- This may also miss deposit aggregation, where you transfer to a router that then transfers -- to LPs, e.g., Gamma.xyz Uniswap v3 Vaults. TX_HASH NOT IN (SELECT TX_HASH FROM allswaps) ), eth_deps as ( select USER_ADDRESS, CONTRACT_ADDRESS, symbol, pool_address, sum(amount_dep_lp) as amount_deposited_lp, sum(amount_dep_usd) as amount_deposited_usd, count(1) as num_deposits from eth_deposits GROUP BY USER_ADDRESS, CONTRACT_ADDRESS, symbol, pool_address ), eth_withdraws as ( SELECT tx_hash, ETH_TO_ADDRESS as USER_ADDRESS, ETH_FROM_ADDRESS as pool_address, NULL as CONTRACT_ADDRESS, 'ETH' as symbol, AMOUNT as amount_withdr_lp, AMOUNT_USD as amount_withdr_usd FROM arbitrum.core.ez_eth_transfers xfers WHERE block_number > (select minblock from mb) and block_number <= (select maxblock from mb) AND ETH_FROM_ADDRESS IN (SELECT POOL_ADDRESS FROM allpools) AND -- NOTE: This extreme removal may impact multicalls that transfer & trade in same tx. -- This may also miss deposit aggregation, where you transfer to a router that then transfers -- to LPs, e.g., Gamma.xyz Uniswap v3 Vaults. TX_HASH NOT IN (SELECT TX_HASH FROM allswaps) ), eth_withdrs as ( SELECT USER_ADDRESS, CONTRACT_ADDRESS, symbol, pool_address, sum(amount_withdr_lp) as amount_withdrawn_lp, sum(amount_withdr_usd) as amount_withdrawn_usd, count(*) as num_withdrawals from eth_withdraws GROUP BY USER_ADDRESS, CONTRACT_ADDRESS, symbol, pool_address ), token_deposits AS ( SELECT ORIGIN_FROM_ADDRESS as USER_ADDRESS, TO_ADDRESS as pool_address, CONTRACT_ADDRESS, xfers.symbol, sum(amount) as amount_deposited_lp, sum(amount_usd) as amount_deposited_usd, count(*) as num_deposits FROM arbitrum.core.ez_token_transfers xfers where block_number > (select minblock from mb) and block_number <= (select maxblock from mb) AND TO_ADDRESS IN (SELECT POOL_ADDRESS FROM allpools) AND -- NOTE: This extreme removal may impact multicalls that transfer & trade in same tx. -- This may also miss deposit aggregation, where you transfer to a router that then transfers -- to LPs, e.g., Gamma.xyz Uniswap v3 Vaults. TX_HASH NOT IN (SELECT TX_HASH FROM allswaps) AND TX_HASH NOT IN (SELECT TX_HASH FROM eth_deposits) GROUP BY USER_ADDRESS, CONTRACT_ADDRESS, symbol, TO_ADDRESS ), token_withdraws AS ( SELECT TO_ADDRESS as USER_ADDRESS, FROM_ADDRESS as pool_address, CONTRACT_ADDRESS, xfers.symbol, sum(amount) as amount_withdrawn_lp, sum(amount_usd) as amount_withdrawn_usd, count(*) as num_withdrawals FROM arbitrum.core.ez_token_transfers xfers where block_number > (select minblock from mb) and block_number <= (select maxblock from mb) AND FROM_ADDRESS IN (SELECT POOL_ADDRESS FROM allpools) AND -- NOTE: This extreme removal may impact multicalls that transfer & trade in same tx. -- This may also miss deposit aggregation, where you transfer to a router that then transfers -- to LPs, e.g., Gamma.xyz Uniswap v3 Vaults. TX_HASH NOT IN (SELECT TX_HASH FROM allswaps) AND TX_HASH NOT IN (SELECT TX_HASH FROM eth_withdraws) GROUP BY USER_ADDRESS, CONTRACT_ADDRESS, symbol, FROM_ADDRESS ), lp_combined AS ( SELECT * FROM token_deposits deps NATURAL FULL OUTER JOIN token_withdraws wdrs UNION SELECT * FROM eth_deps edeps NATURAL FULL OUTER JOIN eth_withdrs ewdrs ) SELECT user_address, count(distinct(platform)) as n_protocols, sum(num_deposits) as n_deposits, sum(num_withdrawals) as n_withdrawals, sum(amount_deposited_usd) as deposit_volume_usd, sum(amount_withdrawn_usd) as withdrawal_volume_usd FROM lp_combined lpc JOIN allpools spd ON lpc.pool_address = spd.pool_address WHERE user_address IS NOT NULL -- rare fluke in garbage coin with no price. AND user_address NOT IN (select address from arbitrum.core.dim_labels) GROUP BY user_address;

arbitrum core stats