Skip to main content

Data Warehouse example queries

This section contains some example queries which you can run using your Data Warehouse.

Gameplay and progression​

List of players ranked by game progress and score​

WITH completed_runs AS (
  SELECT
    user_id,
    progression_1 AS world_name,
    score,
    checkpoint
  FROM `checkpoints.progression_checkpoint`
  WHERE progression_status = 'complete'
    AND game_id = 1234
    AND checkpoint = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
),


ranked_scores AS (
  SELECT
    user_id,
    world_name,
    score,
    RANK() OVER (PARTITION BY world_name ORDER BY score DESC) AS score_rank
  FROM completed_runs
)


SELECT
  world_name,
  user_id,
  score
FROM ranked_scores
WHERE score_rank = 1
ORDER BY world_name;

How far users are in the game​

WITH completed_worlds AS (
  SELECT
    user_id,
    progression_1 AS world_name,
    checkpoint,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY checkpoint DESC) AS rn
  FROM `checkpoints.progression_checkpoint`
  WHERE progression_status = 'complete'
    AND game_id = 1234
    AND checkpoint = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
)


SELECT
  user_id,
  world_name,
  checkpoint AS last_completed_at
FROM completed_worlds
WHERE rn = 1
ORDER BY last_completed_at DESC;

Live ops and In-game economies​

A/B Test revene per user​

DECLARE target_ab_test_id STRING DEFAULT '';  -- Replace with your A/B test ID


SELECT
  user_id,
  SUM(revenue_usd) AS total_revenue_usd
FROM `checkpoints.player_checkpoint`
WHERE ab_test_id = target_ab_test_id
  AND game_id = 1234
GROUP BY user_id
ORDER BY total_revenue_usd DESC;
SELECT
  DATE(checkpoint) AS day,
  resource_currency,
  SUM(resource_amount_sum) AS total_sourced
FROM `checkpoints.resource_checkpoint`
WHERE flow_type = 'Source'
  AND game_id = 1234  -- assuming game_id is needed here too
  AND checkpoint = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY day, resource_currency
ORDER BY day DESC, total_sourced DESC;

Monetization and revenue​

Top revenue generating currency​

SELECT
  currency, SUM(amount_usd) as total_in_usd_cents
FROM
  `checkpoints.payment_checkpoint`
WHERE
  checkpoint = "2025-10-01"
  AND game_id = 1234
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1

Player activity and Retention​

Retention​

WITH install_data AS (  
  SELECT
    user_id,
    install_ts as install_date,
    checkpoint as snapshot_date,
    DATE_DIFF(checkpoint, install_ts, DAY) as days_since_install
  FROM
    `checkpoints.player_checkpoint`
  WHERE
    game_id = 1234
    AND checkpoint →= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
    AND install_ts →= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
),
returning_users AS
  (
  SELECT
    install_date,
    COUNT(DISTINCT user_id) as number_of_installs,
    COUNT(DISTINCT IF(days_since_install = 1, user_id, NULL)) as returning_d1,
    COUNT(DISTINCT IF(days_since_install = 2, user_id, NULL)) as returning_d2,
    COUNT(DISTINCT IF(days_since_install = 3, user_id, NULL)) as returning_d3,
    COUNT(DISTINCT IF(days_since_install = 4, user_id, NULL)) as returning_d4,
    COUNT(DISTINCT IF(days_since_install = 5, user_id, NULL)) as returning_d5,
    COUNT(DISTINCT IF(days_since_install = 6, user_id, NULL)) as returning_d6,
    COUNT(DISTINCT IF(days_since_install = 7, user_id, NULL)) as returning_d7,
  FROM
    install_data
  GROUP BY 1
)
SELECT
  install_date,
  number_of_installs,
  SAFE_DIVIDE(returning_d1, number_of_installs) as retention_d1,
  SAFE_DIVIDE(returning_d2, number_of_installs) as retention_d2,
  SAFE_DIVIDE(returning_d3, number_of_installs) as retention_d3,
  SAFE_DIVIDE(returning_d4, number_of_installs) as retention_d4,
  SAFE_DIVIDE(returning_d5, number_of_installs) as retention_d5,
  SAFE_DIVIDE(returning_d6, number_of_installs) as retention_d6,
  SAFE_DIVIDE(returning_d7, number_of_installs) as retention_d7,
FROM
  returning_users
ORDER BY 1

Ad views per day​

SELECT
  DATE(checkpoint) AS date,
  SUM(ad_impressions) AS total_ad_views
FROM
  `checkpoints.adactivity_checkpoint`
WHERE
  game_id = 1234
  AND checkpoint = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) -- only yesterday's data
GROUP BY
  date
ORDER BY
  date DESC;
SELECT
  TIMESTAMP_TRUNC(event_arrival_ts, HOUR) AS hour,
  SUM(publisher_revenue_usd_cents) AS revenue_usd
FROM
  `events.impression_event`
WHERE
  event_arrival_ts →= TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 1 DAY)
  AND event_arrival_ts < TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)
  AND game_id = 1234
GROUP BY
  hour
ORDER BY
  hour;

Technical performance​

Devices sending the most errors​

SELECT
  device,
  sum(error_error_count) as total_errors
  -- sum(error_critical_count)
  -- sum(error_warning_count)
  -- sum(error_info_count)
  -- sum(error_debug_count)
FROM
  `checkpoints.player_checkpoint`
WHERE
  checkpoint = "2025-10-01"
  AND game_id = 1234
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1