Write a query to find the average time between a support ticket being opened and first responded to, grouped by priority.

Answer

Compute the time difference between each ticket's creation and first response, then average that difference grouped by priority.

Solution

SELECT priority,
       AVG(TIMESTAMPDIFF(MINUTE, created_at, first_response_at)) AS avg_response_minutes
FROM Tickets
WHERE first_response_at IS NOT NULL
GROUP BY priority;

Step by Step

1. TIMESTAMPDIFF(MINUTE, created_at, first_response_at) computes, for each ticket, exactly how many minutes elapsed between creation and the first response.
2. WHERE first_response_at IS NOT NULL excludes tickets that haven't received a response yet - including them would either error or (if NULL) be silently ignored by AVG(), but excluding explicitly makes the intent clear.
3. GROUP BY priority then averages that per-ticket response time within each priority level.

Alternative: Reporting Median Instead of Mean

SELECT priority,
       PERCENTILE_CONT(0.5) WITHIN GROUP (
         ORDER BY TIMESTAMPDIFF(MINUTE, created_at, first_response_at)
       ) AS median_response_minutes
FROM Tickets
WHERE first_response_at IS NOT NULL
GROUP BY priority;

Response time distributions are typically right-skewed - most tickets get a quick response, but a small number take extremely long, dragging the average upward. Reporting the median alongside (or instead of) the mean gives a more representative sense of "typical" response time, which is worth raising proactively.

Key Exam Traps

• Unresolved/unresponded tickets must be excluded, not treated as instant or infinite - forgetting the IS NOT NULL filter (or worse, coalescing a missing response time to 0) would badly distort the average.
• Mean vs median for skewed data is a genuine analytics judgment call worth raising unprompted - a single extreme outlier ticket can meaningfully shift a priority level's average response time.
• Business hours vs wall-clock time: A raw TIMESTAMPDIFF counts weekends and overnight hours as elapsed time - if the support team only operates business hours, "average response time" measured in raw minutes can look artificially worse than the team's actual working-hours performance, a distinction worth flagging to the interviewer.