0xHaM-dCustom Daily Number of New Users in Q3 2024
Updated 2024-10-01
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
›
⌄
with
users as (
select
origin_from_address as user,
min(block_timestamp) as first_date
from
crosschain.olas.fact_service_events
where
blockchain ilike ('{{Custom_Chain}}')
group by
user
)
select
date_trunc('d', first_date) as "Date",
case
when year(first_date) = '2023' then concat('Q', quarter("Date"),'-','Months ','[',month("Date") ,' To ',month("Date") + 1,']','-', 23 )
when year(first_date) = '2024' then concat('Q', quarter("Date"),'-','Months ','[',month("Date") ,' To ',month("Date") + 1,']','-', 24 )
end as "Quarter",
count(distinct user) as "New Users",
sum("New Users") over (order by "Date") as "Total Users"
from
users
where
first_date::date >= '2024-07-01'
AND
first_date::date < '2024-10-01'
group by 1,2
order by
1 desc
QueryRunArchived: QueryRun has been archived