Model orders at the right grain

Easy100% solve it

A shop wants two numbers from the same warehouse:

Revenue per product, which needs the individual lines of an order.
The number of orders, which must not be inflated by orders that had three lines.

Model it so both are right at once.

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 int product_name varchar fact_order_lines order_id int line_number int product_key int quantity int amount numeric

One row per order line, not per order
count(distinct order_id) must give the number of orders
The trap is a fact at order grain with the product on it: that answers the second question and cannot answer the first

Input · dim_product

product_key	product_name
1	Keyboard
2	Monitor

Input · fact_order_lines

amount	order_id	quantity	line_number	product_key
50	100	2	1	1
200	100	1	2	2
25	101	1	1	1

Output

product_name	revenue
Keyboard	75
Monitor	200

Input · dim_product

product_key	product_name
1	Cable

Input · fact_order_lines

amount	order_id	quantity	line_number	product_key
10	200	1	1	1
10	200	1	2	1
10	200	1	3	1

Output

orders	lines
1	3

Topics

data modelingfact grain