Ali3NNew vs Existing Traders Over Time (FLOW NFT Tracker)
    Updated 2024-07-25
    with flowpricet as (
    select hour::date as day,
    avg(price) as flowprice
    from flow.price.ez_prices_hourly
    where symbol ='FLOW'
    group by 1),

    maintable as (
    select block_timestamp,
    tx_id,
    buyer,
    currency,
    price,
    row_number () over (partition by buyer order by block_timestamp asc) as rn
    from flow.nft.ez_nft_sales
    where tx_succeeded ilike 'TRUE'
    and substr(nft_collection,20) ilike '%{{NFT_Collection}}%')

    select date_trunc ('{{Time_Interval}}',block_timestamp) as date,
    case when rn = '1' then 'New Purchasers' else 'Existing Purchasers' end as type,
    count (Distinct tx_id) as Sales,
    count (distinct buyer) as Buyers,
    sum (case when currency in ('A.ead892083b3e2c6c.FlowUtilityToken','A.1654653399040a61.FlowToken') then price*flowprice else price end) as Sales_Volume
    from maintable t1 join flowpricet t2 on t1.block_timestamp::Date = t2.day
    where block_timestamp >= '{{Start_Date}}' and block_timestamp <= '{{End_Date}}'
    group by 1,2
    order by 1 desc



    Auto-refreshes every 12 hours
    QueryRunArchived: QueryRun has been archived