theericstoneUsers interacting with 2+ apps over time copy
Updated 2025-01-08
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 MoDeFi / Users interacting with 2+ apps over time @ https://flipsidecrypto.xyz/MoDeFi/q/VOkTGy2xEHoT/users-interacting-with-2-apps-over-time
-- forked from Users interacting with 2+ apps over time copy @ https://flipsidecrypto.xyz/edit/queries/b4523dce-1ada-46a1-a203-149f23a7649a
-- 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 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,
count(distinct user_address) as "# of Users"
from (
select
date_trunc(month, block_timestamp) as date, user_address,
count(distinct project_name) as projects
from final
group by date, user_address)
QueryRunArchived: QueryRun has been archived