MLDZMNmadc3
    Updated 2023-06-02
    with tb2 as (select
    RECORDED_HOUR::date as day,
    avg(close) as price_token
    from solana.core.fact_token_prices_hourly where SYMBOL='SOL'
    group by 1),

    whales as (SELECT
    distinct purchaser as whale,
    sum(SALES_AMOUNT*price_token) as total_purchase

    from solana.core.fact_nft_sales s
    left outer join solana.core.dim_labels b on s.mint=b.address
    join tb2 y on s.BLOCK_TIMESTAMP::date=y.day
    where SUCCEEDED='TRUE'
    group by 1 having total_purchase>50000
    )

    select
    case when PURCHASER in (select whale from whales) then 'Whales' else 'Regular purchasers' end as user_type,
    'Mad Lads' as collection,
    count(distinct TX_ID) as sale_no,
    count(distinct PURCHASER) as buyer_no,
    count (distinct s.MINT) as no_NFTs,
    sum(SALES_AMOUNT) as volume_SOL,
    sum(SALES_AMOUNT*price_token) as volume_usd,
    avg(SALES_AMOUNT) as average_volume_SOL
    from solana.core.fact_nft_sales s
    left outer join solana.core.dim_labels b on s.mint=b.address
    join tb2 y on s.BLOCK_TIMESTAMP::date=y.day
    where SUCCEEDED='TRUE'
    and LABEL = 'mad lads'
    GROUP BY 1,2

    union all
    select
    Run a query to Download Data