bergTop Tokens per day in terms of swap numbers at 2 weeks ago
    Updated 2022-12-08
    with prices as (
    select block_timestamp::date as day,
    swap_from_mint as mint,
    median (swap_to_amount/swap_from_amount) as price_usd
    from solana.fact_swaps
    where swap_to_mint in ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDav','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB')
    and swap_to_amount > 0
    and swap_from_amount > 0
    and succeeded = 1
    group by 1, 2
    )

    select
    a.block_timestamp::date "Date",
    b.symbol "Token",
    count(distinct a.tx_id) "Swaps Count",
    count(distinct swapper) "Swappers Count",
    sum(price_usd * swap_from_amount) "Volume (USD)",
    avg(price_usd * swap_from_amount) "Average Volume (USD)",
    median(price_usd * swap_from_amount) "Median Amount (USD)",
    sum("Swaps Count") over (partition by "Token" order by "Date" asc) as "Comulative Swaps Count",
    sum("Swappers Count") over (partition by "Token" order by "Date" asc) as "Comulative Swappers Count",
    sum("Volume (USD)") over (partition by "Token" order by "Date" asc) as "Comulative Volume (USD)",
    row_number() over (partition by "Date" order by "Swappers Count" desc) as rank
    from solana.core.fact_swaps a
    join prices c on a.block_timestamp::Date = c.day and a.swap_from_mint = c.mint
    join crosschain.core.dim_asset_metadata b
    on a.swap_from_mint = b.token_address
    where a.block_timestamp >= current_date - interval '2 weeks'
    and swap_program = 'raydium v4'
    and platform = 'solana'
    and a.succeeded = 1
    group by 1, 2
    qualify rank <= 10
    order by 1, rank
    Run a query to Download Data