with 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}')[1]::string, 25, 40 ) :: string) as pool_address from kaia.core.fact_event_logs where topics[0] = '0xf04da67755adf58739649e2fb9949a6328518141b7ac9e44aa10320688b04900' and contract_address = '0x8c7d3063579bdb0b90997e18a770eae32e1ebb08' --kaiaFactory and tx_succeeded and block_timestamp::date >= '2024-05-07' ) , swap as ( select block_timestamp, block_number, origin_from_address, origin_to_address, ORIGIN_FUNCTION_SIGNATURE, tx_hash, event_index, contract_address, REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}') as segmented_data, livequery.utils.udf_hex_to_int(segmented_data[0]::string) :: int as type_in, case when type_in = 0 then livequery.utils.udf_hex_to_int(segmented_data[2]::string) :: int else livequery.utils.udf_hex_to_int(segmented_data[1]::string) :: int end as amountIn, case when type_in = 0 then livequery.utils.udf_hex_to_int(segmented_data[1]::string) :: int else livequery.utils.udf_hex_to_int(segmented_data[2]::string) :: int end as amountout, case when type_in = 0 then lower('0x' || substr(topics[2]::string, 27, 40) :: string) else lower('0x' || substr(topics[1]::string, 27, 40) :: string) end as token_in, case when type_in = 0 then lower('0x' || substr(topics[1]::string, 27, 40) :: string) else lower('0x' || substr(topics[2]::string, 27, 40) :: string) end as token_out --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 kaia.core.fact_event_logs where topics[0]::string = '0x0fe977d619f8172f7fdbe8bb8928ef80952817d96936509f67d66346bc4cd10f' and tx_succeeded and contract_address IN (select pool_address from pool_created) and block_timestamp::date >= '2024-05-07' ) , token_info as ( select * from ( VALUES ('0x19aac5f612f524b754ca7e7c41cbfa2e981a4432', 'WKLAY',18), ('0x7eee60a000986e9efe7f5c90340738558c24317b','PER',18), ('0xe2053bcf56d2030d2470fb454574237cf9ee3d4b' ,'USDC.e',6), ('0x1afa932f90bf84cce04116d3370165acd7257cc5', 'KKAKDOG',18), ('0xbab1349e8dcb0f6175909c914ceb1097c2fb70f2','DOGS' ,18), ('0x5c13e303a62fc5dedf5b52d66873f2e59fedadc2','USDT',6), ( '0x608792deb376cce1c9fa4d0e6b7b44f507cffa6a', 'USDC',6), ('0x9025095263d1e548dc890a7589a4c78038ac40ab' , 'USDT',6), ('0x0f58d0abaae2f586b0d3b6d045305463e89ba603' ,'KKULP' ,18), ( '0x0f921c39efd98809fe6d20a88a4357454578987a','M-BTC' ,18), ( '0x608e8512d31cae43cd8058d81e6b56203a112539', 'PING',18), ('0x8882ec400e9348ff60ae85d3d90a93cf97ce8869','MPING',18), ('0x0a3bb08b3a15a19b4de82f8acfc862606fb69a2d','iUSD',18), ('0x4cad05f5afdd4bfc072e356e88b05c33316bb1c5','KPING',18), ('0xe3f85b41a284ed965826adc98365e70e67a2f556','KKAK',18), ('0xb242cb981952c183421e4ac9b0d4861c27d9dc73','DPING',18) ) as a (token_address, symbol, decimals) ), prices as ( select date_trunc('day', hour) as date, symbol, median(price) as usd_price from kaia.price.ez_prices_hourly group by 1 , 2 ) , base as ( select block_timestamp, tx_hash, origin_from_address, token_in, token_out, b.symbol as symbol_in, c.symbol as symbol_out, amountin/pow(10,b.decimals) as amount_in, amountout/pow(10,c.decimals) as amount_out from swap a left join token_info b ON a.token_in = b.token_address left join token_info c ON a.token_out = c.token_address ) , final as ( select block_timestamp, tx_hash, origin_from_address, token_in, token_out, symbol_in, symbol_out, amount_in, amount_out, case when symbol_in IN ('iUSD','USDT','USDC','USDC.e') then amount_in else amount_in * p1.usd_price end as amount_in_usd, case when symbol_out IN ('iUSD','USDT','USDC','USDC.e') then amount_out else amount_out * p2.usd_price end as amount_out_usd, coalesce(amount_in_usd,amount_out_usd) as volume_usd from base a left join prices p1 ON date_trunc('day', block_timestamp) = p1.date and a.symbol_in = p1.symbol left join prices p2 ON date_trunc('day', block_timestamp) = p2.date and a.symbol_out = p2.symbol ) select CASE WHEN volume_usd < 10 THEN 'less than 10$' WHEN volume_usd < 100 THEN '10$ < volume < 100$' WHEN volume_usd < 1000 THEN '100$ < volume < 1k$' WHEN volume_usd < 10000 THEN '1k$ < volume < 10k$' WHEN volume_usd < 100000 THEN '10k$ < volume < 100k$' WHEN volume_usd < 1000000 THEN '100k$ < volume < 1m$' ELSE 'more than 1m$' END AS type, sum(volume_usd) as volume, count(DISTINCT origin_from_address) as n_users, count(DISTINCT tx_hash) as n_txns from final group by 1

KaiaSwap