TX_DATE | TOTAL_TRANSACTIONS | TOTAL_USERS | NEW_GAMERS | RETURNING_GAMERS | |
|---|---|---|---|---|---|
1 | 2025-01-01 00:00:00.000 | 848472 | 711338 | 5575 | 705763 |
2 | 2025-01-02 00:00:00.000 | 701539 | 584296 | 10782 | 573514 |
3 | 2025-01-03 00:00:00.000 | 723249 | 639819 | 10896 | 628923 |
4 | 2025-01-04 00:00:00.000 | 747501 | 659591 | 12863 | 646728 |
5 | 2025-01-05 00:00:00.000 | 725368 | 646180 | 13001 | 633179 |
6 | 2025-01-06 00:00:00.000 | 749644 | 656358 | 14576 | 641782 |
7 | 2025-01-07 00:00:00.000 | 787141 | 700155 | 37003 | 663152 |
8 | 2025-01-08 00:00:00.000 | 756376 | 573341 | 39382 | 533959 |
9 | 2025-01-09 00:00:00.000 | 802647 | 701568 | 39846 | 661722 |
10 | 2025-01-10 00:00:00.000 | 765468 | 679550 | 42734 | 636816 |
11 | 2025-01-11 00:00:00.000 | 902878 | 698705 | 53788 | 644917 |
12 | 2025-01-12 00:00:00.000 | 872699 | 670998 | 57026 | 613972 |
13 | 2025-01-13 00:00:00.000 | 892309 | 683024 | 61582 | 621442 |
14 | 2025-01-14 00:00:00.000 | 745083 | 632309 | 57555 | 574754 |
15 | 2025-01-15 00:00:00.000 | 794085 | 636599 | 65687 | 570912 |
16 | 2025-01-16 00:00:00.000 | 768341 | 648870 | 76135 | 572735 |
17 | 2025-01-17 00:00:00.000 | 769262 | 674578 | 74897 | 599681 |
18 | 2025-01-18 00:00:00.000 | 833746 | 644175 | 79395 | 564780 |
19 | 2025-01-19 00:00:00.000 | 719785 | 645151 | 78217 | 566934 |
20 | 2025-01-20 00:00:00.000 | 764743 | 658199 | 80266 | 577933 |
permaryNew Gamers Vs Returning Gamers
Updated 2025-04-02
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 q1_users AS (
SELECT DISTINCT t.from_address, DATE(t.block_timestamp) AS tx_date
FROM ronin.core.fact_transactions t
JOIN ronin.core.dim_labels dl
ON t.to_address = dl.address
WHERE t.block_timestamp >= '2025-01-01'
AND t.block_timestamp < '2025-04-01'
AND dl.label_type = 'games'
),
previous_users AS (
SELECT DISTINCT t.from_address
FROM ronin.core.fact_transactions t
JOIN ronin.core.dim_labels dl
ON t.to_address = dl.address
WHERE t.block_timestamp < '2025-01-01'
AND dl.label_type = 'games'
)
SELECT
DATE(t.block_timestamp) AS tx_date,
COUNT(*) AS total_transactions,
COUNT(DISTINCT t.from_address) AS total_users,
COUNT(DISTINCT CASE WHEN p.from_address IS NULL THEN t.from_address END) AS new_gamers,
COUNT(DISTINCT CASE WHEN p.from_address IS NOT NULL THEN t.from_address END) AS returning_gamers
FROM ronin.core.fact_transactions t
JOIN ronin.core.dim_labels dl
ON t.to_address = dl.address
LEFT JOIN q1_users q1
ON t.from_address = q1.from_address
AND DATE(t.block_timestamp) = q1.tx_date
LEFT JOIN previous_users p
ON t.from_address = p.from_address
WHERE t.block_timestamp >= '2025-01-01'
AND t.block_timestamp < '2025-04-01'
AND dl.label_type = 'games'
Last run: 24 days ago
90
5KB
16s