Skip to main content

Gameplay & 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 `live_publicdemo_35f6889f_checkpoints.progression_checkpoint`
  WHERE LOWER(progression_status) = 'complete'
    AND game_id = 237761
    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 `live_publicdemo_35f6889f_checkpoints.progression_checkpoint`
  WHERE LOWER(progression_status) = 'complete'
    AND game_id = 237761
    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;