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…