-- forked from BlockTracker / pairs @ https://flipsidecrypto.xyz/BlockTracker/q/fcvlT3wl9dRk/pairs 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, pair 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, pair, 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 m.*, amount_in_raw / pow(10, c1.decimals) as amount_in, amount_out_raw / pow(10, c2.decimals) as amount_out, c1.symbol as symbol_in, c2.symbol as symbol_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 ) , prices as ( select date_trunc('hour', hour) as date, symbol, median(price) as usd_price from crosschain.price.ez_prices_hourly where blockchain = 'sei v2' and date >= '2024-06-12' group by 1 , 2 union all select date_trunc('hour', hour) as date, symbol, median(price) as usd_price from crosschain.price.ez_prices_hourly where blockchain ilike 'sei network' and date < '2024-06-12' and date >= '2024-05-27' group by 1 , 2 union all select date_trunc('hour', hour) as date, 'WSEI' as symbol, median(price) as usd_price from crosschain.price.ez_prices_hourly where blockchain = 'sei' and symbol = 'SEI' and date < '2024-06-12' and date >= '2024-05-27' group by 1 , 2 ) , add_usd_price as ( select f.* , case when symbol_in IN ('USDC','USDT') then amount_in else amount_in * p1.usd_price end as amount_in_usd, case when symbol_out IN ('USDC','USDT') then amount_in else amount_out * p2.usd_price end as amount_out_usd from final f left outer join prices p1 ON f.symbol_in = p1.symbol and date_trunc('hour', block_timestamp) = p1.date left outer join prices p2 ON f.symbol_out = p2.symbol and date_trunc('hour', block_timestamp) = p2.date ) select date_trunc('day', block_timestamp) as date, pair, count(DISTINCT tx_hash) as n_swaps, count(DISTINCT origin_from_address) as n_swappers, sum(case when amount_in_usd is not null then amount_in_usd else amount_out_usd end) as volume_usd, volume_usd * 0.003 as fee from add_usd_price where block_timestamp::date >= '2024-05-27' AND pair = 'USDC-WSEI' group by 1 , 2

Query	Creator	Created
APT CEX 2 copy copy aptos	Axelar Ambassadors	Aug 2, 2024
pairs copy sei prices ibc metadata	Axelar Ambassadors	Jul 25, 2024

Axelar Ambassadors

3 Members