elsinaMagic Eden Top 5 Collection
    Updated 2022-09-25

    with sol_daily_price as (
    select block_timestamp::date as "day",sum(SWAP_from_AMOUNT)/sum(SWAP_TO_AMOUNT) as "price" from solana.fact_swaps
    where SUCCEEDED=TRUE and swap_to_mint= 'So11111111111111111111111111111111111111112' and swap_from_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' and "day" >= '2022-01-01'
    group by "day"
    order by "day"
    ),top_5_nft as (
    select
    count(*) as "number tx",
    sum(sales_amount * "price") as "valume",
    sum(1) over(order by "valume" desc) as "rank" ,
    iff("rank">5,-1,"rank") as "nft orders",
    iff("rank">5,'other',label) as "nft name"
    from solana.core.fact_nft_sales, solana.core.dim_labels , sol_daily_price
    where
    block_timestamp::date >= '2022-01-01' AND
    marketplace in ('magic eden v1','magic eden v2') and
    SUCCEEDED = TRUE and
    block_timestamp::date = "day" and
    mint = address
    group by label
    ), top_5 as (
    select "nft orders" as "rank","nft name" as "project name", sum("valume") as "Volume" ,sum("number tx") as "Buy count"
    from top_5_nft
    group by "nft orders","nft name"
    order by "rank"
    )

    select concat("rank"::string,'. ',"project name") as "Title",* from top_5 where "rank" > 0



    Run a query to Download Data