Independent practice · SQLite

Find receipts with no ticket

Practice dataset

My saved work →

Your task

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.

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.

evidence_idstring
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

LEFT JOIN evidence to tickets.

Hint 2: build the method

Test the missing ticket key.

Hint 3: check your work

Use DISTINCT to suppress identical receipt copies.

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

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.