with tb AS (

SWAPPER AS user,

count(*) AS TX,

sum(case when SWAP_TO_AMOUNT*B.Close is not null then SWAP_TO_AMOUNT*B.Close

else SWAP_From_AMOUNT*C.Close end ) AS Volume,

count(DISTINCT date_trunc('Month',BLOCK_TIMESTAMP)) AS Months,

count(DISTINCT date_trunc('Week',BLOCK_TIMESTAMP)) AS Weeks,

sum(case when Swap_program like 'jupiter aggregator v2' then 1 else 0 end) AS "Before V3 upgrade TX",

rank() over (order by Volume desc) AS rank,

sum(case when Block_timestamp::date >= '2023-01-01' then 1 else 0 end) AS "2023 TXs",

sum(Case when Block_timestamp::date between '2021-01-01' and '2021-12-30' then 1 else 0 end) AS "2021 TXs"

solana.defi.fact_swaps

left join solana.price.ez_token_prices_hourly B on date_trunc('Hour',BLOCK_TIMESTAMP)=B.RECORDED_HOUR

and SWAP_TO_MINT=B.TOKEN_ADDRESS

left join solana.price.ez_token_prices_hourly C on date_trunc('Hour',BLOCK_TIMESTAMP)=C.RECORDED_HOUR

and SWAP_FROM_MINT=C.TOKEN_ADDRESS

Swap_program like 'jupiter%'

group by 1)

select

Volume,

TX,

rank,

case

when rank <= 2000 then 'Top 2K User'

when rank <= 10000 and rank > 2000 then 'Top 10K User'

when rank <= 50000 and rank > 10000 then 'Top 50K User'

when rank <= 150000 and rank > 50000 then 'Top 150K User'

when rank <= 500000 and rank > 150000 then 'Top 500K User'

when rank <= 1000000 and rank > 500000 then 'Top 1M User'

end AS Tire,