Microsoft: Paid but Unassigned Microsoft 365 Seats on 30 June

Medium
784.

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.

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

ColumnType
tenant_idint
tenant_namevarchar

Primary key: tenant_id.

One row per customer tenant.

Table: LicencePurchase

ColumnTypeValues
tenant_idint
skuenumE3, E5, F3, Copilot
seatsint

Primary key (together): tenant_id, sku.

How many seats of each SKU a tenant pays for.

Table: LicenceAssignment

ColumnTypeValues
assignment_idint
tenant_idint
skuenumE3, E5, F3, Copilot
user_upnvarchar
assigned_ondate
removed_ondate

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.

Example

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

Tenant

tenant_idtenant_name
1Contoso Ltd
2Fabrikam Inc
3Northwind Traders

LicencePurchase

tenant_idskuseats
1E35
1E52
2E33
2Copilot2
3F31

LicenceAssignment

assignment_idtenant_idskuuser_upnassigned_onremoved_on
11E3asha@contoso.com2025-01-10NULL
21E3ravi@contoso.com2025-06-30NULL
31E3neha@contoso.com2025-02-012025-06-30
41E3dev@contoso.com2025-07-01NULL
51E5meera@contoso.com2025-03-01NULL
61E5kabir@contoso.com2025-03-022025-08-01
72E3alex@fabrikam.com2025-05-05NULL
83F3john@northwind.com2025-01-01NULL

Output

tenant_nameskuseatsassigned_seatsidle_seats
Contoso LtdE3523
Fabrikam IncCopilot202
Fabrikam IncE3312
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: 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

ColumnType
tenant_idint
tenant_namevarchar

Primary key: tenant_id.

One row per customer tenant.

Table: LicencePurchase

ColumnType
tenant_idint
skuenum(E3, E5, F3, Copilot)
seatsint

Primary key: tenant_id, sku.

How many seats of each SKU a tenant pays for.

Table: LicenceAssignment

ColumnType
assignment_idint
tenant_idint
skuenum(E3, E5, F3, Copilot)
user_upnvarchar
assigned_ondate
removed_ondate

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_idtenant_name
1Contoso Ltd
2Fabrikam Inc
3Northwind Traders

LicencePurchase

tenant_idskuseats
1E35
1E52
2E33
2Copilot2
3F31

LicenceAssignment

assignment_idtenant_idskuuser_upnassigned_onremoved_on
11E3asha@contoso.com2025-01-10NULL
21E3ravi@contoso.com2025-06-30NULL
31E3neha@contoso.com2025-02-012025-06-30
41E3dev@contoso.com2025-07-01NULL
51E5meera@contoso.com2025-03-01NULL
61E5kabir@contoso.com2025-03-022025-08-01
72E3alex@fabrikam.com2025-05-05NULL
83F3john@northwind.com2025-01-01NULL

Output

tenant_nameskuseatsassigned_seatsidle_seats
Contoso LtdE3523
Fabrikam IncCopilot202
Fabrikam IncE3312

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) > 0

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