Microsoft-style Company ChallengeEasyVerified answerSQLite live

Open Support Tickets

Which support tickets are currently open and waiting for resolution?

  • Date analysis
  • Filtering
  • Sorting

Challenge brief

Understand the request

Support Operations needs a live queue of all unresolved tickets to assign to available support agents at the start of each shift.

List all open tickets showing ticket_id, user_id, product_id, and created_date.

Return

  • ticket_id
  • user_id
  • product_id
  • created_date

Constraints

  • Return tickets whose status is 'Open'
  • Show the oldest tickets first and resolve date ties by ticket ID

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

All ticket data is in support_tickets. Filter to Open status. ORDER BY created_date so the oldest unresolved tickets appear first — the support queue priority order.

Hint 2

SELECT ticket_id, user_id, product_id, created_date FROM support_tickets WHERE status = 'Open' ORDER BY created_date.

Hint 3

Build question 28 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 ticket_id, user_id, product_id, created_date FROM support_tickets WHERE status = 'Open' ORDER BY created_date, ticket_id;

Why this works

5 of 12 tickets are Open. Teams (product_id=3) has 3 of the 5 open tickets — consistent with Q23 which identified Teams as the most problematic product. Ordering by created_date shows the oldest unresolved tickets first.

Success check

5 open tickets — oldest is ticket 2 (user 2, Office) from Mar 2; newest is ticket 11 (user 11, Teams) from Mar 20

Expected result

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

ticket_iduser_idproduct_idcreated_date
2212024-03-02
4432024-03-04
7732024-03-10
9912024-03-15
111132024-03-20

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.