with swaps as ( select block_timestamp::date as date, tx_hash, origin_from_address as swapper, amount_in_usd, amount_out_usd, amount_in, amount_out, token_in, token_out, symbol_in, symbol_out from kaia.defi.ez_dex_swaps where platform = 'dragonswap-v3') , final as ( select date, tx_hash, swapper, case when token_in in ('0x5c13e303a62fc5dedf5b52d66873f2e59fedadc2','0x608792deb376cce1c9fa4d0e6b7b44f507cffa6a','0x4e4a231e864219704ccc1acd81043129157c1901','0xe2053bcf56d2030d2470fb454574237cf9ee3d4b','0xcee8faf64bb97a73bb51e115aa89c17ffa8dd167','0x754288077d0ff82af7a5317c7cb8c444d421d103','0x7d8142a696ddbb8eaaa937696c35772190b234cb','0xcb2c7998696ef7a582dfd0aafadcd008d03e791a') then amount_in else amount_in_usd end as amount_1, case when token_out in ('0x5c13e303a62fc5dedf5b52d66873f2e59fedadc2','0x608792deb376cce1c9fa4d0e6b7b44f507cffa6a','0x4e4a231e864219704ccc1acd81043129157c1901','0xe2053bcf56d2030d2470fb454574237cf9ee3d4b','0xcee8faf64bb97a73bb51e115aa89c17ffa8dd167','0x754288077d0ff82af7a5317c7cb8c444d421d103','0x7d8142a696ddbb8eaaa937696c35772190b234cb','0xcb2c7998696ef7a582dfd0aafadcd008d03e791a') then amount_out else amount_out_usd end as amount_2, case when token_in = '0xaaecb956075daf626bc5d507bb38764e122cf209' then 'ACE' when token_in = '0xd3f78c32af988d67c16f02c3631bf4047ffda617' then 'oETH' when token_in = '0x57f6f9d14bbd43d06188a87882e240d42ddd1393' then 'PrayOzys' when token_in = '0xe3f85b41a284ed965826adc98365e70e67a2f556' then 'KKAK' when token_in = '0x27397bfbefd58a437f2636f80a8e70cfc363d4ff' then 'GPC' when token_in = '0xf63e2719321fb1db23071d7f5600ef77f4027929' then '세력' when token_in = '0x4b4c3006d177bfe7cdda7c2db0fac7cbf02b4f2e' then 'FUBAO' when token_in = '0x023092e1b5ffeaa4f68585edd01ba6bbff244bb1' then 'HATE' when token_in = '0xb6b6fd8e645084ec45ebdcb452e8dab7c1e6f4c8' then 'ISR' else symbol_in end as Symbol_1, case when token_out = '0xaaecb956075daf626bc5d507bb38764e122cf209' then 'ACE' when token_out = '0xd3f78c32af988d67c16f02c3631bf4047ffda617' then 'oETH' when token_out = '0x57f6f9d14bbd43d06188a87882e240d42ddd1393' then 'PrayOzys' when token_out = '0xe3f85b41a284ed965826adc98365e70e67a2f556' then 'KKAK' when token_out = '0x27397bfbefd58a437f2636f80a8e70cfc363d4ff' then 'GPC' when token_out = '0xf63e2719321fb1db23071d7f5600ef77f4027929' then '세력' when token_out = '0x4b4c3006d177bfe7cdda7c2db0fac7cbf02b4f2e' then 'FUBAO' when token_out = '0x023092e1b5ffeaa4f68585edd01ba6bbff244bb1' then 'HATE' when token_out = '0xb6b6fd8e645084ec45ebdcb452e8dab7c1e6f4c8' then 'ISR' else symbol_out end as symbol_2, token_in, token_out, case when amount_1 is null then amount_2 else amount_1 end as volume from swaps) , user as ( select swapper, sum(volume) as volume_usd from final group by 1) select case when volume_usd <= 10 then 'a. <= 10 USD' when volume_usd <= 100 then 'b. 10-100 USD' when volume_usd <= 500 then 'c. 100-500 USD' when volume_usd <= 1000 then 'd. 500-1k USD' when volume_usd <= 5000 then 'e. 1k-5k USD' when volume_usd <= 10000 then 'f. 5k-10k USD' when volume_usd <= 50000 then 'g. 10k-50k USD' when volume_usd <= 100000 then 'h. 50k-100K USD' when volume_usd <= 500000 then 'i. 100k-500k USD' when volume_usd <= 1000000 then 'j. 500k-1m USD' when volume_usd > 1000000 then 'k. > 1m USD' end as category, count(DISTINCT swapper) as swappers, sum(volume_USd) as volume from user where volume_usd is not null group by 1 order by 1 asc

DragonSwap on Kaia!