Flipside TeamUsers interacting with 2+ apps over time
Updated 2024-11-24
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 Users interacting with 2+ apps over time copy @ https://flipsidecrypto.xyz/edit/queries/c01a654b-cc33-471d-a61d-35834447cc42
-- forked from graham / Users interacting with 2+ apps over time @ https://flipsidecrypto.xyz/graham/q/4IjvXsclz9Lw/users-interacting-with-2-apps-over-time
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'))
on address = tx_receiver
where tx_succeeded=true
and tx_signer not in (select distinct address from near.core.dim_address_labels)
and year(block_timestamp) >= 2023
qualify(row_number() over (partition by user_address, project_name order by block_timestamp)) = 1
),
final as (
select
*
from base
qualify(row_number() over (partition by user_address order by block_timestamp)) = 2
)
select
block_timestamp:: date as block_date,
count(distinct user_address) as "# of Users",
sum("# of Users") over (order by block_date) as "Total Users"
from
final
group by block_date
QueryRunArchived: QueryRun has been archived