Hemin1.2 Daily Sales
    Updated 2022-12-03
    with flow_usd as (
    select
    avg(PRICE_USD) as usd , TIMESTAMP::date as day
    from flow.core.fact_prices
    where day >= '2022-01-01' and
    TOKEN_CONTRACT = 'A.1654653399040a61.FlowToken'
    group by day
    )
    , nft_sales as (
    select a.* ,
    iff( BLOCK_TIMESTAMP::date > '2022-11-23', 'After ThanksGiving', 'Before ThanksGiving') as time,
    iff (CURRENCY = 'A.1654653399040a61.FlowToken', b.usd *PRICE ,PRICE) as usd
    from flow.core.ez_nft_sales a LEFT JOIN flow_usd b on BLOCK_TIMESTAMP::date = b.day
    where BLOCK_TIMESTAMP > '2022-02-01'
    and nft_collection = 'A.e4cf4bdc1751c65d.AllDay'
    and TX_SUCCEEDED = TRUE
    ), new_buyer as (select count(BUYER) as new_user , first_buy from (select min(BLOCK_TIMESTAMP)::date as first_buy ,BUYER from flow.core.ez_nft_sales
    where BLOCK_TIMESTAMP >= '2022-01-01' and nft_collection = 'A.e4cf4bdc1751c65d.AllDay' and TX_SUCCEEDED = TRUE GROUP by BUYER ) GROUP by first_buy )
    , new_seller as (select count(SELLER) as new_seller , first_sell from (select min(BLOCK_TIMESTAMP)::date as first_sell ,SELLER from flow.core.ez_nft_sales
    where BLOCK_TIMESTAMP >= '2022-01-01' and nft_collection = 'A.e4cf4bdc1751c65d.AllDay' and TX_SUCCEEDED = TRUE GROUP by SELLER ) GROUP by first_sell )
    select
    -- date_trunc('week',BLOCK_TIMESTAMP) as week ,
    BLOCK_TIMESTAMP::date as date_time ,
    time,
    count(DISTINCT SELLER) as uniqe_seller ,
    count(DISTINCT BUYER) as uniqe_BUYER ,
    count(TX_ID) as num_tx ,
    count(DISTINCT NFT_ID) as num_nft,
    min(new_user) as num_new_buyer ,
    min(new_seller) as num_new_sellers ,
    sum(usd) as usd_volume,
    avg(usd) as avg_usd_sales
    ,avg(usd_volume) over (order by date_time rows between 7 PRECEDING and current row) as "7-day average Moving Total USD"
    ,avg(avg_usd_sales) over (order by date_time rows between 7 PRECEDING and current row) as "7-day Moving average USD"
    Run a query to Download Data