Independent practice · SQLite

Calculate net paid amounts

Practice dataset

My saved work →

Your task

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.

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.

order_idnumber
net_amountnumber

Expected output: 6 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 a CTE to group returns before the 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

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.