0xHaM-ddaily
    Updated 2023-02-24
    with priceTb as (
    select
    TIMESTAMP::date as p_date,
    avg (price_usd) as flow_price
    from flow.core.fact_prices
    where symbol ='FLOW' and source ='coinmarketcap'
    group by 1
    )
    , nft_price as (
    SELECT*,
    price * b.flow_price as nft_usd_price
    from flow.core.ez_nft_sales a join priceTb b on a.block_timestamp::date = b.p_date
    where nft_collection = 'A.329feb3ab062d289.RaceDay_NFT'
    and tx_succeeded = 'TRUE'
    )
    select
    BLOCK_TIMESTAMP::DATE as DATE,
    count(distinct tx_id) as Sales_Cnt,
    count(distinct buyer) as Buyers_Cnt,
    count(distinct seller) as Sellers_Cnt,
    count(distinct nft_collection) as Collections_Cnt,
    count(distinct marketplace) as Marketplaces_Cnt,
    count(distinct nft_id) as Tokens_Cnt,
    sum(price) as Total_flow_Vol,
    sum(nft_usd_price) as Total_USD_Vol,
    avg(nft_usd_price) as Avg_USD_Amt,
    avg(price) as Avg_flow_Vol,
    avg(nft_usd_price) as Avg_USD_Vol,
    sum(Sales_Cnt) over (order by date) as cum_Sales_Cnt,
    sum(Total_flow_Vol) over (order by date) as cum_Total_flow_Vol,
    sum(Total_USD_Vol) over (order by date) as cum_Total_USD_Vol,
    avg(Sales_Cnt) over (order by date) as Avg_Sales_Cnt,
    avg(Buyers_Cnt) over (order by date) as Avg_Buyers_Cnt,
    avg(Avg_USD_Amt) over (order by date rows between 7 PRECEDING and current row) as MA_7,
    avg(Avg_USD_Amt) over (order by date rows between 50 PRECEDING and current row) as MA_50
    from nft_price
    Run a query to Download Data