TIME_TO_ADOPTION | USER_COUNT | AVG_DAYS_TO_DEFI | |
|---|---|---|---|
1 | Over a month | 54449 | 75.550203 |
2 | Within a week | 45162 | 4.360303 |
3 | Same day | 44921 | 0.226331 |
4 | Within a month | 42458 | 16.697089 |
m3jiGaming to DeFi
Updated 2025-06-06
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
›
⌄
-- select DISTINCT label_type from ronin.core.dim_labels
-- Track User Journey from Gaming to DeFi
WITH gaming_users AS (
SELECT DISTINCT
tx.from_address AS wallet,
MIN(tx.block_timestamp) AS first_game_interaction
FROM ronin.core.fact_transactions tx
JOIN ronin.core.dim_labels c ON tx.to_address = c.address
WHERE c.LABEL_TYPE IN ('nft', 'games') -- Replace with actual game contract names
AND tx.block_timestamp >= CURRENT_DATE - 180
GROUP BY wallet
),
defi_users AS (
SELECT DISTINCT
tx.from_address AS wallet,
MIN(tx.block_timestamp) AS first_defi_interaction
FROM ronin.core.fact_transactions tx
JOIN ronin.core.dim_labels c ON tx.to_address = c.address
WHERE c.LABEL_TYPE IN ('dex', 'token', 'cex', 'dapp') -- Replace with actual DeFi contract names
AND tx.block_timestamp >= CURRENT_DATE - 180
GROUP BY wallet
),
user_journey AS (
SELECT
g.wallet,
g.first_game_interaction,
d.first_defi_interaction,
DATEDIFF('day', g.first_game_interaction, d.first_defi_interaction) AS days_to_defi
FROM gaming_users g
JOIN defi_users d ON g.wallet = d.wallet
WHERE g.first_game_interaction < d.first_defi_interaction
)
Last run: 20 days ago
4
130B
11s