Independent practice · SQLite

Assign one follow-up action per ticket

Practice dataset

My saved work →

Your task

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.

Pass every output check to complete this task. Suggested active time: 10 minutes. You can keep working after that.

Required output

Return exactly these columns in this order.

ticket_idnumber
follow_upstring

Expected output: 8 rows. Keep the requested row order.

Values, data types and the number of copies of each row must match. A missing value is distinct from zero and from an empty string.

Preserve SQL NULL unless the task explicitly gives a replacement rule. Check JOINs for extra copies of rows.

Reconciliation checks

Every requested business key appears once.
Preserve SQL NULL and exact output column names.

Need a next step?

Hint 1: where to start

Reuse the checked SLA and evidence results.

Hint 2: build the method

Apply the six rules in the exact stated priority.

Hint 3: check your work

A CASE expression returns the first matching branch.

How your work is checked

Every output check must pass. The score shows how much matched and helps you find mistakes; completion comes from passing all checks. Active time is recorded separately.

Passing records a correct practice output on this device. It is not a professional certification.

Source materials

20 original source records.

Data dictionary

tickets.ticket_id integer: Unique ticket key.
tickets.team text: Owning queue, preserved in handoff.
tickets.priority text: Policy key; P1=30, P2=120 and P3=480 elapsed minutes.
tickets.opened_min integer: UTC elapsed minutes since the scenario origin.
tickets.first_reply_min integer | null: First reply timestamp; future replies are not yet observed and earlier-than-open replies are invalid.
tickets.owner text | null: NULL owner requires assignment when no higher-priority issue exists.
evidence.evidence_id text: Stable reply receipt ID. Exact repeated receipts do not imply extra replies.
evidence.ticket_id integer: Receipt ticket; unmatched receipts are an audit exception.
evidence.recorded_min integer: Receipt time must equal first_reply_min and be no later than as-of.
evidence.kind text: Only public_reply is accepted; internal_note is not a reply receipt.
policies.priority text: Unique SLA policy key.
policies.limit_minutes integer: Allowed elapsed minutes; equality meets the SLA.

View database setup and records

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);

Your work

Active 0:00 · Paused 0s

SQL query

Import answer

Keep a handoff you can replay

Check your current answer, then download the output and its report. The report includes the source, answer and each check so you can review what passed and what needs work.

To replay your work, open this task with the same dataset, import your answer and check it again.

Show the 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.