Microsoft: Paid but Unassigned Microsoft 365 Seats on 30 June
A Microsoft customer-success manager is preparing for each customer's renewal and wants to show them the licences they paid for but are not using.
- Difficulty: Medium
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #784
Problem statement
A Microsoft customer-success manager is preparing for each customer's renewal and wants to show them the licences they paid for but are not using.
A licence assignment is active on 2025-06-30 when it was assigned on or before that day and either has not been removed or was removed after that day (removed on the 30th means it was gone that day). For every purchased (tenant, SKU) with at least one idle seat on that date, return tenant_name, sku, seats, assigned_seats (active assignments) and idle_seats (seats minus assigned_seats). A SKU with no assignments at all has every seat idle. 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: Tenant
| Column | Type |
|---|---|
| tenant_id | int |
| tenant_name | varchar |
Primary key: tenant_id.
One row per customer tenant.
Table: LicencePurchase
| Column | Type |
|---|---|
| tenant_id | int |
| sku | enum(E3, E5, F3, Copilot) |
| seats | int |
Primary key: tenant_id, sku.
How many seats of each SKU a tenant pays for.
Table: LicenceAssignment
| Column | Type |
|---|---|
| assignment_id | int |
| tenant_id | int |
| sku | enum(E3, E5, F3, Copilot) |
| user_upn | varchar |
| assigned_on | date |
| removed_on | date |
Primary key: assignment_id.
One row per time a licence was given to a user; removed_on is NULL while it is still assigned and always after assigned_on otherwise.
Examples
Example 1
Tenant
| tenant_id | tenant_name |
|---|---|
| 1 | Contoso Ltd |
| 2 | Fabrikam Inc |
| 3 | Northwind Traders |
LicencePurchase
| tenant_id | sku | seats |
|---|---|---|
| 1 | E3 | 5 |
| 1 | E5 | 2 |
| 2 | E3 | 3 |
| 2 | Copilot | 2 |
| 3 | F3 | 1 |
LicenceAssignment
| assignment_id | tenant_id | sku | user_upn | assigned_on | removed_on |
|---|---|---|---|---|---|
| 1 | 1 | E3 | asha@contoso.com | 2025-01-10 | NULL |
| 2 | 1 | E3 | ravi@contoso.com | 2025-06-30 | NULL |
| 3 | 1 | E3 | neha@contoso.com | 2025-02-01 | 2025-06-30 |
| 4 | 1 | E3 | dev@contoso.com | 2025-07-01 | NULL |
| 5 | 1 | E5 | meera@contoso.com | 2025-03-01 | NULL |
| 6 | 1 | E5 | kabir@contoso.com | 2025-03-02 | 2025-08-01 |
| 7 | 2 | E3 | alex@fabrikam.com | 2025-05-05 | NULL |
| 8 | 3 | F3 | john@northwind.com | 2025-01-01 | NULL |
Output
| tenant_name | sku | seats | assigned_seats | idle_seats |
|---|---|---|---|---|
| Contoso Ltd | E3 | 5 | 2 | 3 |
| Fabrikam Inc | Copilot | 2 | 0 | 2 |
| Fabrikam Inc | E3 | 3 | 1 | 2 |
How to solve Microsoft: Paid but Unassigned Microsoft 365 Seats on 30 June
The core is a point-in-time test on an interval: an assignment covers 30 June when assigned_on <= '2025-06-30' and removed_on is either NULL (still held) or after the 30th. The boundaries are where people slip: a licence assigned on the 30th counts, one removed on the 30th does not.
Every purchase has to be judged, including SKUs nobody was given, so the query starts from LicencePurchase and LEFT JOINs the assignments with the whole activity test inside ON. Unmatched purchases keep one NULL row, and COUNT(a.assignment_id) counts it as 0, leaving every seat idle. Then HAVING seats - assigned > 0 keeps only SKUs with money on the table; an over-assigned SKU (negative idle) is dropped too.
The alternative counts with a correlated subquery per purchase, expressing activity as '2025-06-30' BETWEEN assigned_on AND removed_on - 1 day with a far-future stand-in for NULL. Both probe assignments by (tenant, sku).
Reference solution (MySQL)
SELECT t.tenant_name, p.sku, p.seats,
COUNT(a.assignment_id) AS assigned_seats,
p.seats - COUNT(a.assignment_id) AS idle_seats
FROM LicencePurchase p
JOIN Tenant t ON t.tenant_id = p.tenant_id
LEFT JOIN LicenceAssignment a
ON a.tenant_id = p.tenant_id AND a.sku = p.sku
AND a.assigned_on <= '2025-06-30'
AND (a.removed_on IS NULL OR a.removed_on > '2025-06-30')
GROUP BY t.tenant_id, t.tenant_name, p.sku, p.seats
HAVING p.seats - COUNT(a.assignment_id) > 0Another way
SELECT tenant_name, sku, seats, assigned_seats, seats - assigned_seats AS idle_seats
FROM (
SELECT t.tenant_name, p.sku, p.seats,
(SELECT COUNT(*) FROM LicenceAssignment a
WHERE a.tenant_id = p.tenant_id AND a.sku = p.sku
AND '2025-06-30' BETWEEN a.assigned_on AND COALESCE(DATE_SUB(a.removed_on, INTERVAL 1 DAY), '2099-12-31')) AS assigned_seats
FROM LicencePurchase p JOIN Tenant t ON t.tenant_id = p.tenant_id
) x
WHERE seats > assigned_seats← Microsoft: Xbox Players Who Unlocked Every Achievement in a Title · Microsoft: Days Between Windows Insider Flights per Channel →