Apple-style Company ChallengeEasyVerified answerSQLite live

Open Support Tickets

Which support tickets are still open or in progress — who are the customers, what products, and how urgent?

  • Joins
  • CASE expressions
  • Date analysis
  • Filtering
  • Sorting

Challenge brief

Understand the request

AppleCare Operations needs a daily unresolved ticket report to assign specialists before end of day.

List all unresolved support tickets with customer name, product name, issue type, priority, status, and created date.

Return

  • ticket_id
  • customer_name (full name)
  • product_name
  • issue_type
  • priority
  • status
  • created_date

Constraints

  • Exclude resolved tickets
  • Priority precedence is Critical, High, Medium, then Low
  • Within a priority, show older tickets first and resolve 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
  • customer_idINTEGER
  • product_idINTEGER
  • issue_typeVARCHAR(50)
  • priorityVARCHAR(20)
  • statusVARCHAR(20)
  • created_dateDATETIME

customers

  • customer_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)

products

  • product_idINTEGER
  • product_nameVARCHAR(100)

Hints, when you need them

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

Hint 1

Support tickets are in support_tickets. Customer names are in customers, product names in products. Three tables — two JOINs. Filter to exclude Resolved tickets.

Hint 2

INNER JOIN support_tickets to customers on customer_id, and to products on product_id. WHERE t.status != 'Resolved'. ORDER BY t.priority DESC, t.created_date.

Hint 3

Build question 12 from its business grain: identify the driving rows, add only valid relationships, then apply the required filtering, aggregation, and deterministic ordering.

Verified SQL answer

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

Reveal solution and explanation
SELECT t.ticket_id, c.first_name || ' ' || c.last_name AS customer_name, p.product_name, t.issue_type, t.priority, t.status, t.created_date FROM support_tickets t INNER JOIN customers c ON t.customer_id = c.customer_id INNER JOIN products p ON t.product_id = p.product_id WHERE t.status != 'Resolved' ORDER BY CASE t.priority WHEN 'Critical' THEN 1 WHEN 'High' THEN 2 WHEN 'Medium' THEN 3 WHEN 'Low' THEN 4 ELSE 5 END, t.created_date, t.ticket_id;

Why this works

Filter unresolved work, join the customer and product context, and express business priority explicitly rather than relying on alphabetical text order.

Success check

2 open tickets — Carol White (MacBook Pro 16, Hardware, High) and Emma Davis (iPhone 15, Display, High)

Expected result

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

ticket_idcustomer_nameproduct_nameissue_typeprioritystatuscreated_date
3003Carol WhiteMacBook Pro 16HardwareHighIn Progress2024-02-01 09:15:00
3005Emma DavisiPhone 15DisplayHighOpen2024-02-10 11:00:00

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.