Microsoft: Licensed Users With No Successful Sign-In

Easy
775.

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.

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

ColumnTypeValues
user_idint
upnvarchar
licence_skuenumE3, 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

ColumnTypeValues
sign_in_idint
user_idint
signed_in_atdatetime
outcomeenumsuccess, failure, mfa_denied

Primary key: sign_in_id.

One row per sign-in attempt; user_id always names a user.

Example

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

M365User

user_idupnlicence_sku
1priya@contoso.comE3
2rahul@contoso.comE5
3zara@contoso.comUnlicensed
4arjun@contoso.comF3
5meera@contoso.comE3
6kabir@contoso.comE5

SignInLog

sign_in_iduser_idsigned_in_atoutcome
112025-05-02 09:10:00success
222025-05-02 09:12:00failure
322025-05-03 10:00:00mfa_denied
442025-05-03 08:00:00success
532025-05-04 11:00:00failure
662025-05-05 12:00:00failure
762025-05-05 12:02:00success

Output

user_idupn
2rahul@contoso.com
5meera@contoso.com
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: 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

ColumnType
user_idint
upnvarchar
licence_skuenum(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

ColumnType
sign_in_idint
user_idint
signed_in_atdatetime
outcomeenum(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_idupnlicence_sku
1priya@contoso.comE3
2rahul@contoso.comE5
3zara@contoso.comUnlicensed
4arjun@contoso.comF3
5meera@contoso.comE3
6kabir@contoso.comE5

SignInLog

sign_in_iduser_idsigned_in_atoutcome
112025-05-02 09:10:00success
222025-05-02 09:12:00failure
322025-05-03 10:00:00mfa_denied
442025-05-03 08:00:00success
532025-05-04 11:00:00failure
662025-05-05 12:00:00failure
762025-05-05 12:02:00success

Output

user_idupn
2rahul@contoso.com
5meera@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 NULL

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