Support SLA & Evidence Handoff: handouts

Choose Print / Save as PDF, then select Save as PDF in your browser. The learning copy hides answers.

Support SLA & Evidence Handoff

Original practice materials · Version 2.0.0 · Synthetic business data

Practice database

CREATE TABLE tickets (ticket_id INTEGER, team TEXT, priority TEXT, opened_min INTEGER, first_reply_min INTEGER, owner TEXT);
INSERT INTO tickets VALUES
(6000,'Billing','P1',1440,NULL,NULL),
(6001,'Technical','P2',0,120,'Agent 2'),
(6002,'Delivery','P3',1420,1805,'Agent 3'),
(6003,'Billing','P3',1439,NULL,'Agent 4'),
(6004,'Technical','P2',92,91,'Agent 5'),
(6005,'Delivery','P3',115,115,'Agent 6'),
(6006,'Billing','P1',138,168,'Agent 1'),
(6007,'Technical','P2',161,282,NULL),
(6008,'Delivery','P3',184,NULL,'Agent 3');
CREATE TABLE evidence (evidence_id TEXT, ticket_id INTEGER, recorded_min INTEGER, kind TEXT);
INSERT INTO evidence VALUES
('EV-6000',6001,120,'internal_note'),
('EV-6001',6002,1805,'public_reply'),
('EV-6002',6005,115,'public_reply'),
('EV-6003',6006,168,'public_reply'),
('EV-6004',6007,282,'public_reply'),
('EV-6001',6002,1805,'public_reply'),
('ORPH-6000',6999,10,'public_reply'),
('BAD-6000',6004,92,'public_reply');
CREATE TABLE policies (priority TEXT, limit_minutes INTEGER);
INSERT INTO policies VALUES
('P1',30),
('P2',120),
('P3',480);

1. Freeze the cohort and review clock

Use a reproducible opening window and as-of time.

Elapsed minutes are UTC minutes from the declared origin, without business-hours or holiday rules.
The opening window includes minute zero and excludes minute 1440.
As-of is minute 1800; a reply stored after that minute is not yet observed.

Select the daily ticket cohort

Return ticket_id, opened_min sorted by ticket_id.
Include 0 <= opened_min < 1440; minutes are UTC elapsed time from 2026-10-01 00:00.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

SELECT ticket_id,opened_min FROM tickets WHERE opened_min>=0 AND opened_min<1440 ORDER BY ticket_id

The exclusive end avoids counting a boundary ticket in two daily reports.

Expose replies observed by review time

Return ticket_id, observed_reply sorted by ticket_id for the daily cohort.
As-of is minute 1800. A stored first_reply_min after 1800 becomes SQL NULL; preserve earlier values even if they are invalid and need a later check.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

WITH cohort AS (SELECT t.*,p.limit_minutes,CASE WHEN t.first_reply_min<=1800 THEN t.first_reply_min ELSE NULL END AS observed_reply FROM tickets t JOIN policies p ON t.priority=p.priority WHERE t.opened_min>=0 AND t.opened_min<1440), checked AS (SELECT c.*,CASE WHEN observed_reply<opened_min THEN 'invalid_time' WHEN COALESCE(observed_reply,1800)-opened_min>limit_minutes THEN 'breached' WHEN observed_reply IS NOT NULL THEN 'met' ELSE 'awaiting' END AS sla_status,(SELECT MIN(e.evidence_id) FROM evidence e WHERE e.ticket_id=c.ticket_id AND e.kind='public_reply' AND e.recorded_min=c.observed_reply AND e.recorded_min>=c.opened_min AND e.recorded_min<=1800) AS evidence_id FROM cohort c) SELECT ticket_id,observed_reply FROM checked ORDER BY ticket_id

Observation and validity are separate checks: expose the past timestamp now, then diagnose it.

Extension: A different as-of time can legitimately change a still-open ticket status.

2. Apply deadline boundaries

Distinguish invalid timing, breached deadlines and awaiting replies.

Each priority has a supplied limit in the policies table.
A reply earlier than opening is invalid_time and takes precedence.
Use observed reply minus opening, or as-of minus opening for unanswered tickets. Equality meets the limit.

Find invalid reply timestamps

Return ticket_id sorted ascending for observed replies earlier than ticket opening.
Use the daily cohort and as-of minute 1800. Missing or future replies are not invalid-time records for this check.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

WITH cohort AS (SELECT t.*,p.limit_minutes,CASE WHEN t.first_reply_min<=1800 THEN t.first_reply_min ELSE NULL END AS observed_reply FROM tickets t JOIN policies p ON t.priority=p.priority WHERE t.opened_min>=0 AND t.opened_min<1440), checked AS (SELECT c.*,CASE WHEN observed_reply<opened_min THEN 'invalid_time' WHEN COALESCE(observed_reply,1800)-opened_min>limit_minutes THEN 'breached' WHEN observed_reply IS NOT NULL THEN 'met' ELSE 'awaiting' END AS sla_status,(SELECT MIN(e.evidence_id) FROM evidence e WHERE e.ticket_id=c.ticket_id AND e.kind='public_reply' AND e.recorded_min=c.observed_reply AND e.recorded_min>=c.opened_min AND e.recorded_min<=1800) AS evidence_id FROM cohort c) SELECT ticket_id FROM checked WHERE observed_reply<opened_min ORDER BY ticket_id

