Microsoft: Products With Three or More Severity A Cases in March

Easy
779.

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.

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

ColumnTypeValues
case_idint
productvarchar
severityenumA, B, C
opened_atdatetime
statusenumopen, resolved, closed_duplicate

Primary key: case_id.

One row per support case. Severity A is the most critical level.

Example

The rows must come back in the order shown.

SupportCase

case_idproductseverityopened_atstatus
1Azure Kubernetes ServiceA2025-03-01 00:00:00resolved
2Azure Kubernetes ServiceA2025-03-10 12:00:00open
3Azure Kubernetes ServiceA2025-03-31 23:59:59resolved
4Azure Kubernetes ServiceA2025-04-01 00:00:00open
5Exchange OnlineA2025-03-05 09:00:00resolved
6Exchange OnlineA2025-03-06 09:00:00closed_duplicate
7Exchange OnlineA2025-03-07 09:00:00open
8Exchange OnlineB2025-03-08 09:00:00open
9Exchange OnlineA2025-02-28 23:59:59open
10Microsoft TeamsA2025-03-12 10:00:00resolved
2 more rows

Output

productsev_a_cases
Azure Kubernetes Service3
Microsoft Teams3
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: 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

ColumnType
case_idint
productvarchar
severityenum(A, B, C)
opened_atdatetime
statusenum(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_idproductseverityopened_atstatus
1Azure Kubernetes ServiceA2025-03-01 00:00:00resolved
2Azure Kubernetes ServiceA2025-03-10 12:00:00open
3Azure Kubernetes ServiceA2025-03-31 23:59:59resolved
4Azure Kubernetes ServiceA2025-04-01 00:00:00open
5Exchange OnlineA2025-03-05 09:00:00resolved
6Exchange OnlineA2025-03-06 09:00:00closed_duplicate
7Exchange OnlineA2025-03-07 09:00:00open
8Exchange OnlineB2025-03-08 09:00:00open
9Exchange OnlineA2025-02-28 23:59:59open
10Microsoft TeamsA2025-03-12 10:00:00resolved
11Microsoft TeamsA2025-03-15 14:30:00resolved
12Microsoft TeamsA2025-03-20 08:15:00open

Output

productsev_a_cases
Azure Kubernetes Service3
Microsoft Teams3

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, product

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