case studies · real world impact

Real-World SQL Case Studies

Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.

real world impact

Real-World SQL Case Studies

Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.

Browse Case Studies Interview questions

Full schema + dataset
Reference solutions

// SELECT A CASE STUDY

SQL case study · Zendesk

Customer Support SLA Compliance & Response Time Analysis

🏢 ZendeskIntermediate

Track support ticket response times, identify SLA violations, and measure team compliance with service-level agreements.

Business Problem

Zendesk's support operations lead is facing a spike in complaints about slow responses — and anecdotes won't fix it. Leadership needs ticket-level proof of exactly where the team stands against its SLAs (service-level agreements: contractual response and resolution deadlines that differ by priority).

For every ticket the analysis must compute the actual response time (created → first response) and resolution time (created → resolved) in hours, compare each against that priority's threshold — Urgent gets 1h / 4h, High 2h / 8h, Medium 4h / 24h, Low 8h / 48h — and flag breaches (actual minus allowed, positive means missed) plus a violation type: response-only, resolution-only, both, or clean.

The real question isn't the breach count — it's the pattern. Is the pain concentrated in high-priority tickets, in the Technical category, or everywhere at once? That pattern decides whether the fix is staffing, triage, or training.

Dataset & Schema

Support tickets with ticket_id, category, priority, created_at, first_response_at, resolved_at; SLA config with priority, response_sla_hours, resolution_sla_hours.

CREATE TABLE tickets (
  ticket_id INTEGER PRIMARY KEY,
  ticket_category TEXT,
  priority TEXT,
  created_at DATETIME,
  first_response_at DATETIME,
  resolved_at DATETIME
);
CREATE TABLE sla_config (
  priority TEXT PRIMARY KEY,
  response_sla_hours INTEGER,
  resolution_sla_hours INTEGER
);

Try it yourself

Your target

Track support ticket response times, identify SLA violations, and measure team compliance with service-level agreements.

Loading the expected output…

Starting the in-browser database…

SQL Solution

WITH ticket_sla_metrics AS (
  SELECT 
    t.ticket_id,
    t.ticket_category,
    t.priority,
    t.created_at,
    t.first_response_at,
    t.resolved_at,
    s.response_sla_hours,
    s.resolution_sla_hours,
    ROUND((julianday(t.first_response_at) - julianday(t.created_at)) * 24, 2) as response_time_hours,
    ROUND((julianday(t.resolved_at) - julianday(t.created_at)) * 24, 2) as resolution_time_hours
  FROM tickets t
  INNER JOIN sla_config s ON t.priority = s.priority
),
sla_compliance AS (
  SELECT 
    ticket_id,
    ticket_category,
    priority,
    response_time_hours,
    resolution_time_hours,
    response_sla_hours,
    resolution_sla_hours,
    CASE 
      WHEN response_time_hours <= response_sla_hours THEN 'Met'
      ELSE 'Violated'
    END as response_sla_status,
    CASE 
      WHEN resolution_time_hours <= resolution_sla_hours THEN 'Met'
      ELSE 'Violated'
    END as resolution_sla_status,
    ROUND(response_time_hours - response_sla_hours, 2) as response_breach_hours,
    ROUND(resolution_time_hours - resolution_sla_hours, 2) as resolution_breach_hours
  FROM ticket_sla_metrics
)
SELECT 
  ticket_id,
  ticket_category,
  priority,
  response_time_hours,
  resolution_time_hours,
  response_sla_hours,
  resolution_sla_hours,
  response_sla_status,
  resolution_sla_status,
  CASE 
    WHEN response_sla_status = 'Violated' OR resolution_sla_status = 'Violated' THEN 'YES'
    ELSE 'NO'
  END as sla_violation,
  response_breach_hours,
  resolution_breach_hours,
  CASE 
    WHEN response_sla_status = 'Violated' AND resolution_sla_status = 'Violated' THEN 'Both-SLAs-Missed'
    WHEN response_sla_status = 'Violated' THEN 'Response-SLA-Missed'
    WHEN resolution_sla_status = 'Violated' THEN 'Resolution-SLA-Missed'
    ELSE 'Compliant'
  END as violation_type
FROM sla_compliance
ORDER BY sla_violation DESC, response_breach_hours DESC;

Explanation

Step-by-step Solution:

Create ticket_sla_metrics CTE by joining tickets with sla_config based on priority. Calculate response_time_hours (julianday difference between first_response_at and created_at, multiplied by 24). Similarly, calculate resolution_time_hours from resolved_at.

In sla_compliance CTE, compare actual times against SLA thresholds: response_time_hours <= response_sla_hours = 'Met', else 'Violated'. Do the same for resolution. Calculate breach_hours (actual minus SLA) for insight into how much time was exceeded.

Flag overall sla_violation as 'YES' if either SLA was missed. Categorize violation_type: Both-SLAs-Missed (worst), Response-SLA-Missed, Resolution-SLA-Missed, or Compliant.

Order by sla_violation DESC to show violations first, then by response_breach_hours DESC to highlight worst offenders. This enables support managers to focus remediation on worst cases and track team compliance trends.

Expected result

Computing the reference output…

So what? — Business Insight

6 of 8 tickets breach SLA (75%) and the pattern is clear: resolution capacity on Technical tickets, not first response — 5 of the 6 violations involve missed resolution, with tickets 4 and 7 missing both SLAs by 15+ hours. Staff resolution bandwidth for the Technical queue before touching response-time targets.

Interview lens — on this exact case

What the interviewer asks next

“Tickets 4 and 7 missed both SLAs by 15+ hours — is that a staffing problem or a triage problem, and how would you tell from data?”

