Independent practice · SQLite

Identify visitors without checkout

Practice dataset

My saved work →

Your task

Return customer_id for visitors who never checked out.
No repeated customer IDs.

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.

customer_idnumber

Expected output: 2 rows. Row order does not affect the check.

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

Return exactly the named columns in the stated order.
Preserve row multiplicity, numeric types and the declared missing-value policy.

Need a next step?

Hint 1: where to start

Use NOT EXISTS or a suitable left join.

Hint 2: build the method

Check the declared business key, eligibility rules and missing-value policy before editing.

Hint 3: check your work

Compare the exact output columns, row count and each value with the contract; matching a total alone is insufficient.

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

29 original source records.

Data dictionary

orders table: Orders with paid, pending and cancelled statuses, integer whole-dollar amounts and nullable customer/region keys.
customers table: Customer dimension keyed by customer_id; emails may be shared, empty or NULL.
returns table: Partial refunds in whole dollars, with an orphan order reference.
inventory table: Physical counts and reorder points; on_hand can be NULL.
events table: Customer events; repeated visits are separate events.

View database setup and records

CREATE TABLE orders(order_id INTEGER, customer_id INTEGER, status TEXT, amount INTEGER, region TEXT, ordered_at TEXT);
INSERT INTO orders VALUES (1,1,'paid',120,'East','2026-09-01'),(2,2,'pending',140,'West','2026-09-02'),(3,1,'paid',100,'East','2026-09-03'),(4,3,'paid',180,'West','2026-09-03'),(5,99,'paid',80,'East','2026-09-04'),(6,NULL,'cancelled',0,NULL,'2026-09-04'),(7,2,'paid',120,'West','2026-09-05'),(8,4,'paid',30,'North','2026-09-06');
CREATE TABLE customers(customer_id INTEGER PRIMARY KEY,name TEXT,email TEXT);
INSERT INTO customers VALUES(1,'Avery','team@example.test'),(2,'Morgan','team@example.test'),(3,'Riley',NULL),(4,'Casey',''),(5,'Sky','sky@example.test');
CREATE TABLE returns(return_id INTEGER,order_id INTEGER,refund INTEGER);
INSERT INTO returns VALUES(1,1,20),(2,1,10),(3,4,180),(4,99,25);
CREATE TABLE inventory(sku TEXT,on_hand INTEGER,reorder_at INTEGER);
INSERT INTO inventory VALUES('PEN',5,10),('BOOK',20,10),('MUG',0,5),('BAG',NULL,4),('CARD',4,4);
CREATE TABLE events(event_id INTEGER,customer_id INTEGER,stage TEXT);
INSERT INTO events VALUES(1,1,'visit'),(2,1,'visit'),(3,1,'checkout'),(4,2,'visit'),(5,3,'visit'),(6,3,'checkout'),(7,4,'visit');

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.customer_id FROM events e WHERE e.stage='visit' AND NOT EXISTS(SELECT 1 FROM events c WHERE c.customer_id=e.customer_id AND c.stage='checkout')

Customers 2 and 4 visited but have no checkout event.