MLDZMNXAR11
Updated 2023-01-27
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
›
⌄
with tb1 as (select
distinct purchaser as u1,
min(BLOCK_TIMESTAMP) as x1
from solana.core.fact_nft_sales
where SUCCEEDED='TRUE'
and MARKETPLACE ='exchange art'
and SALES_AMOUNT>0
group by 1 ),
tb2 as (SELECT
distinct SELLER as u2,
min(BLOCK_TIMESTAMP) as x2
from solana.core.fact_nft_sales
where SUCCEEDED='TRUE'
and MARKETPLACE ='exchange art'
and SALES_AMOUNT>0
and SELLER in (select u1 from tb1)
group by 1) ,
tb3 as (select
tb1.u1 as purchasers,
avg(DATEDIFF(day,x1, x2 )) as time_between
from tb1
join tb2 on tb1.u1=tb2.u2
group by 1)
select
case when time_between<1 then 'whitin 24 Hours'
when time_between>=1 and time_between<7 then 'Under 1 week'
when time_between>=7 and time_between<30 then 'Between 1 week to 1 month'
when time_between>=30 then 'Over 1 month'
end as buckets,
count(distinct purchasers) as count_users
from tb3
group by 1
Run a query to Download Data