with base as (
SELECT
COUNT(DISTINCT tx_group_id) as total_swaps,
COUNT(DISTINCT swapper) as unique_wallets,
swap_to_asset_id as swap_to,
swap_program as app
from algorand.swaps
WHERE
(block_timestamp>='2022-01-01' AND block_timestamp<='2022-03-20')
AND swap_to_asset_id!=0
AND swap_from_amount>0
AND swap_to_amount>0
GROUP BY swap_to, app
),
refine as (
SELECT
asset_name,
total_swaps,
unique_wallets,
app,
sum(total_swaps) OVER (Partition by asset_name) as swaps_overall
from base b LEFT JOIN algorand.asset a ON b.swap_to = a.asset_id
ORDER BY swaps_overall DESC
)
SELECT
*,
dense_rank() OVER ( ORDER BY swaps_overall DESC) as rno
from refine
qualify rno<=10