NavidUntitled Query
Updated 2022-07-25
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
with nft_sales as (
SELECT
dtm.play_type,
dtm.nft_id,
date(fns.BLOCK_TIMESTAMP) as sale_date,
lead(sale_date) over (partition by dtm.nft_id order by sale_date asc) as next_sale_date,
sum(fns.price) as total_value
FROM
flow.core.fact_nft_sales fns join flow.core.dim_topshot_metadata dtm on fns.nft_id=dtm.nft_id
group by
dtm.play_type, dtm.nft_id, date(fns.BLOCK_TIMESTAMP)
order by
dtm.play_type, dtm.nft_id, date(fns.BLOCK_TIMESTAMP)
), holding_days as (
select
play_type,
nft_id,
sale_date,
case when next_sale_date is null then
CURRENT_DATE - sale_date
ELSE
next_sale_date - sale_date
end as hold_duration,
case when next_sale_date is null then
true
ELSE
false
end as is_currently_holded,
total_value
from nft_sales
)
select
play_type,
avg(hold_duration) as avg_hold_duration,
sum(total_value) as sum_total_value
from
Run a query to Download Data