HosseinUntitled Query
Updated 2023-01-08
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
›
⌄
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