A negative duration is a data error, not a very fast response.

Classify first-response SLA

Return ticket_id, sla_status sorted ascending for the daily cohort at minute 1800.
Earlier-than-open observed reply → invalid_time. Otherwise elapsed > policy limit → breached; observed timely reply → met; timely unanswered ticket → awaiting.
Use P1=30, P2=120, P3=480 from policies. Equality meets the policy.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

WITH cohort AS (SELECT t.*,p.limit_minutes,CASE WHEN t.first_reply_min<=1800 THEN t.first_reply_min ELSE NULL END AS observed_reply FROM tickets t JOIN policies p ON t.priority=p.priority WHERE t.opened_min>=0 AND t.opened_min<1440), checked AS (SELECT c.*,CASE WHEN observed_reply<opened_min THEN 'invalid_time' WHEN COALESCE(observed_reply,1800)-opened_min>limit_minutes THEN 'breached' WHEN observed_reply IS NOT NULL THEN 'met' ELSE 'awaiting' END AS sla_status,(SELECT MIN(e.evidence_id) FROM evidence e WHERE e.ticket_id=c.ticket_id AND e.kind='public_reply' AND e.recorded_min=c.observed_reply AND e.recorded_min>=c.opened_min AND e.recorded_min<=1800) AS evidence_id FROM cohort c) SELECT ticket_id,sla_status FROM checked ORDER BY ticket_id

CASE ordering matters. Invalid timestamps must not pass as timely, and unresolved replies use the review clock.

Extension: These are original practice policies; real contracts may use different clocks, calendars and pause rules.

3. Attach a trustworthy evidence receipt

Prove each observed reply with a matching public receipt.

A ticket timestamp is a claim; a receipt provides its provenance.
A public_reply must match the same ticket and exact observed reply minute.
Internal notes, future records and orphan receipts cannot prove a customer response.

Find receipts with no ticket

Return evidence_id, ticket_id sorted by evidence_id for receipts with no matching ticket.
Collapse exact repeated output pairs; inspect all evidence kinds and times.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

SELECT DISTINCT e.evidence_id,e.ticket_id FROM evidence e LEFT JOIN tickets t ON t.ticket_id=e.ticket_id WHERE t.ticket_id IS NULL ORDER BY e.evidence_id

An orphan receipt should remain visible as an exception; it cannot be attached to a fabricated ticket.

Attach first-response evidence IDs

Return ticket_id, evidence_id sorted by ticket_id for the daily cohort.
Match public_reply for the same ticket at exactly the observed first reply minute, within opening and as-of 1800. Choose the smallest evidence_id; otherwise SQL NULL.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

WITH cohort AS (SELECT t.*,p.limit_minutes,CASE WHEN t.first_reply_min<=1800 THEN t.first_reply_min ELSE NULL END AS observed_reply FROM tickets t JOIN policies p ON t.priority=p.priority WHERE t.opened_min>=0 AND t.opened_min<1440), checked AS (SELECT c.*,CASE WHEN observed_reply<opened_min THEN 'invalid_time' WHEN COALESCE(observed_reply,1800)-opened_min>limit_minutes THEN 'breached' WHEN observed_reply IS NOT NULL THEN 'met' ELSE 'awaiting' END AS sla_status,(SELECT MIN(e.evidence_id) FROM evidence e WHERE e.ticket_id=c.ticket_id AND e.kind='public_reply' AND e.recorded_min=c.observed_reply AND e.recorded_min>=c.opened_min AND e.recorded_min<=1800) AS evidence_id FROM cohort c) SELECT ticket_id,evidence_id FROM checked ORDER BY ticket_id

Joining all receipts directly may duplicate tickets and attach internal notes. Reduce evidence to one valid receipt per ticket.

Extension: Keep unmatched receipts in a separate investigation file.

4. Prioritize the handoff

Turn timestamp and evidence checks into explicit next actions.

Use deterministic priority when a ticket has more than one problem.
Repair invalid timestamps first, escalate breaches next, then attach missing evidence.
After those checks, assign missing owners, watch awaiting deadlines or mark the record ready.

Assign one follow-up action per ticket

Return ticket_id, follow_up sorted by ticket_id for the same daily cohort at minute 1800.
Priority: invalid_time → repair timestamps; breached → escalate; observed reply without evidence → attach reply evidence; NULL owner → assign owner; awaiting → watch deadline; otherwise ready.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

