SQL Operations Cases: handouts

Choose Print / Save as PDF, then select Save as PDF in your browser. The learning copy hides answers.

SQL Operations Cases

Original practice materials · Version 1.0.0 · Synthetic business data

Practice database

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

1. Count a funnel honestly

Count unique customers rather than repeated visits.

A customer may visit more than once.
Count distinct customer_id for each stage.
A count of events is not a count of people.

Count unique customers by stage

Return stage and customers.
Count unique customer IDs per stage.

SELECT * FROM orders;

Reference answer

SELECT stage, COUNT(DISTINCT customer_id) AS customers FROM events GROUP BY stage

The repeated visit from customer 1 must count once.

Identify visitors without checkout

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

SELECT * FROM orders;

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.

Extension: A funnel conversion definition must use a consistent cohort; this exercise reports stage counts only.

2. Account for partial refunds

Aggregate multiple refunds before calculating net revenue.

An order may have more than one refund row.
Sum refunds by order_id before joining.
Net revenue equals amount minus total refund.

Calculate net paid amounts

Return order_id and net_amount for every paid order.
Sum multiple refunds per order before joining.
Missing refunds mean 0; amounts are whole dollars.

SELECT * FROM orders;

Reference answer

WITH r AS (SELECT order_id,SUM(refund) refund FROM returns GROUP BY order_id) SELECT o.order_id,o.amount-COALESCE(r.refund,0) AS net_amount FROM orders o LEFT JOIN r ON o.order_id=r.order_id WHERE o.status='paid'

A direct join repeats the sale for each refund. Order 1 net is 90.

Find unmatched refunds

Return return_id and order_id for refunds with no matching order.
Preserve all orphan refund rows. Order is not required.

SELECT * FROM orders;

Reference answer

SELECT r.return_id,r.order_id FROM returns r LEFT JOIN orders o ON r.order_id=o.order_id WHERE o.order_id IS NULL

Return 4 points to missing order 99.

Extension: Inspect orphan refund records separately instead of forcing a match.

3. Flag inventory exceptions

Compare known quantities while separating missing counts.

Low stock means on_hand < reorder_at, strictly.
NULL on_hand is an unknown count, not zero.
Equality is not below the reorder point.

Find stock below the reorder point

Return sku and on_hand.
Include only known counts strictly below reorder_at.

SELECT * FROM orders;

Reference answer

SELECT sku,on_hand FROM inventory WHERE on_hand<reorder_at

Do not include CARD at equality or BAG with NULL.

List products needing a recount

Return sku for missing stock counts.
Zero is a known count and should be excluded.

SELECT * FROM orders;

Reference answer

SELECT sku FROM inventory WHERE on_hand IS NULL

BAG is unknown. MUG is a known zero.

Extension: Changing < to <= is a business policy change; keep the stated rule.

4. Diagnose duplicate customer keys

Find shared nonblank emails without deleting records.

Two customer IDs may share an email.
Exclude NULL and empty text from this diagnostic.
Report the duplicate key and its count.

Find shared customer emails

Return email and customer_count.
Exclude NULL and empty email.
Include only keys used by more than one customer.

SELECT * FROM orders;

Reference answer

SELECT email,COUNT(*) AS customer_count FROM customers WHERE email IS NOT NULL AND email<>'' GROUP BY email HAVING COUNT(*)>1

The shared email has two customer IDs. Missing emails are not duplicate keys.

Find paid orders with unmatched customer IDs

Return order_id and customer_id.
Include paid orders whose customer ID has no matching record.

SELECT * FROM orders;

Reference answer

SELECT o.order_id,o.customer_id FROM orders o LEFT JOIN customers c ON o.customer_id=c.customer_id WHERE o.status='paid' AND c.customer_id IS NULL

Order 5 references 99. Cancelled NULL-key order 6 is not paid.

Extension: A shared email can be legitimate; flag it for review instead of merging automatically.

Final project

Deliver regional net revenue

Return region and net_revenue for paid orders.
Preaggregate refunds per order. Include unmatched customer orders.
Sort net_revenue descending then region ascending.

WITH r AS (SELECT order_id,SUM(refund) AS refunded FROM returns GROUP BY order_id) SELECT o.region,SUM(o.amount-COALESCE(r.refunded,0)) AS net_revenue FROM orders o LEFT JOIN r ON o.order_id=r.order_id WHERE o.status='paid' GROUP BY o.region ORDER BY net_revenue DESC, o.region ASC

Source objectives: https://sqlzoo.net/wiki/SQL_Tutorial. Independent original materials. CC BY 4.0.