new user | SocioAnalytica

Updated 2024-02-01

with all_user as (

SELECT

block_timestamp,

tx_hash,

to_address,

from_address,

row_number() over (partition by from_address order by block_timestamp) as rank

FROM polygon.core.fact_transactions

WHERE STATUS = 'SUCCESS'

)

new_users_q1 as (

SELECT

project_name,

count(DISTINCT from_address) as "Q1 2023"

FROM all_user a

LEFT JOIN polygon.core.dim_labels b

on a.to_address = b.address

WHERE block_timestamp BETWEEN '2023-01-01' AND '2023-04-01'

AND project_name is not null

AND label_type not in ('token', 'chadmin')

AND rank = 1

GROUP by 1

)

new_users_q2 as (

SELECT

project_name,

count(DISTINCT from_address) as "Q2 2023"

FROM all_user a

LEFT JOIN polygon.core.dim_labels b

on a.to_address = b.address

WHERE block_timestamp BETWEEN '2023-04-01' and '2023-07-01'

AND project_name is not null

AND label_type not in ('token', 'chadmin')

AND rank = 1

Last run: about 1 year ago

	PROJECT	New.U Q1 2023	New.U Q2 2023	New.U Q3 2023	New.U Q4 2023	Total New Users 2023	% New users Q4 2023
1	hop	429493	727484	804229	861970	2823176	31
2	binance	121890	68666	63727	64953	319236	20
3	okx	21080	81151	78077	72598	252906	29
4	imvu	0	1530	165299	77417	244246	32
5	indigg	0	1	234316	5757	240074	2
6	gamee	121943	76832	8502	1345	208622	1
7	ultimate champions	46412	30983	43930	65622	186947	35
8	socket	1543	82847	39538	20255	144183	14
9	smart cats	0	0	0	127792	127792	100
10	metatrace	0	0	70034	41353	111387	37
11	opensea	8337	36647	22145	41185	108314	38
12	metamask	23923	31068	22642	27998	105631	27
13	qorpo	5	85	8232	92847	101169	92
14	stargate finance	2506	58453	31632	7752	100343	8
15	credit dao	77442	20615	35	8	98100	0
16	uniswap	17939	31267	9706	23424	82336	28
17	sns	60389	21831	1	1	82222	0
18	carv	2	35	134	79838	80009	100
19	0x	10516	29797	17599	16634	74546	22
20	sandbox	16993	16122	26056	11237	70408	16

849B

598s

flipside