Microsoft: Outlook Mailboxes at 90% of Their Quota or More

Easy
777.

Microsoft: Outlook Mailboxes at 90% of Their Quota or More

An Exchange Online admin sends a warning to every mailbox that is about to stop receiving mail because it is nearly full.

Return each user or shared mailbox (room mailboxes are managed separately) whose used_mb is at least 90% of its quota_mb. Return owner_upn and free_mb (quota minus used; negative when the mailbox is already over quota), ordered by free_mb ascending, then owner_upn ascending.

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: Mailbox

ColumnTypeValues
mailbox_idint
owner_upnvarchar
mailbox_typeenumuser, shared, room
quota_mbint
used_mbint

Primary key: mailbox_id.

One row per mailbox; owner_upn is unique. Sizes are in megabytes.

Example

The rows must come back in the order shown.

Mailbox

mailbox_idowner_upnmailbox_typequota_mbused_mb
1priya@contoso.comuser5120046080
2rahul@contoso.comuser5120046079
3helpdesk@contoso.comshared5120050900
4boardroom@contoso.comroom1024010000
5zara@contoso.comuser102400103000
6kabir@contoso.comuser10240020000

Output

owner_upnfree_mb
zara@contoso.com-600
helpdesk@contoso.com300
priya@contoso.com5120
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: Outlook Mailboxes at 90% of Their Quota or More

An Exchange Online admin sends a warning to every mailbox that is about to stop receiving mail because it is nearly full.

  • Difficulty: Easy
  • Topics: Basics, Conditional Logic
  • Dialect: MySQL
  • Problem: #777

Problem statement

An Exchange Online admin sends a warning to every mailbox that is about to stop receiving mail because it is nearly full.

Return each user or shared mailbox (room mailboxes are managed separately) whose used_mb is at least 90% of its quota_mb. Return owner_upn and free_mb (quota minus used; negative when the mailbox is already over quota), ordered by free_mb ascending, then owner_upn ascending.

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: Mailbox

ColumnType
mailbox_idint
owner_upnvarchar
mailbox_typeenum(user, shared, room)
quota_mbint
used_mbint

Primary key: mailbox_id.

One row per mailbox; owner_upn is unique. Sizes are in megabytes.

Examples

Example 1

Mailbox

mailbox_idowner_upnmailbox_typequota_mbused_mb
1priya@contoso.comuser5120046080
2rahul@contoso.comuser5120046079
3helpdesk@contoso.comshared5120050900
4boardroom@contoso.comroom1024010000
5zara@contoso.comuser102400103000
6kabir@contoso.comuser10240020000

Output

owner_upnfree_mb
zara@contoso.com-600
helpdesk@contoso.com300
priya@contoso.com5120

How to solve Microsoft: Outlook Mailboxes at 90% of Their Quota or More

Two conditions pick the mailboxes: the type is user or shared, and usage is at least nine tenths of the quota. Writing the second as used_mb * 10 >= quota_mb * 9 keeps everything in integers, so a mailbox at exactly 90% (46,080 of 51,200 MB) is kept and one a single megabyte below is not — a comparison through a floating-point ratio can wobble on that boundary.

free_mb is quota_mb - used_mb. Exchange lets a mailbox pass its quota briefly, so the value can be negative, and those mailboxes sort first under the ascending order — exactly the ones the admin should call first. Ties on free space are broken by the unique UPN.

The alternative restates the threshold as "free space is at most a tenth of the quota", which is the same inequality rearranged; every quota is a multiple of ten so the division is exact. One scan of the table.

Reference solution (MySQL)

SELECT owner_upn, quota_mb - used_mb AS free_mb
FROM Mailbox
WHERE mailbox_type IN ('user', 'shared')
  AND used_mb * 10 >= quota_mb * 9
ORDER BY free_mb, owner_upn

Another way

SELECT owner_upn, quota_mb - used_mb AS free_mb FROM Mailbox WHERE mailbox_type <> 'room' AND quota_mb - used_mb <= quota_mb / 10 ORDER BY quota_mb - used_mb ASC, owner_upn ASC

← Microsoft: Gamerscore Awarded per Xbox Title · Microsoft: Azure Resource Count per Resource Group From Resource IDs →