danhanTime NFT Hold for Solana Users
Updated 2022-07-17
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 buytx as (
select
distinct PURCHASER as br,
BLOCK_TIMESTAMP as buy_time
from solana.core.fact_nft_sales
where tx_id is not NULL
and block_timestamp::DATE >= CURRENT_DATE - 60
),
selltx as (
select
distinct seller as sr,
BLOCK_TIMESTAMP as sell_time
from solana.core.fact_nft_sales
where tx_id is not NULL
and block_timestamp::DATE >= CURRENT_DATE - 60
),
timedifference as (
select
b.br as users,
Avg(abs(DATEDIFF(day, buy_time,sell_time))) as hold_time
from buytx b
join selltx s on b.br=s.sr
group by 1
)
SELECT
case
when hold_time < 1 then '(1)under 1 days'
when hold_time between 1 and 7 then '(2)between 1 day and 1 week'
when hold_time between 7 and 30 then '(3)between 1 week and 1 month'
when hold_time>=30 then '(4)more than month'
end as "hold time",
count(distinct users) as "Distinct Users"
from timedifference
Run a query to Download Data