DiamondNBATS Redemption Denver-Miami copy copy
    Updated 2024-03-08
    with news as (
    SELECT
    distinct buyer as news,
    min(trunc(block_timestamp :: date, 'hour')) as debut
    from
    flow.nft.ez_nft_sales s
    left join flow.nft.dim_topshot_metadata m on s.nft_id = m.nft_id
    and s.nft_collection = m.nft_collection
    where
    tx_succeeded = true
    group by
    1
    )
    select
    trunc(block_timestamp :: date, 'hour') as "Date",
    team as "Team",
    CASE
    WHEN player = 'N/A' THEN "Team"
    ELSE player
    END AS "Name",
    count(distinct tx_id) as "Sales Count",
    sum(price) as "Sales Volume",
    avg(price) as avg_NFT_price,
    count(distinct buyer) as unique_buyers,
    count(distinct news) as new_buyers
    from
    flow.nft.ez_nft_sales s
    left join flow.nft.dim_topshot_metadata m on s.nft_id = m.nft_id
    and s.nft_collection = m.nft_collection
    left join news on s.block_timestamp = news.debut
    where
    tx_succeeded = true
    and set_name in ('Crunch Time')
    AND block_timestamp > '2023-01-01 00:00:00'
    AND block_timestamp < '2024-07-10 19:00:00'
    group by
    QueryRunArchived: QueryRun has been archived