MLDZMNhwt4
    Updated 2022-10-23
    with tb1 as (select
    LABEL as NFT,
    count(TX_ID) as sale_no,
    count(distinct PURCHASER) as buyer_no,
    sum(SALES_AMOUNT) as volume,
    avg(SALES_AMOUNT) as average_volume,
    sale_no/buyer_no as average_buyer,
    buyer_no/count(distinct date_trunc(day, block_timestamp)) as average_buyer_day,
    volume/count(distinct date_trunc(day, block_timestamp)) as average_volume_day
    from solana.core.fact_nft_sales s left outer join solana.core.dim_labels b on s.mint=b.address
    where marketplace = 'hadeswap' and SUCCEEDED='TRUE'
    group by 1 having NFT is not null
    order by 3 desc limit 10
    )

    select
    block_timestamp::date as day,
    LABEL as NFT,
    count(TX_ID) as sale_no,
    count(distinct PURCHASER) as buyer_no,
    sum(SALES_AMOUNT) as volume,
    avg(SALES_AMOUNT) as average_volume,
    avg(average_volume) OVER (partition by NFT ORDER BY day ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as MA_7_Days,
    sum(buyer_no) over (partition by NFT order by day) as cum_buyers,
    sum(sale_no) over (partition by NFT order by day) as cum_sale,
    sum(volume) over (partition by NFT order by day) as cum_volume
    from solana.core.fact_nft_sales s left outer join solana.core.dim_labels b on s.mint=b.address
    where marketplace = 'hadeswap' and SUCCEEDED='TRUE'
    and NFT in (select NFT from tb1)
    group by 1 , 2


    Run a query to Download Data