SELECT

block_timestamp

, tx_hash

, origin_from_address

, CASE WHEN token_in = lower('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E') THEN 'buy'

WHEN token_out = lower('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E') THEN 'sell'

END AS Action

, CASE WHEN token_in = lower('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E') THEN symbol_in

WHEN token_out = lower('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E') THEN symbol_out

END AS symbol

, CASE WHEN amount_in_usd IS NULL THEN amount_out_usd ELSE amount_in_usd END AS usd_amount

, CASE WHEN token_in = lower('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E') THEN amount_in

WHEN token_out = lower('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E') THEN amount_out

END AS token_amount

, platform

FROM avalanche.defi.ez_dex_swaps

WHERE token_in = lower('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E')

OR token_out = lower('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E')

SELECT

CASE WHEN usd_amount > 100000 THEN 'Rank 1: Over $100k'

WHEN usd_amount > 50000 AND usd_amount <= 100000 THEN 'Rank 2: Over $50k - $100k'

WHEN usd_amount > 10000 AND usd_amount <= 50000 THEN 'Rank 3: Over $10k - $50k'

WHEN usd_amount > 1000 AND usd_amount <= 10000 THEN 'Rank 4: Over $1k - $10k'

WHEN usd_amount > 500 AND usd_amount <= 1000 THEN 'Rank 5: Over $500 - $1k'

WHEN usd_amount > 100 AND usd_amount <= 500 THEN 'Rank 6: Over $100 - $500'

WHEN usd_amount > 10 AND usd_amount <= 100 THEN 'Rank 7: Over $10 - $100'

WHEN usd_amount <= 10 THEN 'Rank 8: Less Than $10'

ELSE 'amount'

END AS "Trader Rank"

, COUNT(DISTINCT origin_from_address) AS "Traders"

, SUM(usd_amount) AS "Total Volume"

, COUNT(tx_hash) AS "Transactions"