WITH cohort AS (SELECT t.*,p.limit_minutes,CASE WHEN t.first_reply_min<=1800 THEN t.first_reply_min ELSE NULL END AS observed_reply FROM tickets t JOIN policies p ON t.priority=p.priority WHERE t.opened_min>=0 AND t.opened_min<1440), checked AS (SELECT c.*,CASE WHEN observed_reply<opened_min THEN 'invalid_time' WHEN COALESCE(observed_reply,1800)-opened_min>limit_minutes THEN 'breached' WHEN observed_reply IS NOT NULL THEN 'met' ELSE 'awaiting' END AS sla_status,(SELECT MIN(e.evidence_id) FROM evidence e WHERE e.ticket_id=c.ticket_id AND e.kind='public_reply' AND e.recorded_min=c.observed_reply AND e.recorded_min>=c.opened_min AND e.recorded_min<=1800) AS evidence_id FROM cohort c) SELECT ticket_id,CASE WHEN sla_status='invalid_time' THEN 'repair timestamps' WHEN sla_status='breached' THEN 'escalate' WHEN observed_reply IS NOT NULL AND evidence_id IS NULL THEN 'attach reply evidence' WHEN owner IS NULL THEN 'assign owner' WHEN sla_status='awaiting' THEN 'watch deadline' ELSE 'ready' END AS follow_up FROM checked ORDER BY ticket_id

Use the first matching action only. Alphabetical or severity guesses do not implement the declared handoff policy.

Reconcile the SLA review counts

Return sla_status, ticket_count sorted by sla_status.
Count the daily ticket cohort at minute 1800 using the declared SLA rules. Include only statuses actually present.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

WITH cohort AS (SELECT t.*,p.limit_minutes,CASE WHEN t.first_reply_min<=1800 THEN t.first_reply_min ELSE NULL END AS observed_reply FROM tickets t JOIN policies p ON t.priority=p.priority WHERE t.opened_min>=0 AND t.opened_min<1440), checked AS (SELECT c.*,CASE WHEN observed_reply<opened_min THEN 'invalid_time' WHEN COALESCE(observed_reply,1800)-opened_min>limit_minutes THEN 'breached' WHEN observed_reply IS NOT NULL THEN 'met' ELSE 'awaiting' END AS sla_status,(SELECT MIN(e.evidence_id) FROM evidence e WHERE e.ticket_id=c.ticket_id AND e.kind='public_reply' AND e.recorded_min=c.observed_reply AND e.recorded_min>=c.opened_min AND e.recorded_min<=1800) AS evidence_id FROM cohort c) SELECT sla_status,COUNT(*) AS ticket_count FROM checked GROUP BY sla_status ORDER BY sla_status

Aggregate checked ticket rows, not evidence receipts. The sum must equal the eligible ticket count.

Extension: A handoff action is a review queue recommendation; it does not send replies or change a ticketing system.

Final project

Handoff a checked SLA and evidence queue

Return ticket_id, team, owner, sla_status, evidence_id, follow_up, sorted by ticket_id. Preserve SQL NULL owner and evidence_id.
Times are UTC elapsed minutes from 2026-10-01 00:00 (transfer: the same declared origin). Include 0 <= opened_min < 1440; assess at minute 1800. Stored replies after as-of are treated as not yet observed.
Join priority to policies. A reply earlier than opening is invalid_time. Otherwise elapsed minutes above the limit is breached; equality meets the SLA. A timely observed reply is met; a timely ticket without an observed reply is awaiting.
Reply evidence must be public_reply, for the same ticket, with recorded_min exactly equal to the observed reply, not earlier than opening or later than as-of. Choose the lexicographically smallest evidence_id if several qualify; exact duplicate receipts do not duplicate tickets.
follow_up priority: invalid_time → repair timestamps; breached → escalate; observed reply with no evidence → attach reply evidence; NULL owner → assign owner; awaiting → watch deadline; otherwise ready. These are fictional training policies, not an industry-wide SLA standard.

Use the complete project source data in the course download. Every output check must pass.

WITH cohort AS (SELECT t.*,p.limit_minutes,CASE WHEN t.first_reply_min<=1800 THEN t.first_reply_min ELSE NULL END AS observed_reply FROM tickets t JOIN policies p ON t.priority=p.priority WHERE t.opened_min>=0 AND t.opened_min<1440), checked AS (SELECT c.*,CASE WHEN observed_reply<opened_min THEN 'invalid_time' WHEN COALESCE(observed_reply,1800)-opened_min>limit_minutes THEN 'breached' WHEN observed_reply IS NOT NULL THEN 'met' ELSE 'awaiting' END AS sla_status,(SELECT MIN(e.evidence_id) FROM evidence e WHERE e.ticket_id=c.ticket_id AND e.kind='public_reply' AND e.recorded_min=c.observed_reply AND e.recorded_min>=c.opened_min AND e.recorded_min<=1800) AS evidence_id FROM cohort c) SELECT ticket_id,team,owner,sla_status,evidence_id,CASE WHEN sla_status='invalid_time' THEN 'repair timestamps' WHEN sla_status='breached' THEN 'escalate' WHEN observed_reply IS NOT NULL AND evidence_id IS NULL THEN 'attach reply evidence' WHEN owner IS NULL THEN 'assign owner' WHEN sla_status='awaiting' THEN 'watch deadline' ELSE 'ready' END AS follow_up FROM checked ORDER BY ticket_id;

Related public objectives: https://sqlbolt.com/.

Independent original materials. CC BY 4.0.