HosseinUntitled Query
    Updated 2023-01-08
    select
    block_timestamp::date as day,
    iff(year(day) = 2022, 'Before Chrismas', 'After Chrismas') as timespan,
    count(distinct tx_id) as nft_txns_count,
    count(distinct purchaser) as purchasers_count,
    count(distinct seller) as sellers_count,
    sum(sales_amount / pow(10, decimals)) as total_amount,
    avg(sales_amount / pow(10, decimals)) as average_amount,
    median(sales_amount / pow(10, decimals)) as median_amount,
    max(sales_amount / pow(10, decimals)) as max_amount,
    min(sales_amount / pow(10, decimals)) as min_amount,
    count(distinct (token_id)) as nfts_count,
    sum (nft_txns_count) over (partition by timespan order by day asc) as cumulative_nft_txns_count,
    sum (purchasers_count) over (partition by timespan order by day asc) as cumulative_purchasers_count,
    sum (sellers_count) over (partition by timespan order by day asc) as cumulative_sellers_count,
    sum (total_amount) over (partition by timespan order by day asc) as cumulative_total_amount
    from terra.core.fact_nft_sales
    where day between date('2023-01-01') - interval '2 weeks' and date('2023-01-01') + interval '2 weeks'
    group by timespan, day
    order by day