zyroquser growth
Updated 2024-12-18
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 user_activity AS (
SELECT
date_trunc('hour', block_timestamp) AS date,
from_address,
MIN(block_timestamp) OVER (PARTITION BY from_address) AS first_activity_date
FROM
avalanche.core.fact_transactions
WHERE
block_timestamp >= '2024-12-13'
),
classified_users AS (
SELECT
date,
COUNT(DISTINCT from_address) AS total_users,
COUNT(DISTINCT CASE WHEN date = date_trunc('hour', first_activity_date) THEN from_address END) AS new_users,
COUNT(DISTINCT CASE WHEN date > date_trunc('hour', first_activity_date) THEN from_address END) AS returning_users
FROM
user_activity
GROUP BY
date
)
SELECT
cu.date,
cu.total_users,
cu.new_users,
cu.returning_users,
COUNT(DISTINCT t.tx_hash) AS transactions,
AVG(t.tx_fee) AS transaction_fee,
CASE
WHEN cu.date < '2024-12-16 17:00:00.000' THEN 'Before Retro9000 Upgrade'
ELSE 'After Retro9000 Upgrade'
END AS period
FROM
classified_users cu
LEFT JOIN
avalanche.core.fact_transactions t
QueryRunArchived: QueryRun has been archived