Microsoft-style Company ChallengeHardVerified answerSQLite live

Most Problematic Product

Which product has the most open (unresolved) support tickets?

  • Aggregation
  • Filtering
  • Sorting
  • Top-N

Challenge brief

Understand the request

Engineering Reliability is triaging the product with the worst unresolved support backlog to assign an emergency fix team.

Find the single product with the highest count of 'Open' tickets using GROUP BY + LIMIT 1.

Return

  • product_id
  • open_tickets

Constraints

  • Count tickets whose status is 'Open'
  • Return exactly one product and use product ID to resolve equal counts

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

support_tickets

  • ticket_idINTEGER
  • user_idINTEGER
  • product_idINTEGER
  • created_dateDATE
  • statusTEXT

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Filter to Open tickets, GROUP BY product_id, COUNT, ORDER BY desc, LIMIT 1 to get the single worst product.

Hint 2

SELECT product_id, COUNT(*) AS open_tickets FROM support_tickets WHERE status = 'Open' GROUP BY product_id ORDER BY open_tickets DESC LIMIT 1.

Hint 3

Build question 23 from the required result grain: choose the driving table, add only the joins and filters needed for that grain, then apply aggregation and deterministic ordering.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT product_id, COUNT(*) AS open_tickets FROM support_tickets WHERE status = 'Open' GROUP BY product_id ORDER BY open_tickets DESC, product_id ASC LIMIT 1;

Why this works

5 tickets are Open: tickets 2 (Office), 4 (Teams), 7 (Teams), 9 (Office), 11 (Teams). Teams (product_id=3) has 3 open tickets — the most. Office has 2 open. Azure has 0 open tickets.

Success check

1 row — product_id: 3 (Teams) with 3 open tickets

Expected result

Use this output to verify values, aliases, ordering, and row count.

product_idopen_tickets
33

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.