Merge seven tables into one clean DataFrame

Using pandas only (no loops over rows), write a function build_facts(customers, orders, order_items, products, payments, shipments, refunds) -> pd.DataFrame that returns a single denormalized table with exactly these columns in this order: ["order_item_id","order_id","customer_id","order_ts","paid_ts","shipped_ts","product_id","sku","category","qty","unit_price","line_amount","refund_amount","country","status","payment_method","carrier"]. Input tables (columns): customers(customer_id:int, signup_date:datetime, country:str); orders(order_id:int, customer_id:int, order_ts:datetime, status:str); order_items(order_item_id:int, order_id:int, product_id:int, qty:int, unit_price:float); products(product_id:int, sku:str, category:str); payments(payment_id:int, order_id:int, paid_ts:datetime, method:str, amount:float); shipments(shipment_id:int, order_id:int, shipped_ts:datetime, carrier:str, ship_cost:float); refunds(refund_id:int, order_item_id:int, refund_ts:datetime, refund_amount:float, reason:str). Rules: one row per order_item_id; line_amount = qty * unit_price; refund_amount = sum of refund_amount per order_item_id (0.0 if none); paid_ts = max(paid_ts) per order; payment_method = method associated with the largest payment amount for the order (break ties by lexicographically smallest method); shipped_ts = earliest shipped_ts per order (NaT if none); left‑join so every order_item_id in order_items appears exactly once; ensure datetime dtypes for *_ts, numeric dtypes for qty/unit_price/line_amount/refund_amount; output must contain no extra or missing columns and exact lower_snake_case names. Provide brief unit‑test style assertions for row count preservation, sorted column order, and absence of duplicate order_item_id.

Build a denormalized order item fact table

You are given seven PostgreSQL tables describing an e-commerce system: customers, orders, order_items, products, payments, shipments, and refunds. Write a single SQL query that returns a denormalized fact table with exactly these columns in this order:

order_item_id, order_id, customer_id, order_ts, paid_ts, shipped_ts, product_id, sku, category, qty, unit_price, line_amount, refund_amount, country, status, payment_method, carrier

Rules:

• Return one row per order_item_id; every row from order_items must appear exactly once.
• line_amount = qty * unit_price.
• refund_amount = SUM(refund_amount) for that order_item_id; use 0.0 when there are no refunds.
• paid_ts = the latest paid_ts per order_id; if an order has no payments, paid_ts is NULL.
• payment_method = the method for the largest payment amount for that order_id; if multiple payments tie for largest amount, choose the lexicographically smallest method. If an order has no payments, payment_method is NULL.
• shipped_ts = the earliest shipped_ts per order_id; if an order has no shipments, shipped_ts is NULL.
• carrier = the carrier for that earliest shipment; if multiple shipments tie for earliest timestamp, choose the lexicographically smallest carrier. If an order has no shipments, carrier is NULL.
• Use left joins for optional payments, shipments, and refunds so order_items are not dropped.
• Format order_ts, paid_ts, and shipped_ts as YYYY-MM-DD HH24:MI:SS strings.

Return the result ordered by order_item_id.

Table customers

column	type	constraints
customer_id	INT	PRIMARY KEY, NOT NULL
signup_date	TIMESTAMP	NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	customer_id	signup_date
US	1	2025-01-10 09:00:00
CA	2	2025-02-15 10:30:00
UK	3	2025-03-20 14:45:00

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
order_ts	TIMESTAMP	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

customer_id	order_id	order_ts	status
1	101	2025-05-01 10:00:00	completed
2	102	2025-05-02 12:00:00	pending
1	103	2025-05-03 09:30:00	completed
3	104	2025-05-04 15:45:00	cancelled

Table order_items

column	type	constraints
order_item_id	INT	PRIMARY KEY, NOT NULL
order_id	INT	NOT NULL
product_id	INT	NOT NULL
qty	INT	NOT NULL
unit_price	DECIMAL(10,2)	NOT NULL

