MLDZMNTop Claynosaurz traders by net USD profit
    Updated 2023-03-25
    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),
    buy as (
    select
    purchaser as buyer,
    sum(SALES_AMOUNT*price_token) as volume_usd_buy
    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 SALES_AMOUNT > 0
    and LABEL = 'claynosaurz'
    group by 1 having volume_usd_buy is not null
    ),
    sel as (select
    seller as seller,
    sum(SALES_AMOUNT*price_token) as volume_usd_sell
    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 SALES_AMOUNT > 0
    and LABEL = 'claynosaurz'
    group by 1 having volume_usd_sell is not null
    )
    select
    buyer as "Address of NFT Trader",
    count(distinct tx_id) as "Count of purchase",
    sum(volume_usd_buy) as "Total purchase volume (USD)",
    sum(volume_usd_sell) as "Total sold volume (USD)",
    sum (volume_usd_sell - volume_usd_buy) as "Net USD profit",

    Run a query to Download Data