Independent practice · SQLite

Find invalid reply timestamps

Practice dataset

My saved work →

Your task

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.

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

Expected output: 1 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

Compare two known timestamps.

Hint 2: build the method

Exclude unobserved future replies.

Hint 3: check your work

Use observed_reply < opened_min.

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 FROM checked WHERE observed_reply<opened_min ORDER BY ticket_id

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