DataDriven_Web3Percentage of daily creators are new
Updated 2025-03-31
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 daily_creators AS (
SELECT
block_timestamp::date AS date,
signers[0] AS creator
FROM
solana.core.fact_decoded_instructions
WHERE
program_id = '6EF8rrecthR5Dkzon8Nwu78hRvfCKubJ14M5uBEwF6P'
AND event_type = 'create'
AND block_timestamp >= CURRENT_DATE - INTERVAL '30 days'
),
new_daily_creators AS (
SELECT
signers[0] AS creator,
MIN(block_timestamp::date) AS first_create_date
FROM
solana.core.fact_decoded_instructions
WHERE
program_id = '6EF8rrecthR5Dkzon8Nwu78hRvfCKubJ14M5uBEwF6P'
AND event_type = 'create'
GROUP BY
creator
)
SELECT
dc.date,
COUNT(DISTINCT CASE WHEN dc.date = ndc.first_create_date THEN dc.creator END) AS daily_new_creators,
COUNT(DISTINCT dc.creator) AS total_daily_creators,
(COUNT(DISTINCT CASE WHEN dc.date = ndc.first_create_date THEN dc.creator END) * 100.0 /
COUNT(DISTINCT dc.creator)) AS percentage_new_creators
FROM
daily_creators dc
LEFT JOIN
new_daily_creators ndc ON dc.creator = ndc.creator
GROUP BY
QueryRunArchived: QueryRun has been archived