ArioNFL All Day Tournament [Round 1] - sales volume and etc.
    Updated 2024-05-10
    with NFL_data as (
    select
    BLOCK_TIMESTAMP,
    TX_ID,
    MARKETPLACE,
    NFT_COLLECTION a,
    NFT_ID,
    BUYER,
    SELLER,
    PRICE,
    TX_SUCCEEDED
    from flow.nft.ez_nft_sales
    where NFT_COLLECTION ilike '%allday%'
    and PRICE is not null
    and TX_SUCCEEDED = 'TRUE'
    )
    select date_trunc(month, BLOCK_TIMESTAMP)::date as date,
    sum(PRICE) as sales_volume,
    sum(sales_volume) over(order by date) as Cum_Sales_Volume,
    count(distinct TX_ID) as N_Tx,
    sum(N_Tx) over(order by date) as Cum_N_Tx,
    count(distinct buyer) as N_Unique_Buyer,
    sum(N_Unique_Buyer) over(order by date) as Cum_N_Buyer,
    count(distinct seller) as N_Unique_Seller,
    sum(N_Unique_Seller) over(order by date) as Cum_N_Seller,
    sum(PRICE) / COUNT(distinct buyer) as Power_of_Buyer,
    sum(PRICE) / COUNT(distinct seller) as Power_of_Seller,
    Power_of_Buyer / Power_of_Seller as ratio_buyer_seller,
    Avg(Price) as Aerage_price,
    min(Price) as Minimum_Price,
    max(Price) as Maximum_Price,
    median(Price) as Median_Price
    from NFL_data
    group by DATE
    ORDER by date desc

    QueryRunArchived: QueryRun has been archived