SocioAnalytica(q1-2023 vs q1-2024 )
Updated 2024-03-29
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 all_user as (
SELECT
block_timestamp,
tx_hash,
tx_receiver,
tx_signer,
row_number() over (partition by tx_signer order by block_timestamp) as rank
FROM near.core.fact_transactions
WHERE TX_SUCCEEDED
)
,
DAU_u as (
SELECT
case when block_timestamp::date between '2024-01-01' and '2024-03-30' then 'Q1-2024'
when block_timestamp::date between '2023-01-01' and '2023-03-30' then 'Q1-2023'
end as time,
count(DISTINCT tx_signer) as DAU
FROM all_user
where block_timestamp::date >= '2023-01-01'
group by 1
)
,
new as (
SELECT
case when block_timestamp::date between '2024-01-01' and '2024-03-30' then 'Q1-2024'
when block_timestamp::date between '2023-01-01' and '2023-03-30' then 'Q1-2023'
end as time,
count(DISTINCT tx_signer) as new_user
FROM all_user
where block_timestamp::date >= '2023-01-01'
and rank = 1
group by 1
)
SELECT
a.time,
QueryRunArchived: QueryRun has been archived