Sample data:

order_id	order_item_id	product_id	qty	unit_price
101	1001	501	2	10
101	1002	502	1	20
102	1003	502	3	20
103	1004	503	1	15
104	1005	501	2	10

Table products

column	type	constraints
product_id	INT	PRIMARY KEY, NOT NULL
sku	VARCHAR(50)	NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	product_id	sku
electronics	501	SKU-501
books	502	SKU-502
toys	503	SKU-503

Table payments

column	type	constraints
payment_id	INT	PRIMARY KEY, NOT NULL
order_id	INT	NOT NULL
paid_ts	TIMESTAMP	NOT NULL
method	VARCHAR(30)	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	method	order_id	paid_ts	payment_id
15	card	101	2025-05-01 10:05:00	9001
15	paypal	101	2025-05-01 10:10:00	9002
60	bank_transfer	102	2025-05-02 12:30:00	9003
10	card	103	2025-05-03 09:40:00	9004
5	paypal	103	2025-05-03 09:45:00	9005

Table shipments

column	type	constraints
shipment_id	INT	PRIMARY KEY, NOT NULL
order_id	INT	NOT NULL
shipped_ts	TIMESTAMP	NOT NULL
carrier	VARCHAR(30)	NOT NULL
ship_cost	DECIMAL(10,2)	NOT NULL

Sample data:

carrier	order_id	ship_cost	shipment_id	shipped_ts
DHL	101	5	8001	2025-05-01 11:00:00
UPS	101	3	8002	2025-05-02 09:00:00
FedEx	102	7	8003	2025-05-03 14:00:00
UPS	104	4	8004	2025-05-05 10:00:00

Table refunds

column	type	constraints
refund_id	INT	PRIMARY KEY, NOT NULL
order_item_id	INT	NOT NULL
refund_ts	TIMESTAMP	NOT NULL
refund_amount	DECIMAL(10,2)	NOT NULL
reason	VARCHAR(100)	NOT NULL

Sample data:

order_item_id	reason	refund_amount	refund_id	refund_ts
1001	damaged	2	7001	2025-05-10 13:00:00
1001	customer_return	1	7002	2025-05-11 09:15:00
1003	late_delivery	5	7003	2025-05-12 16:30:00

Expected output

A denormalized fact table with one row per order_item_id combining customer, order, product, payment, shipment, and refund information. For the sample data, there are 5 order items, so the result has 5 rows with the specified columns in the given order.

carrier	category	country	customer_id	line_amount	order_id	order_item_id	order_ts	paid_ts	payment_method	product_id	qty	refund_amount	shipped_ts	sku	status	unit_price
DHL	electronics	US	1	20	101	1001	2025-05-01 10:00:00	2025-05-01 10:10:00	card	501	2	3	2025-05-01 11:00:00	SKU-501	completed	10
DHL	books	US	1	20	101	1002	2025-05-01 10:00:00	2025-05-01 10:10:00	card	502	1	0	2025-05-01 11:00:00	SKU-502	completed	20
FedEx	books	CA	2	60	102	1003	2025-05-02 12:00:00	2025-05-02 12:30:00	bank_transfer	502	3	5	2025-05-03 14:00:00	SKU-502	pending	20
NULL	toys	US	1	15	103	1004	2025-05-03 09:30:00	2025-05-03 09:45:00	card	503	1	0	NULL	SKU-503	completed	15
UPS	electronics	UK	3	20	104	1005	2025-05-04 15:45:00	NULL	NULL	501	2	0	2025-05-05 10:00:00	SKU-501	cancelled	10

Hints

1. Use ROW_NUMBER() to pick the largest payment amount per order with a method tiebreaker, and a window MAX() to retain the latest paid_ts.
2. Use another ROW_NUMBER() to choose the earliest shipment per order with a carrier tiebreaker.
3. Start from order_items and left join optional aggregates so missing payments, shipments, or refunds do not remove rows.