Strong answer covers: Diagnosis before prescription: compare breach patterns by category, priority, and time-of-day; consistent Technical/High misses point at capacity or expertise, scattered misses point at process.

“Five of six violations involve resolution, only three involve first response — where does the next hire (or dollar) go?”

Strong answer covers: Reading the asymmetry: response is largely healthy, so invest in resolution bandwidth for the Technical queue rather than spreading effort evenly.

“What single metric would you put on a weekly dashboard for leadership?”

Strong answer covers: Metric design: resolution-SLA compliance % by category (it captures the dominant failure), with violation-type mix as the drill-down — and defending why one number beats a wall of them.

“Ticket 6 (Low) took 43 hours to resolve yet counts as Met against a 48h SLA — is that a success worth reporting?”

Strong answer covers: SLA compliance vs customer experience: technically met, experientially a 2-day wait — argue for pairing compliance % with absolute wait-time percentiles so easy thresholds can't hide pain.

“Both Account tickets (5 and 8) missed resolution SLA — coincidence, or a category-level training gap?”

Strong answer covers: Pattern-hunting across small samples: two misses in one category warrant a targeted audit (escalation paths, product knowledge) while admitting n=2 needs confirmation.

“Negative breach hours (-5 on ticket 6) mean beating SLA — how do you present over-performance without perverse incentives?”

Strong answer covers: Celebrating slack carefully: beating a loose Low-priority SLA by hours may signal over-servicing; reframe as capacity that could be reallocated to the breaching Technical queue.

Interview lens — how to tackle any SQL case study

Questions test whether your SQL runs. Cases test whether you can own a decision. Learn this once, use it everywhere below.

Restate the grain

One output row = one what? Write it down first — every GROUP BY and join must serve that grain.

Explore before you aggregate

Eyeball every table first: NULL join keys, orphan keys, date coverage. Ten minutes here saves an hour of debugging.

Hand-trace one entity end to end

Compute one row's answer by hand, then demand your query agree. Trust the trace, not the query.

Build incrementally, validate each CTE

Run each CTE standalone and reconcile counts before stacking the next one.

Pressure-test boundaries and empties

Probe cutoffs, zero-activity entities, ties, and empty results — interviewers live on boundaries.

Recommend, don't just report

End with a decision, an owner, and what would change your mind. No recommendation, no case study.

Loading…

case studies · real world impact

Real-World SQL Case Studies

Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.

real world impact

Real-World SQL Case Studies

Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.

Browse Case Studies Interview questions

Full schema + dataset
Reference solutions

// SELECT A CASE STUDY

SQL case study · Zendesk

Customer Support SLA Compliance & Response Time Analysis

🏢 ZendeskIntermediate

Track support ticket response times, identify SLA violations, and measure team compliance with service-level agreements.

Business Problem

Dataset & Schema

Support tickets with ticket_id, category, priority, created_at, first_response_at, resolved_at; SLA config with priority, response_sla_hours, resolution_sla_hours.

CREATE TABLE tickets (
  ticket_id INTEGER PRIMARY KEY,
  ticket_category TEXT,
  priority TEXT,
  created_at DATETIME,
  first_response_at DATETIME,
  resolved_at DATETIME
);
CREATE TABLE sla_config (
  priority TEXT PRIMARY KEY,
  response_sla_hours INTEGER,
  resolution_sla_hours INTEGER
);

Try it yourself

Your target

Track support ticket response times, identify SLA violations, and measure team compliance with service-level agreements.

Loading the expected output…

Starting the in-browser database…

SQL Solution

WITH ticket_sla_metrics AS (
  SELECT 
    t.ticket_id,
    t.ticket_category,
    t.priority,
    t.created_at,
    t.first_response_at,
    t.resolved_at,
    s.response_sla_hours,
    s.resolution_sla_hours,
    ROUND((julianday(t.first_response_at) - julianday(t.created_at)) * 24, 2) as response_time_hours,
    ROUND((julianday(t.resolved_at) - julianday(t.created_at)) * 24, 2) as resolution_time_hours
  FROM tickets t
  INNER JOIN sla_config s ON t.priority = s.priority
),
sla_compliance AS (
  SELECT 
    ticket_id,
    ticket_category,
    priority,
    response_time_hours,
    resolution_time_hours,
    response_sla_hours,
    resolution_sla_hours,
    CASE 
      WHEN response_time_hours <= response_sla_hours THEN 'Met'
      ELSE 'Violated'
    END as response_sla_status,
    CASE 
      WHEN resolution_time_hours <= resolution_sla_hours THEN 'Met'
      ELSE 'Violated'
    END as resolution_sla_status,
    ROUND(response_time_hours - response_sla_hours, 2) as response_breach_hours,
    ROUND(resolution_time_hours - resolution_sla_hours, 2) as resolution_breach_hours
  FROM ticket_sla_metrics
)
SELECT 
  ticket_id,
  ticket_category,
  priority,
  response_time_hours,
  resolution_time_hours,
  response_sla_hours,
  resolution_sla_hours,
  response_sla_status,
  resolution_sla_status,
  CASE 
    WHEN response_sla_status = 'Violated' OR resolution_sla_status = 'Violated' THEN 'YES'
    ELSE 'NO'
  END as sla_violation,
  response_breach_hours,
  resolution_breach_hours,
  CASE 
    WHEN response_sla_status = 'Violated' AND resolution_sla_status = 'Violated' THEN 'Both-SLAs-Missed'
    WHEN response_sla_status = 'Violated' THEN 'Response-SLA-Missed'
    WHEN resolution_sla_status = 'Violated' THEN 'Resolution-SLA-Missed'
    ELSE 'Compliant'
  END as violation_type
FROM sla_compliance
ORDER BY sla_violation DESC, response_breach_hours DESC;