Home
Documentation

Currently Trending Game Stats

Stay ahead with real-time stats from the hottest games.

Suggest Edits

Arcade Champion is a popular game on the SUI blockchain that has been trending for the past couple of months. This use case leverages ZettaBlock's platform to analyze and display key gaming statistics, providing insights into player behavior and game dynamics.

📘

This dashboard serves as a central hub for this data.

Data Points

The primary data points include:

  1. Player Activity: Total number of active players, number of unique players, and number of transactions.
  2. Game Performance Metrics: New vs old users, user engagement & retention.
  3. User Actions and Top Users: most common user actions & identifying users who engaged the most with the game.

Analysis

To obtain the data for the above metrics, please use the SQL code below:

WITH top_level as (
SELECT
  t.*,
  b.sender
FROM sui_mainnet.transactions as t
INNER JOIN sui_mainnet.transaction_blocks as b ON t.transaction_block_digest=b.digest
WHERE t.data_creation_date >= DATE('2023-05-26')
AND b.data_creation_date >= DATE('2023-05-26')
AND (
t.package = '0xf09c99aa87a706c4f010c8c6b86d0249ccf670a138dcbcc7af9f4da8573019fe' or
t.package = '0xef9124bfbeefc494e74ef7d4f4394018b7a094ccccb9a149a67eb04d4f79c034'
)
),
unique_users as (
SELECT
  date_trunc('{{date_trunc}}',block_time) as dt,
  COUNT(distinct sender) as unique_users
from
(
  select
    sender AS sender,
    MIN(block_time) AS block_time
  FROM top_level
  GROUP BY 1
)
GROUP BY 1
),
aggregation as (
SELECT
  date_trunc('day',block_time) as dt,
  COUNT(distinct transaction_block_digest) as num_transactions,
  COUNT(distinct sender) as unique_active_users
FROM top_level
GROUP BY 1
)
select
  a.dt,
  a.num_transactions,
  SUM(a.num_transactions) OVER (ORDER BY a.dt) as cumulative_transactions,
  a.unique_active_users,
  uu.unique_users,
  round(CAST(uu.unique_users as double)/CAST(a.unique_active_users as double),3) as ratio_new_old,
  SUM(uu.unique_users) OVER (ORDER BY a.dt) as cumulative_new_users
FROM aggregation as a
LEFT JOIN unique_users as uu 
ON a.dt=uu.dt
ORDER BY 1 ASC

This query provides insights into player activity, differentiating between new and returning users, and tracking the overall engagement in terms of transactions.

To calculate which users engaged the most (biggest number of updates), use this SQL code:

SELECT
    sender as user,
    COUNT(distinct transaction_block_digest) as hero_updated_count,
    MIN(block_time) as first_updated,
    MAX(block_time) as last_updated
FROM sui_mainnet.events as e
WHERE e.data_creation_date >= DATE('2023-05-26')
AND (
    e.package_id = '0xf09c99aa87a706c4f010c8c6b86d0249ccf670a138dcbcc7af9f4da8573019fe' or
    e.package_id = '0xef9124bfbeefc494e74ef7d4f4394018b7a094ccccb9a149a67eb04d4f79c034'
)
AND type like '%:arcade_champion::HeroUpdated'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 500

This query identifies the top players based on their engagement level, specifically focusing on the frequency of their game-related updates.

This use case is just an example of how to analyze a currently trending game on any blockchain.

The outlined SQL queries are designed to extract meaningful insights into player activities, game performance, and user engagement, enabling developers to make data-driven decisions for game improvements and marketing strategies.

Feel free to customize these SQL examples by substituting in different package/contract addresses, depending on the specific blockchain & the game you are interested in.

Updated 10 months ago