Microsoft-style Company ChallengeMediumVerified answerSQLite live

Tickets by Product

How many support tickets have been raised for each product?

  • Joins
  • Aggregation
  • Sorting

Challenge brief

Understand the request

Support Operations needs to understand which products generate the most support tickets to prioritise staffing and documentation improvements.

Count support tickets per product_id from support_tickets.

Return

  • product_id
  • tickets

Constraints

  • Include every catalog product, even when it has no support tickets
  • Treat a missing ticket count as zero
  • Show the largest ticket counts first

Data you will use

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

products

  • product_idINTEGER
  • product_nameTEXT

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

All ticket data is in support_tickets. Single-table aggregation: GROUP BY product_id and COUNT rows per product.

Hint 2

SELECT product_id, COUNT(*) AS tickets FROM support_tickets GROUP BY product_id.

Hint 3

Build question 15 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 p.product_id, COUNT(st.ticket_id) AS tickets FROM products p LEFT JOIN support_tickets st ON p.product_id = st.product_id GROUP BY p.product_id ORDER BY tickets DESC, p.product_id;

Why this works

Drive from products and count the nullable ticket identifier, not rows, so the ticket-free Dynamics product remains with a zero count.

Success check

3 products — Office (product_id=1) leads with 5 tickets, Azure (4), Teams (3)

Expected result

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

product_idtickets
15
24
33
40

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.