MLDZMNnewusers2
    Updated 2023-05-12
    with t1 as (select
    distinct SWAPPER as users,
    CONCAT(q.SYMBOL,' -> ',z.SYMBOL) as asset_pairs,
    min(block_timestamp) as first_appear
    from solana.core.fact_swaps x
    JOIN solana.core.dim_tokens z on x.SWAP_to_MINT=z.TOKEN_ADDRESS
    join solana.core.dim_tokens q on x.SWAP_from_MINT=q.TOKEN_ADDRESS
    where SUCCEEDED='TRUE'
    and PROGRAM_ID ilike 'JUP%'
    group by 1,2
    )
    SELECT
    date_trunc('{{Time_basis}}',first_appear) as date,
    asset_pairs,
    count(distinct users) as new_users
    --sum(new_users) over (order by date) as Total_new_users
    from t1
    where first_appear>=current_date-{{Time_period_days}}
    group by 1,2 having asset_pairs is not null
    QUALIFY row_number() OVER (partition by date ORDER BY new_users DESC) <= 5
    Run a query to Download Data