Microsoft: Products With Three or More Severity A Cases in March
A Microsoft support operations manager is preparing a monthly review of the products generating the most critical (Severity A) support cases.
- Difficulty: Easy
- Topics: Aggregation, Dates
- Dialect: MySQL
- Problem: #779
Problem statement
A Microsoft support operations manager is preparing a monthly review of the products generating the most critical (Severity A) support cases.
Count the Severity A cases opened in March 2025 for each product, ignoring cases closed as duplicates (closed_duplicate). Return product and sev_a_cases for products with at least 3 such cases, ordered by sev_a_cases descending, then product 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: SupportCase
| Column | Type |
|---|---|
| case_id | int |
| product | varchar |
| severity | enum(A, B, C) |
| opened_at | datetime |
| status | enum(open, resolved, closed_duplicate) |
Primary key: case_id.
One row per support case. Severity A is the most critical level.
Examples
Example 1
SupportCase
| case_id | product | severity | opened_at | status |
|---|---|---|---|---|
| 1 | Azure Kubernetes Service | A | 2025-03-01 00:00:00 | resolved |
| 2 | Azure Kubernetes Service | A | 2025-03-10 12:00:00 | open |
| 3 | Azure Kubernetes Service | A | 2025-03-31 23:59:59 | resolved |
| 4 | Azure Kubernetes Service | A | 2025-04-01 00:00:00 | open |
| 5 | Exchange Online | A | 2025-03-05 09:00:00 | resolved |
| 6 | Exchange Online | A | 2025-03-06 09:00:00 | closed_duplicate |
| 7 | Exchange Online | A | 2025-03-07 09:00:00 | open |
| 8 | Exchange Online | B | 2025-03-08 09:00:00 | open |
| 9 | Exchange Online | A | 2025-02-28 23:59:59 | open |
| 10 | Microsoft Teams | A | 2025-03-12 10:00:00 | resolved |
| 11 | Microsoft Teams | A | 2025-03-15 14:30:00 | resolved |
| 12 | Microsoft Teams | A | 2025-03-20 08:15:00 | open |
Output
| product | sev_a_cases |
|---|---|
| Azure Kubernetes Service | 3 |
| Microsoft Teams | 3 |
How to solve Microsoft: Products With Three or More Severity A Cases in March
Row filters come first: Severity A, not a duplicate, opened in March 2025. For the month the safest test is the half-open range opened_at >= '2025-03-01' AND opened_at < '2025-04-01' — it keeps a case opened at 23:59:59 on 31 March and drops one opened at midnight on 1 April, with no reasoning about times. BETWEEN '2025-03-01' AND '2025-03-31' would quietly lose most of the 31st, since a timestamp later that day is greater than the bare date.
Then GROUP BY product and HAVING COUNT(*) >= 3: the threshold is about the group, so it cannot go in WHERE. The order is fully specified, with the product name breaking ties between equal counts.
The alternative counts conditionally over all rows with SUM(CASE …) and YEAR/MONTH. It gives the same answer, but it cannot use an index on opened_at, so on a large case table the range filter is cheaper.
Reference solution (MySQL)
SELECT product, COUNT(*) AS sev_a_cases
FROM SupportCase
WHERE severity = 'A'
AND status <> 'closed_duplicate'
AND opened_at >= '2025-03-01' AND opened_at < '2025-04-01'
GROUP BY product
HAVING COUNT(*) >= 3
ORDER BY sev_a_cases DESC, productAnother way
SELECT product, SUM(CASE WHEN severity = 'A' AND status <> 'closed_duplicate' AND YEAR(opened_at) = 2025 AND MONTH(opened_at) = 3 THEN 1 ELSE 0 END) AS sev_a_cases
FROM SupportCase
GROUP BY product
HAVING sev_a_cases >= 3
ORDER BY sev_a_cases DESC, product ASC← Microsoft: Azure Resource Count per Resource Group From Resource IDs · Microsoft: Azure Subscription Budget Status This Month →