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
| Column | Type |
|---|---|
| mailbox_id | int |
| owner_upn | varchar |
| mailbox_type | enum(user, shared, room) |
| quota_mb | int |
| used_mb | int |
Primary key: mailbox_id.
One row per mailbox; owner_upn is unique. Sizes are in megabytes.
Examples
Example 1
Mailbox
| mailbox_id | owner_upn | mailbox_type | quota_mb | used_mb |
|---|---|---|---|---|
| 1 | priya@contoso.com | user | 51200 | 46080 |
| 2 | rahul@contoso.com | user | 51200 | 46079 |
| 3 | helpdesk@contoso.com | shared | 51200 | 50900 |
| 4 | boardroom@contoso.com | room | 10240 | 10000 |
| 5 | zara@contoso.com | user | 102400 | 103000 |
| 6 | kabir@contoso.com | user | 102400 | 20000 |
Output
| owner_upn | free_mb |
|---|---|
| zara@contoso.com | -600 |
| helpdesk@contoso.com | 300 |
| priya@contoso.com | 5120 |
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_upnAnother 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 →