Drill 3 of 12 in Gaming questions
0

Interview Question Bank · MAX per player · LEFT JOIN · COALESCE

3. Where players stop: furthest level reached

EasyGamingQuestion #147

Concept: MAX per player · LEFT JOIN · COALESCEshow

To see where people stop, first boil each person down to one number (their furthest step, with MAX), then count people per value. Counting raw rows per step answers a different question: how many people passed it. People with no rows at all stopped before step one, so start from the full list of people, LEFT JOIN the progress, and COALESCE their NULL to 0.

Example (different data):

WITH furthest AS (
  SELECT s.id, COALESCE(MAX(m.module_no), 0) AS last_module
  FROM students s
  LEFT JOIN module_passes m ON m.student_id = s.id
  GROUP BY s.id
)
SELECT last_module, COUNT(*) AS students
FROM furthest
GROUP BY last_module;

Your task

Skyrift Arena's campaign has five levels played in order. The level designers suspect one of them is a wall, and want to see where players' progress stops.

For every player in gm_players, take the highest level they completed in gm_level_completions (0 if they never completed one). Return max_level and players (how many players stopped there). Sort by max_level.

Starting Postgres in your browser…

Checking your access…