Active 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

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

GROUP by 1 , 2

)

Last run: about 1 year ago

	PROJECT	SECTORS	DAU Q1 2023	DAU Q2 2023	DAU Q3 2023	DAU Q4 2023	Total Active Users 2023	% DAU Q4
1	kaikai	dapp	0	0	1723169	13256680	14979849	88
2	sweat	games	1486061	1395403	1431022	1862022	6174508	30
3	playember	games	78095	75273	649178	1731270	2533816	68
4	orderly network	defi	2829	3681	35836	33488	75834	44
5	near social	dapp	6080	6692	5479	29732	47983	62
6	okex	cex	6037	5475	4255	8512	24279	35
7	ref finance	defi	5600	2540	1731	9156	19027	48
8	learnnear	dapp	3943	4953	5086	4509	18491	24
9	paras	nft	5324	5546	1725	2594	15189	17
10	few and far	nft	2524	12020	194	165	14903	1
11	paras	nft	5324	5546	305	2594	13769	19
12	paras	defi	5324	5546	1725	748	13343	6
13	astro stakers	defi	2726	2317	3244	4571	12858	36
14	neat	defi	0	0	0	12819	12819	100
15	ref	defi	3727	2370	1789	4161	12047	35
16	paras	defi	5324	5546	305	748	11923	6
17	paras	nft	879	5546	1725	2594	10744	24
18	coinbase	cex	2301	1603	1881	4553	10338	44
19	paras	nft	5324	534	1725	2594	10177	25
20	paras	nft	879	5546	305	2594	9324	28

969B

364s

flipside