-- forked from top 5 pools based on liquidity @ https://flipsidecrypto.xyz/edit/queries/612224b1-c4f3-4873-833a-22d7648bc9d5 ------------------------------------------------------------------------- ---------------------------------add & remove liquidity ----------------------- with token_info as ( select * from ( VALUES ('0xe30fedd158a2e3b13e9badaeabafc5516e95e8c7', 'WSEI',18), ('0xb75d0b03c06a926e488e2659df1a861f860bd3d1', 'USDT',6), ('0x3894085ef7ff0f0aedf52e2a2704928d1ec074f1', 'USDC',6), ('0x5f0e07dfee5832faa00c63f2d33a0d79150e8598', 'SEIYAN',6), ('0xc18b6a15fb0ceaf5eb18696eefcb5bc7b9107149', 'POPO',18), ('0x17fe1101af2dab1af0317d1ad1bd66cd06da4b5c', 'SeiPepe',18), ('0x263090d507377f47e944c4da0d4ed2eebecf59dd', 'SeilorV2',18), ('0xc6bc81a0e287cc8103cc002147a9d76cae4cd6e5', 'BALLZ',18), ('0x5cf6826140c1c56ff49c808a1a75407cd1df9423', 'iSEI',6), ('0x71d2115967111a399d5fb619929fa0fda1ee93f7', 'WASB',18), ('0x86fff10d3cd57d0b1e2abb1c21a081c4649c9718', 'SEIMOON',18), ('0x11131ccf0406ad0670f1e1ee37e85b66e8761757', 'Maga',18), ('0x29a1d64f61f333aeac33caf0ee18521002b3cbd2', 'DOB',18), ('0xb684e8fafdf9db710e044780a382d6fe9559f56b','STASH',18), ('0xf1db2416fd551f4b65c978d85efe02c0f14fb5ed','SEIMPLE',18), ('0x47b676e7ebce3b1961658334d4e09e23c87e787e','ROCKET',18), ('0x2556db239c49e7964b9e087b360821473cc6caf8','Ponk',18), ('0xd8549278b3db213f70f9bb3e9c7f5373280769a6','POPO (F2)',6) ) as a (token_address, symbol, decimals) ) , pools_created as ( select lower('0x' || substr(topics[1]::string, 27, 40) :: string) as token0, lower('0x' || substr(topics[2]::string, 27, 40) :: string) as token1, lower('0x' || substr(REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}')[0]::string, 25, 40 ) :: string) as pool_address from sei.core_evm.fact_event_logs where topics[0] = '0x0d3648bd0f6ba80134a33ba9275ac585d9d315f0ad8355cddefde31afa28d0e9' and contract_address = '0x71f6b49ae1558357bbb5a6074f1143c46cbca03d' -- dragonswapfactory and tx_status = 'SUCCESS' ) , pairs as ( select pool_address, token0, token1, b.symbol as symbol0, c.symbol as symbol1, concat(symbol0, '-' , symbol1) as pair from pools_created a join token_info b on a.token0 = b.token_address join token_info c on a.token1 = c.token_address ) , add_remove_liquidity as ( select 'add_liquidity' as event_type, block_timestamp, contract_address as pool_address, b.token0 as token, REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}') as segmented_data, livequery.utils.udf_hex_to_int(segmented_data[0])::int as amount from sei.core_evm.fact_event_logs a join pairs b on a.contract_address = b.pool_address where topics[0] = '0x4c209b5fc8ad50758f13e2e1088ba56a560dff690a1c6fef26394f4c03821c4f' and tx_status = 'SUCCESS' union all select 'add_liquidity' as event_type, block_timestamp, contract_address as pool_address, b.token1 as token, REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}') as segmented_data, livequery.utils.udf_hex_to_int(segmented_data[1])::int as amount from sei.core_evm.fact_event_logs a join pairs b on a.contract_address = b.pool_address where topics[0] = '0x4c209b5fc8ad50758f13e2e1088ba56a560dff690a1c6fef26394f4c03821c4f' and tx_status = 'SUCCESS' union all select 'remove_liquidity' as event_type, block_timestamp, contract_address as pool_address, b.token0 as token, REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}') as segmented_data, -1*livequery.utils.udf_hex_to_int(segmented_data[0])::int as amount from sei.core_evm.fact_event_logs a join pairs b on a.contract_address = b.pool_address where topics[0] = '0xdccd412f0b1252819cb1fd330b93224ca42612892bb3f4f789976e6d81936496' and tx_status = 'SUCCESS' union all select 'remove_liquidity' as event_type, block_timestamp, contract_address as pool_address, b.token1 as token, REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}') as segmented_data, -1*livequery.utils.udf_hex_to_int(segmented_data[1])::int as amount from sei.core_evm.fact_event_logs a join pairs b on a.contract_address = b.pool_address where topics[0] = '0xdccd412f0b1252819cb1fd330b93224ca42612892bb3f4f789976e6d81936496' and tx_status = 'SUCCESS' ) ----------------------------------------------------------------- ------------------------------Trades ----------------------------- , trades as ( with token_info as ( select * from ( VALUES ('0xe30fedd158a2e3b13e9badaeabafc5516e95e8c7', 'WSEI',18), ('0xb75d0b03c06a926e488e2659df1a861f860bd3d1', 'USDT',6), ('0x3894085ef7ff0f0aedf52e2a2704928d1ec074f1', 'USDC',6), ('0x5f0e07dfee5832faa00c63f2d33a0d79150e8598', 'SEIYAN',6), ('0xc18b6a15fb0ceaf5eb18696eefcb5bc7b9107149', 'POPO',18), ('0x17fe1101af2dab1af0317d1ad1bd66cd06da4b5c', 'SeiPepe',18), ('0x263090d507377f47e944c4da0d4ed2eebecf59dd', 'SeilorV2',18), ('0xc6bc81a0e287cc8103cc002147a9d76cae4cd6e5', 'BALLZ',18), ('0x5cf6826140c1c56ff49c808a1a75407cd1df9423', 'iSEI',6), ('0x71d2115967111a399d5fb619929fa0fda1ee93f7', 'WASB',18), ('0x86fff10d3cd57d0b1e2abb1c21a081c4649c9718', 'SEIMOON',18), ('0x11131ccf0406ad0670f1e1ee37e85b66e8761757', 'Maga',18), ('0x29a1d64f61f333aeac33caf0ee18521002b3cbd2', 'DOB',18), ('0xb684e8fafdf9db710e044780a382d6fe9559f56b','STASH',18), ('0xf1db2416fd551f4b65c978d85efe02c0f14fb5ed','SEIMPLE',18), ('0x47b676e7ebce3b1961658334d4e09e23c87e787e','ROCKET',18), ('0x2556db239c49e7964b9e087b360821473cc6caf8','Ponk',18), ('0xd8549278b3db213f70f9bb3e9c7f5373280769a6','POPO (F2)',6) ) as a (token_address, symbol, decimals) ) , pool_created as ( select lower('0x' || substr(topics[1]::string, 27, 40) :: string) as token0, lower('0x' || substr(topics[2]::string, 27, 40) :: string) as token1, lower('0x' || substr(REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}')[0]::string, 25, 40 ) :: string) as pool_address from sei.core_evm.fact_event_logs where topics[0] = '0x0d3648bd0f6ba80134a33ba9275ac585d9d315f0ad8355cddefde31afa28d0e9' and contract_address = '0x71f6b49ae1558357bbb5a6074f1143c46cbca03d' -- dragonswapfactory and tx_status = 'SUCCESS' ) , pairs as ( select pool_address, token0, token1, b.symbol as symbol0, c.symbol as symbol1, concat(symbol0, '-' , symbol1) as pair from pool_created a join token_info b on a.token0 = b.token_address join token_info c on a.token1 = c.token_address ) , swap as ( select block_timestamp, block_number, origin_from_address, origin_to_address, ORIGIN_FUNCTION_SIGNATURE, tx_hash, event_index, contract_address, lower('0x' || substr(topics[1]::string, 27, 40) :: string) as sender, lower('0x' || substr(topics[2]::string, 27, 40) :: string) as address_to, REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}') as segmented_data, livequery.utils.udf_hex_to_int(segmented_data[0]::string) :: int as amount0In, livequery.utils.udf_hex_to_int(segmented_data[1]::string) :: int as amount1In, livequery.utils.udf_hex_to_int(segmented_data[2]::string) :: int as amount0Out, livequery.utils.udf_hex_to_int(segmented_data[3]::string) :: int as amount1Out from sei.core_evm.fact_event_logs where topics[0]::string = '0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822' and tx_status = 'SUCCESS' ) , result as ( select a.* , token0, token1 from swap a join pairs b on a.contract_address = b.pool_address ) , main as ( SELECT block_number, block_timestamp, tx_hash, origin_from_address, origin_to_address, origin_function_signature, event_index, contract_address, sender, address_to, CASE -- fork from dbt Docs flipside WHEN amount0In <> 0 AND amount1In <> 0 AND amount0Out <> 0 THEN amount1In WHEN amount0In <> 0 THEN amount0In WHEN amount1In <> 0 THEN amount1In END AS amount_in_raw, CASE WHEN amount0Out <> 0 THEN amount0Out WHEN amount1Out <> 0 THEN amount1Out END AS amount_out_raw, CASE WHEN amount0In <> 0 AND amount1In <> 0 AND amount0Out <> 0 THEN token1 WHEN amount0In <> 0 THEN token0 WHEN amount1In <> 0 THEN token1 END AS token_in, CASE WHEN amount0Out <> 0 THEN token0 WHEN amount1Out <> 0 THEN token1 END AS token_out, 'Swap' AS event_name, 'DragonSwap' AS platform from result ) , final as ( select block_timestamp, tx_hash, contract_address as pool_address, token_in, c1.symbol as symbol_in, amount_in_raw / pow(10, c1.decimals) as amount_in, token_out, c2.symbol as symbol_out, amount_out_raw / pow(10, c2.decimals) as amount_out from main m left join token_info c1 ON m.token_in = c1.token_address left join token_info c2 ON m.token_out = c2.token_address ) select pool_address, token, sum(amount) as delta from ( select block_timestamp, pool_address, token_in as token, amount_in as amount from final union all select block_timestamp, pool_address, token_out as token, -amount_out as amount from final ) group by 1 , 2 ) , balance as ( select pool_address, token, sum(amount/ pow(10,b.decimals)) as delta from add_remove_liquidity a join token_info b on a.token = b.token_address group by 1 , 2 ) , prices as ( select symbol, price from crosschain.price.ez_prices_hourly where blockchain ilike '%sei%' and hour::date >= '2024-05-27' qualify row_number() over (partition by symbol order by hour desc) = 1 union all select 'WSEI' as symbol, price from crosschain.price.ez_prices_hourly where blockchain ilike 'cosmos' and symbol = 'SEI' and hour::date >= '2024-05-27' qualify row_number() over (order by hour desc) = 1 union all select 'iSEI' as symbol, price * 1.01 as price from crosschain.price.ez_prices_hourly where blockchain ilike 'cosmos' and symbol = 'SEI' and hour::date >= '2024-05-27' qualify row_number() over (order by hour desc) = 1 ) , ---------------------------combine trades and add_reomove liquidity ---------------- delta_balance as ( select pool_address, token, sum(delta) as amount from ( select pool_address, token, delta from balance union all select pool_address, token, delta from trades ) group by 1 , 2 ) select z.pair as pairs, round(sum(case when x.symbol IN ('USDC', 'USDT') then amount else amount * y.price end),2) as amount_usd from ( select pool_address, token, b.symbol, amount, from delta_balance a left join token_info b ON a.token = token_address ) x left join prices y ON x.symbol = y.symbol left join pairs z ON x.pool_address = z.pool_address where x.pool_address IN ( '0x8ba502d9243fa5e44678044098be9782cebafda5', '0x8d5261cff8d63e71c772574eba63e64e6726ee06', '0xc75c669a62a7ece0c8d37904b747970467432ad3', '0xe7fef2854ac5b89f116d1d8e93ad8506d85d8f31', '0xff64743312fcf27c6a4ef450c821a98e9e62d945' ) group by 1 order by 2 desc

Query	Creator	Created
liquidity copy sei ibc	BlockTracker	Jun 3, 2024
unique swapper sei prices ibc metadata	BlockTracker	May 31, 2024
[test] swap overview sei prices ibc metadata	BlockTracker	Jul 1, 2024
top projects based on tvl sei ibc	BlockTracker	May 30, 2024
engaged users sei ibc	BlockTracker	Aug 22, 2023
seaswap sei ibc	BlockTracker	Aug 27, 2023
holders sei ibc	BlockTracker	Sep 24, 2023

BlockTracker