Microsoft: Xbox Players Who Unlocked Every Achievement in a Title

Medium
783.

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.

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

ColumnType
title_idint
title_namevarchar

Primary key: title_id.

One row per game.

Table: Achievement

ColumnType
achievement_idint
title_idint
achievement_namevarchar

Primary key: achievement_id.

One row per achievement a title offers.

Table: AchievementUnlock

ColumnType
gamertagvarchar
achievement_idint
unlocked_atdatetime

Primary key (together): gamertag, achievement_id.

One row per player per achievement unlocked.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

XboxTitle

title_idtitle_name
1Halo Infinite
2Forza Motorsport
3Hi-Fi Rush

Achievement

achievement_idtitle_idachievement_name
2011Spartan Initiate
2021Grapple Master
2031Legendary Finish
2042Green Flag
2052Podium Finish

AchievementUnlock

gamertagachievement_idunlocked_at
NovaRiya2012025-01-01 10:00:00
NovaRiya2022025-01-03 21:00:00
NovaRiya2032025-01-08 23:00:00
PixelPriya2012025-01-02 18:00:00
PixelPriya2022025-01-05 19:30:00
NovaRiya2042025-02-10 08:00:00
NovaRiya2052025-02-10 09:15:00
ShadowKabir2042025-02-01 23:50:00
ShadowKabir2052025-02-02 00:10:00

Output

gamertagtitle_namedays_to_complete
NovaRiyaHalo Infinite7
NovaRiyaForza Motorsport0
ShadowKabirForza Motorsport1
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
title_idint
title_namevarchar

Primary key: title_id.

One row per game.

Table: Achievement

ColumnType
achievement_idint
title_idint
achievement_namevarchar

Primary key: achievement_id.

One row per achievement a title offers.

Table: AchievementUnlock

ColumnType
gamertagvarchar
achievement_idint
unlocked_atdatetime

Primary key: gamertag, achievement_id.

One row per player per achievement unlocked.

Examples

Example 1

XboxTitle

title_idtitle_name
1Halo Infinite
2Forza Motorsport
3Hi-Fi Rush

Achievement

achievement_idtitle_idachievement_name
2011Spartan Initiate
2021Grapple Master
2031Legendary Finish
2042Green Flag
2052Podium Finish

AchievementUnlock

gamertagachievement_idunlocked_at
NovaRiya2012025-01-01 10:00:00
NovaRiya2022025-01-03 21:00:00
NovaRiya2032025-01-08 23:00:00
PixelPriya2012025-01-02 18:00:00
PixelPriya2022025-01-05 19:30:00
NovaRiya2042025-02-10 08:00:00
NovaRiya2052025-02-10 09:15:00
ShadowKabir2042025-02-01 23:50:00
ShadowKabir2052025-02-02 00:10:00

Output

gamertagtitle_namedays_to_complete
NovaRiyaHalo Infinite7
NovaRiyaForza Motorsport0
ShadowKabirForza Motorsport1

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_id

Another 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 →