Day | Daily Transactions | Cummulative Daily Transactions | Unique Users | Transaction Success Rate | New Users | Returning Users | New Users Over Time | Succeeded Transactions | Failed Transactions | Daily Retention Rate | Churned Users | Churned Users (-) | Churned Users Over Time | Transaction Per User | Total Gas Fees | AVG Gas Fee Per User | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 2025-03-24 00:00:00.000 | 222027 | 57941690 | 218183 | 99.979282 | 3626 | 214557 | 3816786 | 221981 | 46 | 91.064756 | 20205 | -20205 | 7749987 | 1.017618 | 309.18394908 | 0.001417085424 |
2 | 2025-03-23 00:00:00.000 | 229829 | 57719663 | 226127 | 99.994779 | 5176 | 220951 | 3813160 | 229817 | 12 | 96.30906 | 8054 | -8054 | 7729782 | 1.016371 | 312.464066333 | 0.001381807862 |
3 | 2025-03-22 00:00:00.000 | 225398 | 57489834 | 218210 | 99.992901 | 4662 | 213548 | 3807984 | 225382 | 16 | 93.748409 | 13510 | -13510 | 7721728 | 1.032941 | 417.713091991 | 0.001914271078 |
4 | 2025-03-21 00:00:00.000 | 221874 | 57264436 | 216105 | 99.990986 | 5331 | 210774 | 3803322 | 221854 | 20 | 91.099829 | 19827 | -19827 | 7708218 | 1.026695 | 335.865422075 | 0.001554177007 |
5 | 2025-03-20 00:00:00.000 | 226023 | 57042562 | 222771 | 99.988497 | 2567 | 220204 | 3797991 | 225997 | 26 | 93.665304 | 14407 | -14407 | 7688391 | 1.014598 | 307.61170419 | 0.001380842678 |
6 | 2025-03-19 00:00:00.000 | 231923 | 56816539 | 227430 | 99.993101 | 5215 | 222215 | 3795424 | 231907 | 16 | 93.801584 | 14224 | -14224 | 7673984 | 1.019756 | 440.656126542 | 0.001937546175 |
7 | 2025-03-18 00:00:00.000 | 236183 | 56584616 | 229478 | 99.992379 | 7436 | 222042 | 3790209 | 236165 | 18 | 94.790591 | 11503 | -11503 | 7659760 | 1.029218 | 320.453957288 | 0.001396447404 |
8 | 2025-03-17 00:00:00.000 | 225426 | 56348433 | 220812 | 99.991572 | 2076 | 218736 | 3782773 | 225407 | 19 | 92.871473 | 15883 | -15883 | 7648257 | 1.020896 | 296.5252006 | 0.001342885353 |
9 | 2025-03-16 00:00:00.000 | 229617 | 56123007 | 222809 | 99.954707 | 4187 | 218622 | 3780697 | 229513 | 104 | 95.011267 | 10959 | -10959 | 7632374 | 1.030555 | 297.46479863 | 0.001335066351 |
10 | 2025-03-15 00:00:00.000 | 224773 | 55893390 | 219675 | 99.990657 | 1780 | 217895 | 3776510 | 224752 | 21 | 91.823248 | 18708 | -18708 | 7621415 | 1.023207 | 293.471752612 | 0.001335936054 |
11 | 2025-03-14 00:00:00.000 | 234387 | 55668617 | 228795 | 99.991467 | 2104 | 226691 | 3774730 | 234367 | 20 | 96.140401 | 8634 | -8634 | 7602707 | 1.024441 | 305.633303718 | 0.001335839086 |
12 | 2025-03-13 00:00:00.000 | 229767 | 55434230 | 223702 | 99.956913 | 2156 | 221546 | 3772626 | 229668 | 99 | 92.973548 | 16121 | -16121 | 7594073 | 1.027112 | 299.90235527 | 0.001340633321 |
13 | 2025-03-12 00:00:00.000 | 234156 | 55204463 | 229433 | 99.995729 | 1482 | 227951 | 3770470 | 234146 | 10 | 94.631372 | 12471 | -12471 | 7577952 | 1.020586 | 305.739007527 | 0.001332585145 |
14 | 2025-03-11 00:00:00.000 | 237070 | 54970307 | 232294 | 99.994938 | 2054 | 230240 | 3768988 | 237058 | 12 | 93.090777 | 16677 | -16677 | 7565481 | 1.02056 | 309.906141987 | 0.001334111695 |
15 | 2025-03-10 00:00:00.000 | 247270 | 54733237 | 241373 | 99.987868 | 2840 | 238533 | 3766934 | 247240 | 30 | 96.954627 | 7171 | -7171 | 7548804 | 1.024431 | 322.340805435 | 0.001335446821 |
16 | 2025-03-09 00:00:00.000 | 242535 | 54485967 | 235472 | 99.992166 | 1979 | 233493 | 3764094 | 242516 | 19 | 92.583895 | 18190 | -18190 | 7541633 | 1.029995 | 316.646382234 | 0.001344730508 |
17 | 2025-03-08 00:00:00.000 | 251918 | 54243432 | 245277 | 99.995237 | 4179 | 241098 | 3762115 | 251906 | 12 | 56.063073 | 5127 | -5127 | 7523443 | 1.027076 | 329.814299187 | 0.001344660523 |
18 | 2025-03-07 00:00:00.000 | 24256 | 53991514 | 11669 | 99.909301 | 3089 | 8580 | 3757936 | 24234 | 22 | 0.975612 | 263595 | -263595 | 7518316 | 2.07867 | 27.239250737 | 0.002334326055 |
19 | 2025-03-06 00:00:00.000 | 274006 | 53967258 | 266192 | 99.991971 | 2349 | 263843 | 3754847 | 273984 | 22 | 97.362679 | 6865 | -6865 | 7254721 | 1.029355 | 356.447849688 | 0.001339062968 |
20 | 2025-03-05 00:00:00.000 | 267521 | 53693252 | 260302 | 99.992898 | 10508 | 249794 | 3752498 | 267502 | 19 | 12.502123 | 5151 | -5151 | 7247856 | 1.027733 | 350.012264075 | 0.001344639166 |
i_danPixel: Total Daily Metrics
Updated 8 days ago
999
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
›
⌄
-- Daily GameFi Metrics on Ronin
WITH base_data AS (
SELECT
block_timestamp::DATE AS day
, to_address AS game_contract
, from_address AS user
, tx_succeeded
, tx_hash
, tx_fee
, MIN(block_timestamp::DATE) OVER (PARTITION BY from_address, to_address) AS user_first_day
FROM ronin.core.fact_transactions
WHERE to_address IN (
'0x02790f32ad7e7eaaecfb0ad21950829932f1a2ee',
'0x7eae20d11ef8c779433eb24503def900b9d28ad7'
)
-- AND block_timestamp >= '2025-01-01'
),
-- Create a distinct set of user activity days for efficient joining
user_activity_days AS (
SELECT DISTINCT
user
, day
FROM base_data
),
-- Calculate daily metrics
daily_metrics AS (
SELECT
day
, COUNT(DISTINCT tx_hash) AS total_transactions
, COUNT(DISTINCT user) AS unique_users
, COUNT(CASE WHEN tx_succeeded = 'TRUE'THEN tx_hash END) AS Succeeded_Transactions
, COUNT(CASE WHEN tx_succeeded = 'FALSE'THEN tx_hash END) AS Failed_Transactions
, SUM(CASE WHEN tx_succeeded THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(tx_hash), 0) AS transaction_success_rate
, COUNT(DISTINCT CASE WHEN day = user_first_day THEN user END) AS new_users
Last run: 8 days ago
...
175
27KB
25s