Microsoft: Xbox Players Who Unlocked Every Achievement in a Title
The Xbox achievements team wants to know who has fully completed a game — unlocked every achievement it offers — and how long the run took.
- Difficulty: Medium
- Topics: Joins, Aggregation, Subqueries, Dates
- Dialect: MySQL
- Problem: #783
Problem statement
The Xbox achievements team wants to know who has fully completed a game — unlocked every achievement it offers — and how long the run took.
Return gamertag, title_name and days_to_complete for every player who has unlocked all of a title's achievements, where days_to_complete is the number of calendar days between the date of their first unlock in that title and the date of their last (DATEDIFF, so the time of day is ignored). A title with no achievements cannot be completed. Return the rows in any order.
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.
Table: Achievement
| Column | Type |
|---|---|
| achievement_id | int |
| title_id | int |
| achievement_name | varchar |
Primary key: achievement_id.
One row per achievement a title offers.
Table: AchievementUnlock
| Column | Type |
|---|---|
| gamertag | varchar |
| achievement_id | int |
| unlocked_at | datetime |
Primary key: gamertag, achievement_id.
One row per player per achievement unlocked.
Examples
Example 1
XboxTitle
| title_id | title_name |
|---|---|
| 1 | Halo Infinite |
| 2 | Forza Motorsport |
| 3 | Hi-Fi Rush |
Achievement
| achievement_id | title_id | achievement_name |
|---|---|---|
| 201 | 1 | Spartan Initiate |
| 202 | 1 | Grapple Master |
| 203 | 1 | Legendary Finish |
| 204 | 2 | Green Flag |
| 205 | 2 | Podium Finish |
AchievementUnlock
| gamertag | achievement_id | unlocked_at |
|---|---|---|
| NovaRiya | 201 | 2025-01-01 10:00:00 |
| NovaRiya | 202 | 2025-01-03 21:00:00 |
| NovaRiya | 203 | 2025-01-08 23:00:00 |
| PixelPriya | 201 | 2025-01-02 18:00:00 |
| PixelPriya | 202 | 2025-01-05 19:30:00 |
| NovaRiya | 204 | 2025-02-10 08:00:00 |
| NovaRiya | 205 | 2025-02-10 09:15:00 |
| ShadowKabir | 204 | 2025-02-01 23:50:00 |
| ShadowKabir | 205 | 2025-02-02 00:10:00 |
Output
| gamertag | title_name | days_to_complete |
|---|---|---|
| NovaRiya | Halo Infinite | 7 |
| NovaRiya | Forza Motorsport | 0 |
| ShadowKabir | Forza Motorsport | 1 |
How to solve Microsoft: Xbox Players Who Unlocked Every Achievement in a Title
"Unlocked every achievement" is relational division. The counting form is the easiest to get right: for each (gamertag, title) count the achievements the player unlocked, for each title count the achievements it offers, and keep the pairs where the two counts are equal. The primary key on (gamertag, achievement_id) guarantees the player's count is of distinct achievements, so equality really means "all of them". A title with no achievements has no row in per_title and no unlocks, so it can never qualify.
The same GROUP BY gives the first and last unlock, and DATEDIFF(last, first) returns calendar days — MySQL ignores the time part, so a completion that ran from 23:50 to 00:10 counts as 1 day, and one finished on the same day as 0.
The alternative states division literally with a double NOT EXISTS: keep a (player, title) pair if there is no achievement of the title that the player has not unlocked. It is elegant but correlated twice; the counting version is two aggregations and a join.
Reference solution (MySQL)
WITH per_player AS (
SELECT u.gamertag, a.title_id, COUNT(*) AS unlocked,
MIN(u.unlocked_at) AS first_unlock, MAX(u.unlocked_at) AS last_unlock
FROM AchievementUnlock u
JOIN Achievement a ON a.achievement_id = u.achievement_id
GROUP BY u.gamertag, a.title_id
), per_title AS (
SELECT title_id, COUNT(*) AS offered FROM Achievement GROUP BY title_id
)
SELECT p.gamertag, t.title_name, DATEDIFF(p.last_unlock, p.first_unlock) AS days_to_complete
FROM per_player p
JOIN per_title pt ON pt.title_id = p.title_id AND pt.offered = p.unlocked
JOIN XboxTitle t ON t.title_id = p.title_idAnother way
SELECT g.gamertag, t.title_name,
DATEDIFF(
(SELECT MAX(u.unlocked_at) FROM AchievementUnlock u JOIN Achievement a ON a.achievement_id = u.achievement_id WHERE u.gamertag = g.gamertag AND a.title_id = g.title_id),
(SELECT MIN(u.unlocked_at) FROM AchievementUnlock u JOIN Achievement a ON a.achievement_id = u.achievement_id WHERE u.gamertag = g.gamertag AND a.title_id = g.title_id)
) AS days_to_complete
FROM (SELECT DISTINCT u.gamertag, a.title_id FROM AchievementUnlock u JOIN Achievement a ON a.achievement_id = u.achievement_id) g
JOIN XboxTitle t ON t.title_id = g.title_id
WHERE NOT EXISTS (
SELECT 1 FROM Achievement a2
WHERE a2.title_id = g.title_id
AND NOT EXISTS (SELECT 1 FROM AchievementUnlock u2 WHERE u2.gamertag = g.gamertag AND u2.achievement_id = a2.achievement_id)
)← Microsoft: Teams Channels Where Guests Post Most Messages · Microsoft: Paid but Unassigned Microsoft 365 Seats on 30 June →