Skip to main content

Example Queries

Total Daily Active Users and Players across all games​

SELECT checkpoint, COUNT(DISTINCT user_id) AS DAUsers, COUNT(player_id) AS DAPlayers
FROM project_id.dataset_id.player_checkpoint
WHERE checkpoint >= '2020-01-01' AND checkpoint < '2020-01-08'
GROUP BY checkpoint
ORDER BY checkpoint

Top Countries New Users per game​

SELECT checkpoint, country_code, game_id, COUNT(DISTINCT user_id) as DAU
FROM project_id.dataset_id.player_checkpoint
WHERE checkpoint >= '2020-01-01' AND checkpoint < '2020-01-08'
AND checkpoint = install_ts
AND country_code IN ('US', 'GB', 'SA', 'CN', 'FR', 'AU', 'BR')
GROUP BY checkpoint, country_code, game_id
ORDER BY checkpoint DESC

Devices sending the most errors​

SELECT manufacturer, device, SUM(error_critical_count) as errors
FROM project_id.dataset_id.player_checkpoint
WHERE checkpoint >= '2020-01-01' AND checkpoint < '2020-02-01'
AND game_id = 123456
GROUP BY manufacturer, device
ORDER BY errors DESC

Daily total session length across all games of US players that installed this year​

SELECT SUM(session_length_sum)
FROM project_id.dataset_id.player_checkpoint
WHERE checkpoint = '2020-01-31'
AND install_ts > '2020-01-01'
AND country_code = 'US'

Latest retention day 1 for paying user​

SELECT ret.returning_users/total.new_users AS ret_d1
FROM
  (
    SELECT COUNT(player_id) AS returning_users
    FROM project_id.dataset_id.player_checkpoint
    WHERE checkpoint = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
    AND install_ts = DATE_ADD(checkpoint, INTERVAL -2 DAY)
    AND game_id = 123456
    AND is_paying
  ) AS ret,
  (
    SELECT COUNT(player_id) as new_users
    FROM project_id.dataset_id.player_checkpoint
    WHERE checkpoint = DATE_ADD(CURRENT_DATE(), INTERVAL -2 DAY)
    AND install_ts = checkpoint
    AND game_id = 123456
    AND is_paying
  ) AS total

Currency bringing in the most revenue​

SELECT currency, SUM(amount_usd) AS revenue_usd
FROM project_id.dataset_id.payment_checkpoint
WHERE checkpoint = "2020-01-01"
AND game_id = 123456
GROUP BY currency
SELECT item_id, is_converting as new_buyers, COUNT(*) AS purchases
FROM project_id.dataset_id.payment_checkpoint
WHERE checkpoint = "2020-01-01"
AND game_id = 123456
GROUP BY item_id, is_converting
ORDER BY purchases DESC
SELECT EXTRACT(hour FROM session_start_ts) AS session_start_utc_hour, COUNT(*) AS session_count, SUM(session_length) AS session_length
FROM project_id.dataset_id.session_checkpoint
WHERE checkpoint = "2020-01-01"
AND game_id = 123456
GROUP BY session_start_utc_hour
ORDER BY session_length DESC

Levels progression – where do players get stuck?​

SELECT progression_1, progression_status, COUNT(DISTINCT(player_id)) AS players
FROM project_id.dataset_id.progression_checkpoint
WHERE checkpoint >= "2020-01-01" AND checkpoint < "2020-01-08"
AND game_id = 123456
GROUP BY progression_1, progression_status
ORDER BY progression_1 ASC, progression_status DESC

Most skilled players​

SELECT pr.player_id, COUNT(*) AS levels_completed, MAX(score) as score, MAX(session_count) as session_count, MAX(session_length_sum) as playtime
FROM project_id.dataset_id.progression_checkpoint as pr
LEFT JOIN project_id.dataset_id.player_checkpoint as pl
ON pr.checkpoint = pl.checkpoint AND pr.player_id = pl.player_id
WHERE pr.checkpoint = "2020-01-01" AND pl.checkpoint = "2020-01-01"
AND pr.game_id = 123456 AND pl.game_id = 123456
AND progression_status = "Complete"
GROUP BY pr.player_id
ORDER BY levels_completed DESC