adambalaUntitled Query
    Updated 2022-12-05
    -- credit by elsina
    with flow_price as (
    select
    date_trunc('day', timestamp) as day,
    avg(price_usd) as flow_price
    from flow.core.fact_prices
    where token_contract = 'A.1654653399040a61.FlowToken'
    group by 1
    ),
    flow_usd as (
    select
    case
    when currency in ('A.ead892083b3e2c6c.FlowUtilityToken', 'A.1654653399040a61.FlowToken') then price * flow_price
    when currency in ('A.3c5959b568896393.FUSD', 'A.ead892083b3e2c6c.DapperUtilityCoin', 'A.b19436aae4d94622.FiatToken') then price
    else null
    end as volume_usd, *
    from flow.core.ez_nft_sales join flow_price f on block_timestamp::date = f.day
    where tx_succeeded = true and volume_usd is not null and NFT_COLLECTION ='A.e4cf4bdc1751c65d.AllDay'
    )

    select
    date_trunc('day', block_timestamp) as date,
    -- count(distinct seller) as unique_seller,
    case
    when date BETWEEN '2022-11-16' and '2022-11-30' then 'Thanksgiving 2022'
    else 'Normal Day' end as "TimeFrame",

    sum(volume_usd) as sales_volume,
    count(distinct tx_id) as tx_count,
    count(distinct buyer) as unique_buyer,
    avg(volume_usd) as avg_sales_price,
    sum(sales_volume) over (order by date) as cum_sales_volume,
    sum(tx_count) over (order by date) as cum_tx_count,