Microsoft: Gamerscore Awarded per Xbox Title — SQL Problem
An Xbox achievements analyst is reviewing how much Gamerscore each game has handed out to players.
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #776
Problem statement
An Xbox achievements analyst is reviewing how much Gamerscore each game has handed out to players.
For every title in the catalogue, return title_name, unlocks (the number of achievement unlocks for the title) and gamerscore_awarded (the sum of the Gamerscore of those unlocks). A title with no achievements, or none unlocked yet, appears with 0 in both columns. Order by gamerscore_awarded descending, then title_name ascending.
A practice scenario set at Microsoft: the tables and data are invented, and this is not a question from, or endorsed by, Microsoft.
Tables
Table: XboxTitle
| Column | Type |
|---|---|
| title_id | int |
| title_name | varchar |
Primary key: title_id.
One row per game; title_name is unique.
Table: Achievement
| Column | Type |
|---|---|
| achievement_id | int |
| title_id | int |
| gamerscore | int |
Primary key: achievement_id.
One row per achievement a title offers, with the Gamerscore it is worth.
Table: AchievementUnlock
| Column | Type |
|---|---|
| gamertag | varchar |
| achievement_id | int |
| unlocked_at | datetime |
Primary key: gamertag, achievement_id.
One row per player per achievement they unlocked.
Examples
Example 1
XboxTitle
| title_id | title_name |
|---|---|
| 1 | Forza Horizon 5 |
| 2 | Halo Infinite |
| 3 | Starfield |
| 4 | Sea of Thieves |
Achievement
| achievement_id | title_id | gamerscore |
|---|---|---|
| 101 | 1 | 10 |
| 102 | 1 | 50 |
| 103 | 2 | 20 |
| 104 | 2 | 100 |
| 105 | 3 | 15 |
AchievementUnlock
| gamertag | achievement_id | unlocked_at |
|---|---|---|
| ShadowKabir | 101 | 2025-02-01 18:00:00 |
| ShadowKabir | 102 | 2025-02-03 21:30:00 |
| PixelPriya | 101 | 2025-02-04 20:00:00 |
| PixelPriya | 103 | 2025-02-05 22:15:00 |
| NovaRiya | 104 | 2025-02-06 19:45:00 |
Output
| title_name | unlocks | gamerscore_awarded |
|---|---|---|
| Halo Infinite | 2 | 120 |
| Forza Horizon 5 | 3 | 70 |
| Sea of Thieves | 0 | 0 |
| Starfield | 0 | 0 |
How to solve Microsoft: Gamerscore Awarded per Xbox Title
Each title must appear even when it has nothing to report, so the query starts from XboxTitle and LEFT JOINs down the chain: title → achievements → unlocks. A title with no achievements keeps one row of NULLs; an achievement no one unlocked keeps one row with the unlock columns NULL.
That second case is why the sum is conditional. The row of an unlocked-by-nobody achievement still carries its gamerscore, so a plain SUM(a.gamerscore) would award points nobody earned. Summing only where u.gamertag IS NOT NULL fixes it, and COALESCE(…, 0) turns the empty sum (NULL) into 0. COUNT(u.gamertag) likewise counts only real unlocks.
The alternative uses two correlated subqueries per title — easier to read, but it scans the unlocks twice per title; the join version does one pass with a GROUP BY.
Reference solution (MySQL)
SELECT t.title_name,
COUNT(u.gamertag) AS unlocks,
COALESCE(SUM(CASE WHEN u.gamertag IS NOT NULL THEN a.gamerscore END), 0) AS gamerscore_awarded
FROM XboxTitle t
LEFT JOIN Achievement a ON a.title_id = t.title_id
LEFT JOIN AchievementUnlock u ON u.achievement_id = a.achievement_id
GROUP BY t.title_id, t.title_name
ORDER BY gamerscore_awarded DESC, t.title_nameAnother way
SELECT t.title_name,
(SELECT COUNT(*) FROM AchievementUnlock u JOIN Achievement a ON a.achievement_id = u.achievement_id WHERE a.title_id = t.title_id) AS unlocks,
(SELECT COALESCE(SUM(a.gamerscore), 0) FROM AchievementUnlock u JOIN Achievement a ON a.achievement_id = u.achievement_id WHERE a.title_id = t.title_id) AS gamerscore_awarded
FROM XboxTitle t
ORDER BY gamerscore_awarded DESC, title_name← Microsoft: Licensed Users With No Successful Sign-In · Microsoft: Outlook Mailboxes at 90% of Their Quota or More →