WEEK | PLAYER_CATEGORY | NUM_PLAYERS | TOTAL_TXS | AVG_TXS_PER_PLAYER | |
|---|---|---|---|---|---|
1 | 2025-03-10 00:00:00.000 | Casual Gamer | 4747 | 34300 | 7.225616 |
2 | 2025-03-03 00:00:00.000 | Casual Gamer | 4807 | 34743 | 7.227585 |
3 | 2025-02-24 00:00:00.000 | Casual Gamer | 4911 | 36831 | 7.499695 |
4 | 2025-02-24 00:00:00.000 | Power Player | 1 | 272 | 272 |
5 | 2025-02-17 00:00:00.000 | Casual Gamer | 4893 | 36712 | 7.502963 |
6 | 2025-02-17 00:00:00.000 | Power Player | 2 | 423 | 211.5 |
7 | 2025-02-10 00:00:00.000 | Casual Gamer | 5207 | 38430 | 7.379032 |
8 | 2025-02-10 00:00:00.000 | Power Player | 2 | 438 | 219 |
9 | 2025-02-03 00:00:00.000 | Casual Gamer | 5189 | 41370 | 7.972634 |
10 | 2025-02-03 00:00:00.000 | Power Player | 1 | 111 | 111 |
11 | 2025-01-27 00:00:00.000 | Casual Gamer | 5286 | 46350 | 8.768445 |
12 | 2025-01-27 00:00:00.000 | Power Player | 6 | 770 | 128.333333 |
13 | 2025-01-20 00:00:00.000 | Casual Gamer | 4742 | 41000 | 8.646141 |
14 | 2025-01-20 00:00:00.000 | Power Player | 2 | 413 | 206.5 |
15 | 2025-01-13 00:00:00.000 | Casual Gamer | 5088 | 42855 | 8.422759 |
16 | 2025-01-13 00:00:00.000 | Power Player | 5 | 933 | 186.6 |
17 | 2025-01-06 00:00:00.000 | Casual Gamer | 4863 | 43275 | 8.898828 |
18 | 2025-01-06 00:00:00.000 | Power Player | 6 | 707 | 117.833333 |
19 | 2024-12-30 00:00:00.000 | Casual Gamer | 4393 | 37027 | 8.428636 |
20 | 2024-12-30 00:00:00.000 | Power Player | 1 | 117 | 117 |
adriaparcerisasavalanche gamers behavior weekly
Updated 2025-03-17
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
›
⌄
WITH player_activity AS (
SELECT
origin_from_address AS player,
project_name,
DATE_TRUNC('week', block_timestamp) as week,
COUNT(*) AS total_txs
FROM avalanche.core.fact_event_logs x
JOIN avalanche.core.dim_labels y
ON x.contract_address = y.address
WHERE label_type = 'games'
--AND block_timestamp >= DATEADD('day', -30, CURRENT_DATE) -- Last 30 days by default
GROUP BY 1, 2, 3
),
categorized_players AS (
SELECT
player,
project_name,
week,
total_txs,
CASE
WHEN total_txs > 100 THEN 'Power Player'
ELSE 'Casual Gamer'
END AS player_category
FROM player_activity
)
SELECT
week,
player_category,
COUNT(DISTINCT player) AS num_players,
SUM(total_txs) AS total_txs,
AVG(total_txs) as avg_txs_per_player
FROM categorized_players where week<trunc(current_date,'week')
GROUP BY week, player_category
ORDER BY week DESC, total_txs DESC
Last run: 27 days ago
...
245
14KB
4s