LOGIN_DATE | TOTAL_USERS | CUMULATIVE_USERS | |
|---|---|---|---|
1 | 2025-04-03 00:00:00.000 | 172641 | 3269133 |
2 | 2025-04-02 00:00:00.000 | 179451 | 3269067 |
3 | 2025-04-01 00:00:00.000 | 186223 | 3268990 |
4 | 2025-03-31 00:00:00.000 | 185445 | 3268369 |
5 | 2025-03-30 00:00:00.000 | 191614 | 3267950 |
6 | 2025-03-29 00:00:00.000 | 208938 | 3267525 |
7 | 2025-03-28 00:00:00.000 | 212929 | 3265975 |
8 | 2025-03-27 00:00:00.000 | 207797 | 3263709 |
9 | 2025-03-26 00:00:00.000 | 207617 | 3263336 |
10 | 2025-03-25 00:00:00.000 | 214017 | 3262909 |
11 | 2025-03-24 00:00:00.000 | 212477 | 3261881 |
12 | 2025-03-23 00:00:00.000 | 219584 | 3261112 |
13 | 2025-03-22 00:00:00.000 | 210719 | 3259491 |
14 | 2025-03-21 00:00:00.000 | 209881 | 3258903 |
15 | 2025-03-20 00:00:00.000 | 218017 | 3256926 |
16 | 2025-03-19 00:00:00.000 | 221516 | 3256325 |
17 | 2025-03-18 00:00:00.000 | 220860 | 3253815 |
18 | 2025-03-17 00:00:00.000 | 217001 | 3249809 |
19 | 2025-03-16 00:00:00.000 | 215828 | 3248491 |
20 | 2025-03-15 00:00:00.000 | 214850 | 3247636 |
permarysubjective-tomato
Updated 2025-04-03
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 first_login AS (
-- Identify the first login date for each user
SELECT
from_address,
MIN(DATE_TRUNC('day', block_timestamp)) AS first_login_date
FROM ronin.core.fact_transactions
WHERE
to_address = '0x02790f32ad7e7eaaecfb0ad21950829932f1a2ee'
AND tx_succeeded = true
GROUP BY from_address
),
daily_logins AS (
-- Get all successful logins per day
SELECT
DATE_TRUNC('day', block_timestamp) AS login_date,
from_address
FROM ronin.core.fact_transactions
WHERE
to_address = '0x02790f32ad7e7eaaecfb0ad21950829932f1a2ee'
AND tx_succeeded = true
),
unique_users_per_day AS (
-- Find the first time each user logs in and track daily new users
SELECT
first_login_date AS login_date,
from_address
FROM first_login
),
cumulative_users AS (
-- Accumulate distinct users over time
SELECT
ulp.login_date,
COUNT(DISTINCT ulp.from_address) AS new_users,
Last run: 2 months ago
...
183
8KB
12s