Microsoft: Licensed Users With No Successful Sign-In
A Microsoft 365 licensing analyst is helping a customer reclaim paid licences from people who never actually got into their account.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #775
Problem statement
A Microsoft 365 licensing analyst is helping a customer reclaim paid licences from people who never actually got into their account.
Return every user who holds a licence (any licence_sku other than Unlicensed) and has no successful sign-in in the log. Failed and MFA-denied attempts do not count as signing in. Return user_id and upn, 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: M365User
| Column | Type |
|---|---|
| user_id | int |
| upn | varchar |
| licence_sku | enum(E3, E5, F3, Unlicensed) |
Primary key: user_id.
One row per user in the tenant. upn is the user principal name they sign in with.
Table: SignInLog
| Column | Type |
|---|---|
| sign_in_id | int |
| user_id | int |
| signed_in_at | datetime |
| outcome | enum(success, failure, mfa_denied) |
Primary key: sign_in_id.
One row per sign-in attempt; user_id always names a user.
Examples
Example 1
M365User
| user_id | upn | licence_sku |
|---|---|---|
| 1 | priya@contoso.com | E3 |
| 2 | rahul@contoso.com | E5 |
| 3 | zara@contoso.com | Unlicensed |
| 4 | arjun@contoso.com | F3 |
| 5 | meera@contoso.com | E3 |
| 6 | kabir@contoso.com | E5 |
SignInLog
| sign_in_id | user_id | signed_in_at | outcome |
|---|---|---|---|
| 1 | 1 | 2025-05-02 09:10:00 | success |
| 2 | 2 | 2025-05-02 09:12:00 | failure |
| 3 | 2 | 2025-05-03 10:00:00 | mfa_denied |
| 4 | 4 | 2025-05-03 08:00:00 | success |
| 5 | 3 | 2025-05-04 11:00:00 | failure |
| 6 | 6 | 2025-05-05 12:00:00 | failure |
| 7 | 6 | 2025-05-05 12:02:00 | success |
Output
| user_id | upn |
|---|---|
| 2 | rahul@contoso.com |
| 5 | meera@contoso.com |
How to solve Microsoft: Licensed Users With No Successful Sign-In
The question is about the absence of a row: a licensed user for whom no successful sign-in exists. NOT EXISTS says that directly — for each user, look for a log row with their id and outcome = 'success', and keep the user if none is found. A user with only failures or MFA denials still passes, and so does a user with no log rows at all.
The LEFT JOIN version is the classic trap. The condition s.outcome = 'success' must sit in the ON clause, so unsuccessful rows simply fail to match and the user gets a NULL-extended row; putting it in WHERE would throw away the users with failures only. Then s.sign_in_id IS NULL keeps the unmatched users.
NOT IN works too here because SignInLog.user_id is never NULL. All three read each user once and probe the log by user_id, so an index on (user_id, outcome) makes them cheap.
Reference solution (MySQL)
SELECT u.user_id, u.upn
FROM M365User u
WHERE u.licence_sku <> 'Unlicensed'
AND NOT EXISTS (
SELECT 1 FROM SignInLog s
WHERE s.user_id = u.user_id AND s.outcome = 'success'
)Another way
SELECT u.user_id, u.upn
FROM M365User u
LEFT JOIN SignInLog s ON s.user_id = u.user_id AND s.outcome = 'success'
WHERE u.licence_sku <> 'Unlicensed' AND s.sign_in_id IS NULLAnother way
SELECT user_id, upn FROM M365User WHERE licence_sku IN ('E3', 'E5', 'F3') AND user_id NOT IN (SELECT user_id FROM SignInLog WHERE outcome = 'success')← Microsoft: Teams Meetings That Ran Past Their Booked Slot · Microsoft: Gamerscore Awarded per Xbox Title →