strawbetty Top 10 most popular assets users have swapped to in 2022
    Updated 2022-03-28
    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
    Run a query to Download Data