Model returns against orders

MediumNot attempted yetAsked at Amazon

Customers return part of an order, sometimes more than once, sometimes months later. Finance needs net revenue per product — sales less returns — and it has to stay right when only two of the three units come back.

What the grader expects

Your DDL is executed, rows are inserted into the tables you create, and queries are run against them, so the names are part of the answer:

dim_product product_key INTEGER product_name VARCHAR fact_order_lines order_id BIGINT line_number INTEGER product_key INTEGER quantity INTEGER amount DECIMAL(18,2) fact_order_returns return_id BIGINT order_id BIGINT line_number INTEGER quantity INTEGER amount DECIMAL(18,2) returned_on DATE

Constraints

A return is its own row, at return grain — not a flag or a column on the order line
One order line can have several returns; together they never exceed what was sold
Relationships are optional, but if you draw them, the returns point at the order line
The trap is a returned BOOLEAN on fact_order_lines: it answers "was this returned" and can express neither a partial return nor a second one

Example 1a whole line comes back

Input · dim_product

product_key	product_name
1	Lamp

Input · fact_order_lines

amount	order_id	quantity	line_number	product_key
200	10	2	1	1

Input · fact_order_returns

amount	order_id	quantity	return_id	line_number	returned_on
200	10	2	500	1	2026-02-01

Output

product_name	net_revenue
Lamp	0

Example 2only part of the line comes back

Input · dim_product

product_key	product_name
1	Chair

Input · fact_order_lines

amount	order_id	quantity	line_number	product_key
300	11	3	1	1

Input · fact_order_returns

amount	order_id	quantity	return_id	line_number	returned_on
100	11	1	501	1	2026-02-05

Output

sold	returned	net
300	100	200

Topics

data modelingfact grain