rajs# of Txs and Joined Days Ago
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
36
›
⌄
select
'Exchange Art' as marketplace,
avg(joined_days_ago) as avg_joined_days_ago,
median(joined_days_ago) as median_joined_days_ago,
percentile_cont(0.1) within group (order by joined_days_ago) as "10_pct_joined_days_ago",
percentile_cont(0.3) within group (order by joined_days_ago) as "30_pct_joined_days_ago",
percentile_cont(0.7) within group (order by joined_days_ago) as "70_pct_joined_days_ago",
percentile_cont(0.9) within group (order by joined_days_ago) as "90_pct_joined_days_ago",
avg(no_of_txs) as avg_no_of_txs,
median(no_of_txs) as median_no_of_txs,
percentile_cont(0.1) within group (order by no_of_txs) as "10_pct_no_of_txs",
percentile_cont(0.3) within group (order by no_of_txs) as "30_pct_no_of_txs",
percentile_cont(0.7) within group (order by no_of_txs) as "70_pct_no_of_txs",
percentile_cont(0.9) within group (order by no_of_txs) as "90_pct_no_of_txs"
FROM
(
SELECT
signers[0] as user,
min(block_timestamp) as date_joined,
datediff('day', min(block_timestamp), CURRENT_DATE) as joined_days_ago,
count(*) as no_of_txs
from solana.core.fact_transactions
where signers[0] in
(
select distinct purchaser
from solana.core.fact_nft_sales
where succeeded
and marketplace = 'exchange art'
)
group by 1
order by 3 desc
)
group by 1
union all
Run a query to Download Data