An order has many line items and many refunds. Return one row for every order: order_id, gross_cents (sum of quantity * unit_cents), refunded_cents (sum of refunds), net_cents (gross minus refunded). Missing items/refunds contribute zero. Equal amounts on different rows must each count. Refunds may exceed gross; preserve a negative net. Order by order_id. Avoid multiplying amounts when both child tables have multiple rows.
Schema (all columns are NOT NULL unless explicitly nullable):
CREATE TABLE orders (order_id INTEGER PRIMARY KEY);
CREATE TABLE items (item_id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, quantity INTEGER NOT NULL, unit_cents INTEGER NOT NULL);
CREATE TABLE refunds (refund_id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, amount_cents INTEGER NOT NULL);Write one read-only SQLite query. The tables are already populated. Return exactly the requested columns and ordering, with no headers. Empty input produces no rows unless specified otherwise.
Example setup:
INSERT INTO orders VALUES (1); INSERT INTO items VALUES (1,1,2,100),(2,1,1,50); INSERT INTO refunds VALUES (1,1,20),(2,1,30);Expected output:
1|250|50|200Timebox: 9 minutes. Focus on correctness at boundaries and duplicate handling.
Sample tests