Microsoft-style Company ChallengeHardVerified answerSQLite live

Support Ticket Status by User

For every user who has raised a support ticket, show their total ticket count and how many are still open.

  • Joins
  • Aggregation
  • CASE expressions
  • Sorting

Challenge brief

Understand the request

Customer Health Team is building a customer health dashboard that flags users with unresolved support issues alongside their total ticket history.

Join users to support_tickets and use conditional SUM(CASE WHEN) to split Open vs total tickets.

Return

  • user_id
  • country
  • total_tickets
  • open_tickets

Constraints

  • Return users who have raised at least one ticket
  • Count open tickets conditionally within each user total
  • Resolve equal totals by user ID
  • Show higher total ticket counts first; resolve ties by user ID

Data you will use

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

subscriptions

  • subscription_idINTEGER
  • user_idINTEGER
  • product_idINTEGER
  • start_dateDATE
  • end_dateDATE
  • priceINTEGER

users

  • user_idINTEGER
  • countryTEXT
  • signup_dateDATE

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

This needs user country from users and ticket metrics from support_tickets. JOIN them, GROUP BY user. Use SUM(CASE WHEN status = 'Open' THEN 1 ELSE 0 END) to count only Open tickets per user.

Hint 2

FROM users u JOIN support_tickets st ON u.user_id = st.user_id. GROUP BY u.user_id, u.country. COUNT(st.ticket_id) AS total_tickets. SUM(CASE WHEN st.status = 'Open' THEN 1 ELSE 0 END) AS open_tickets.

Hint 3

Build question 24 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 u.user_id, u.country, COUNT(st.ticket_id) AS total_tickets, SUM(CASE WHEN st.status = 'Open' THEN 1 ELSE 0 END) AS open_tickets FROM users u INNER JOIN support_tickets st ON u.user_id = st.user_id GROUP BY u.user_id, u.country ORDER BY total_tickets DESC, u.user_id;

Why this works

SUM(CASE WHEN) is the conditional counting pattern — it sums 1 for Open rows and 0 for Closed rows within each user's group. Every user has exactly 1 ticket in this dataset. Users 2, 4, 7, 9, 11 have Open tickets.

Success check

12 users — all have 1 total ticket; 5 have 1 open ticket, 7 have 0

Expected result

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

user_idcountrytotal_ticketsopen_tickets
1US10
2IN11
3US10
4UK11
5CA10
6US10
7IN11
8US10
9DE11
10FR10

Previewing 10 of 12 expected rows. Run the query in the editor to inspect the full result.

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.