new user | SocioAnalytica

Updated 2024-02-07

with all_user as (

SELECT

block_timestamp,

tx_hash,

tx_receiver,

tx_signer,

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

FROM near.core.fact_transactions

WHERE TX_SUCCEEDED

)

active_users_q1 as (

SELECT

project_name,

label_type as sector,

count(DISTINCT tx_signer) as "Q1 2023"

FROM all_user a

LEFT JOIN near.core.dim_address_labels b

on a.tx_RECEIVER = b.address

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

AND project_name is not null

AND rank = 1

GROUP by 1 , 2

)

active_users_q2 as (

SELECT

project_name,

label_type as sector,

count(DISTINCT tx_signer) as "Q2 2023"

FROM all_user a

LEFT JOIN near.core.dim_address_labels b

on a.tx_RECEIVER = b.address

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

AND project_name is not null

AND rank = 1

Last run: about 1 year ago

	PROJECT	SECTORS	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	kaikai	dapp	0	0	1723162	11798428	13521590	87
2	playember	games	77681	74765	649039	1653511	2454996	67
3	sweat	games	561852	396701	340577	698729	1997859	35
4	okex	cex	3275	3024	2044	3635	11978	30
5	orderly network	defi	1	33	6975	3823	10832	35
6	coinbase	cex	1627	1028	1151	2833	6639	43
7	lis	defi	4332	317	187	85	4921	2
8	astro stakers	defi	433	386	766	1098	2683	41
9	gate.io	cex	476	434	324	1371	2605	53
10	huobi	cex	582	631	281	373	1867	20
11	swissborg	cex	251	132	159	340	882	39
12	aurora	bridge	500	166	3	14	683	2
13	neat	defi	0	0	0	682	682	100
14	ref	defi	257	165	71	137	630	22
15	oct	defi	148	99	98	244	589	41
16	mexc	cex	90	82	207	203	582	35
17	everstake	defi	120	84	241	124	569	22
18	epic pool	defi	77	79	222	156	534	29
19	tao	defi	286	40	120	18	464	4
20	near social	dapp	29	2	25	308	364	85

869B

292s

flipside