Flipside Community2b) popular apps for first time users
Updated 2024-02-23
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 2b) popular apps for first time users @ https://flipsidecrypto.xyz/edit/queries/4a6093f9-8096-4ef8-8818-87df6a6daa13
-- forked from 2b @ https://flipsidecrypto.xyz/edit/queries/956394e3-fe4f-4801-b8b3-68e508994b63
with addresses as
(
select from_address, min(block_timestamp) as first_transaction_sent
from {{Chain}}.core.fact_transactions
group by from_address
having first_transaction_sent >= '{{Start_date}}'
),
first_transaction as
(
select * from {{Chain}}.core.fact_transactions
where 1=1
and block_timestamp >= '{{Start_date}}'
and from_address in ( select from_address from addresses)
qualify(row_number() over (partition by from_address order by block_timestamp asc)=1)
),
top_apps as (
select to_address, count(distinct from_address) as new_user
from first_transaction ft
group by to_address
order by new_user desc
limit 100
),
top_100 as
(
select
dl.project_name,
sum(ta.new_user) as number_of_users,
-- dl.address_name,
-- dl.label_type,
-- dl.label_subtype,
-- dl.project_name,
row_number() over (order by number_of_users desc) as rn
QueryRunArchived: QueryRun has been archived