Flipside TeamMAUs by # of Projects (Excluding Token & CEX)
Updated 2024-11-14
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
›
⌄
-- forked from MAUs by # of Projects (Excluding Token & CEX) @ https://flipsidecrypto.xyz/edit/queries/62be059a-82b6-4638-8941-b85bda54a8f9
with base as (
select
block_timestamp,
tx_signer as user_address,
project_name
from near.core.fact_transactions
inner join (select address, project_name from near.core.dim_address_labels
where label_type not in ('token','cex')
and label_subtype not in ('token_contract', 'nf_token_contract')
and project_name not in ('kaikai', 'sweat'))
on address = tx_receiver
where tx_succeeded=true
and tx_signer not in (select distinct address from near.core.dim_address_labels)
and block_timestamp::date >= '{{starting_date}}'
-- qualify(row_number() over (partition by user_address, project_name order by block_timestamp)) = 1
),
final as (
select
a.*,
row_number() over (partition by a.user_address order by block_timestamp) as rank
from base a )
select date,
case when projects=1 then '1 Project'
when projects=2 then '2 Project'
when projects>2 and projects<6 then '3 to 5 Project'
else '5+ Projects'
end as "Type",
count(distinct user_address) as "# of Users"
from (
select