Independent practice · SQLite

Reconcile the SLA review counts

Practice dataset

My saved work →

Your task

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.

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.

sla_statusstring
ticket_countnumber

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

The status counts sum to 8, exactly the cohort count.

Need a next step?

Hint 1: where to start

Classify one row per ticket before grouping.

Hint 2: build the method

GROUP BY sla_status and COUNT(*).

Hint 3: check your work

Reconcile the sum against the initial cohort.

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