swaps_to as (

Select

trunc(block_timestamp,'day') as date,

swap_to_mint,

case when swap_to_mint ='RLYv2ubRMDLcGG2UyvPmnPmkfuQTsMbg4Jtygc7dmnq' then 'Rally'

else address_name end as address_name,

count(distinct tx_id) as swapcount

From Solana.swaps s

LEFT OUTER JOIN Solana.labels b ON s.swap_to_mint =b.address

Where block_timestamp::date >= '2022-02-01' And succeeded = 'True'

and swap_program like '%jupiter%'

Group by 1,2,3

order by 3 desc

),

ranks as

(SELECT swaps_to.*, RANK() OVER(PARTITION BY date ORDER BY swapcount DESC) as rank FROM swaps_to

SELECT *

FROM ranks

WHERE rank <= 10 -- top 10

ORDER BY date DESC, swapcount DESC