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 2.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, net_revenue in that order. All monetary values in this project are integer cents. Sort net_revenue descending, then region ascending.
Sales cohort: paid orders with 2026-09-01 <= ordered_at < 2026-10-01. Refund cohort: posted refunds with the same date window applied to booked_at.
Deduplicate refunds by refund_id: identical copies count once; if any order_id, amount, status or booked_at disagrees, exclude every variant of that refund_id.
Aggregate eligible refund amounts by order before joining sales. Orphan refunds and refunds against sales outside the cohort do not affect this report.
No refund means zero. Keep negative net amounts when refunds exceed a sale; do not clamp to zero. Over-refunded means total eligible refunds strictly greater than sale amount.

Use the complete project source data in the course download. Every output check must pass.

WITH clean_refunds AS (
 SELECT DISTINCT r.* FROM refunds r WHERE NOT EXISTS (
  SELECT 1 FROM refunds x WHERE x.refund_id=r.refund_id AND
  (x.order_id<>r.order_id OR x.amount<>r.amount OR x.status<>r.status OR x.booked_at<>r.booked_at)
 )
), posted AS (SELECT order_id,SUM(amount) AS refunded FROM clean_refunds WHERE status='posted' AND booked_at>='2026-09-01' AND booked_at<'2026-10-01' GROUP BY order_id), ledger AS (SELECT o.*,COALESCE(p.refunded,0) AS refunded FROM orders o LEFT JOIN posted p ON p.order_id=o.order_id WHERE o.status='paid' AND o.ordered_at>='2026-09-01' AND o.ordered_at<'2026-10-01') SELECT region,SUM(amount-refunded) AS net_revenue FROM ledger GROUP BY region ORDER BY net_revenue DESC,region ASC;

Related public objectives: https://sqlzoo.net/wiki/SQL_Tutorial.

Independent original materials. CC BY 4.0.