Unify 7 tables and impute missing values

Using pandas, write a robust function unify_orders(...) that ingests seven dataframes (or CSVs) with possibly inconsistent column casing/whitespace and returns a single denormalized OrdersAnalytics table with exact columns and order: [order_id, order_date, customer_id, customer_name, shipper_name, total_amount, product_count, category_list, payment_status]. Rules: - order_date must be a YYYY-MM-DD string; - total_amount is sum(quantity*unit_price) across items per order; - product_count is count of distinct product_id per order; - category_list is ';'-joined, deduplicated, alphabetically sorted category_name per order; - Keep orders even if shipper or payment is missing (shipper_name may be null; payment_status becomes 'unknown'); - No extra/missing columns; assert returned_df.columns == [...]. Handle missing values: unit_price imputed by product-level median; if unavailable, use category-level median; if still missing, use global median across order_items. quantity missing -> impute 1. Payment amount missing -> recompute from items; payment_status missing -> 'unknown'. Normalize column names to snake_case and strip cell whitespace before processing. Provide O(N log N) or better joins and avoid quadratic loops. Use the following small ASCII samples to illustrate joins and expected aggregation behavior (you do not need to hardcode these): customers: +-------------+-------------+ | customer_id | name | +-------------+-------------+ | 1 | Ada Lovelace| | 2 | A. Turing | +-------------+-------------+ orders: +----------+-------------+------------+ | order_id | customer_id | shipper_id | +----------+-------------+------------+ | 10 | 1 | 100 | | 11 | 1 | null | | 12 | 2 | 101 | +----------+-------------+------------+ (OrderDate column may appear as 'OrderDate' or 'order_date' in files; assume values: 2025-05-01 for 10, 2025-05-03 for 11, 2025-05-04 for 12.) order_items: +----------+------------+----------+------------+ | order_id | product_id | quantity | unit_price | +----------+------------+----------+------------+ | 10 | 501 | 2 | 30.0 | | 10 | 502 | null | 10.0 | | 11 | 501 | 1 | null | | 12 | 503 | 3 | 7.5 | +----------+------------+----------+------------+ products: +------------+--------------+-------------+ | product_id | product_name | category_id | +------------+--------------+-------------+ | 501 | Widget A | 9001 | | 502 | Gadget B | 9002 | | 503 | Gizmo C | 9001 | +------------+--------------+-------------+ categories: +-------------+---------------+ | category_id | category_name | +-------------+---------------+ | 9001 | Tools | | 9002 | Accessories | +-------------+---------------+ shippers: +------------+--------------+ | shipper_id | shipper_name | +------------+--------------+ | 100 | FastShip | | 101 | SureShip | +------------+--------------+ payments: +----------+----------------+--------+ | order_id | payment_status | amount | +----------+----------------+--------+ | 10 | paid | 70.0 | | 11 | null | null | +----------+----------------+--------+ Sub-questions: 1) Specify the exact pandas operations (merges/groupbys) and any indices you would set to make it efficient. 2) Show the final expected row for order_id=10 (verify total_amount, product_count, category_list). 3) Explain how your imputation prevents data leakage if the data later gets split by date for modeling.

Unify seven order-related tables into an OrdersAnalytics view with imputations

Write a single SQL query that takes the seven tables below (customers, orders, order_items, products, categories, shippers, payments) and produces a denormalized result named OrdersAnalytics with the exact columns and order:

[order_id, order_date, customer_id, customer_name, shipper_name, total_amount, product_count, category_list, payment_status]

Use the following rules:

• order_date in the result must be a 'YYYY-MM-DD' string (cast from the DATE column in orders).
• total_amount is the sum over all items in the order of (imputed_quantity * imputed_unit_price).
• product_count is the count of DISTINCT product_id values per order.
• category_list is a ';'-separated string of DISTINCT category_name values per order, alphabetically sorted by category_name (e.g., 'Accessories;Tools').
• Keep all orders even if there is no shipper or payment record. If shipper is missing, shipper_name is NULL. If payment record is missing or payment_status is NULL, payment_status must be 'unknown'.
• The output must have no extra or missing columns and must appear in exactly the column order listed above.

Handle missing values with the following imputation logic (assume NULL represents missing):

• unit_price: impute using the median unit_price at the product level (same product_id). If that median is NULL (no non-null prices for that product), use the median at the category level (same category_id). If that is still NULL, use the global median unit_price across all order_items.
• quantity: if NULL, impute 1.
• Payment amount (payments.amount): if NULL, recompute it as the total_amount for that order (after the above imputations). This recomputed amount does not need to be returned in OrdersAnalytics but should be available in your query logic (e.g., as a CTE).
• payment_status: if NULL or if there is no payments row, output 'unknown'.

Use joins and window functions so that the overall complexity is O(N log N) or better; do not rely on per-row procedural loops. You may assume all column names are already in snake_case as given in the schema.

Table customers

column	type	constraints
customer_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL

Sample data:

customer_id	name
1	Ada Lovelace
2	A. Turing

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL
shipper_id	INT
order_date	DATE	NOT NULL

Sample data:

customer_id	order_date	order_id	shipper_id
1	2025-05-01	10	100
1	2025-05-03	11	NULL
2	2025-05-04	12	101
2	2025-05-05	13	100

Table order_items

column	type	constraints
order_id	INT	NOT NULL
product_id	INT	NOT NULL
quantity	INT
unit_price	DECIMAL(10,2)

Sample data:

order_id	product_id	quantity	unit_price
10	501	2	30
10	502	NULL	10
11	501	1	NULL
12	503	3	7.5
13	504	2	NULL
13	505	1	NULL

Table products

column	type	constraints
product_id	INT	PRIMARY KEY
product_name	VARCHAR(100)	NOT NULL
category_id	INT	NOT NULL

Sample data:

category_id	product_id	product_name
9001	501	Widget A
9002	502	Gadget B
9001	503	Gizmo C
9001	504	Widget D
9003	505	Gadget E

Table categories

column	type	constraints
category_id	INT	PRIMARY KEY
category_name	VARCHAR(100)	NOT NULL

Sample data:

category_id	category_name
9001	Tools
9002	Accessories
9003	Misc

Table shippers

column	type	constraints
shipper_id	INT	PRIMARY KEY
shipper_name	VARCHAR(100)	NOT NULL

Sample data:

shipper_id	shipper_name
100	FastShip
101	SureShip

Table payments

column	type	constraints
order_id	INT	PRIMARY KEY
payment_status	VARCHAR(20)
amount	DECIMAL(10,2)

Sample data:

amount	order_id	payment_status
70	10	paid
NULL	11	NULL

Expected output

A denormalized OrdersAnalytics result with one row per order and columns [order_id, order_date (YYYY-MM-DD string), customer_id, customer_name, shipper_name, total_amount, product_count, category_list, payment_status]. Using the sample data, all four orders (10–13) appear, including orders with missing shipper or payment info. Numeric values reflect the specified imputations and aggregations.

category_list	customer_id	customer_name	order_date	order_id	payment_status	product_count	shipper_name	total_amount
Accessories;Tools	1	Ada Lovelace	2025-05-01	10	paid	2	FastShip	70
Tools	1	Ada Lovelace	2025-05-03	11	unknown	1	NULL	30
Tools	2	A. Turing	2025-05-04	12	unknown	1	SureShip	22.5
Misc;Tools	2	A. Turing	2025-05-05	13	unknown	2	FastShip	47.5

Hints

1. In PostgreSQL, percentile_cont is an ordered-set aggregate; compute it in grouped CTEs and join it back instead of using it as a window function.
2. Build the distinct category rows before string_agg to keep ordering deterministic.

Verify the OrdersAnalytics aggregation for a single order

Using the same schema and business rules as in Question 1, write a SQL query that returns only the OrdersAnalytics row for order_id = 10. Your query should apply the same imputations and aggregations (including unit_price and quantity imputations, category_list construction, and payment_status handling) and then filter for order_id = 10.

Return the columns in the exact order: [order_id, order_date, customer_id, customer_name, shipper_name, total_amount, product_count, category_list, payment_status].

Table customers

column	type	constraints
customer_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL

Sample data:

customer_id	name
1	Ada Lovelace
2	A. Turing

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL
shipper_id	INT
order_date	DATE	NOT NULL

Sample data:

customer_id	order_date	order_id	shipper_id
1	2025-05-01	10	100
1	2025-05-03	11	NULL
2	2025-05-04	12	101
2	2025-05-05	13	100

Table order_items

column	type	constraints
order_id	INT	NOT NULL
product_id	INT	NOT NULL
quantity	INT
unit_price	DECIMAL(10,2)

Sample data:

order_id	product_id	quantity	unit_price
10	501	2	30
10	502	NULL	10
11	501	1	NULL
12	503	3	7.5
13	504	2	NULL
13	505	1	NULL

Table products

column	type	constraints
product_id	INT	PRIMARY KEY
product_name	VARCHAR(100)	NOT NULL
category_id	INT	NOT NULL

Sample data:

category_id	product_id	product_name
9001	501	Widget A
9002	502	Gadget B
9001	503	Gizmo C
9001	504	Widget D
9003	505	Gadget E

Table categories

column	type	constraints
category_id	INT	PRIMARY KEY
category_name	VARCHAR(100)	NOT NULL

Sample data:

category_id	category_name
9001	Tools
9002	Accessories
9003	Misc

Table shippers

column	type	constraints
shipper_id	INT	PRIMARY KEY
shipper_name	VARCHAR(100)	NOT NULL

Sample data:

shipper_id	shipper_name
100	FastShip
101	SureShip

Table payments

column	type	constraints
order_id	INT	PRIMARY KEY
payment_status	VARCHAR(20)
amount	DECIMAL(10,2)

Sample data:

amount	order_id	payment_status
70	10	paid
NULL	11	NULL

Expected output

Single OrdersAnalytics row for order_id = 10, showing the correctly aggregated totals and category list after imputations.

category_list	customer_id	customer_name	order_date	order_id	payment_status	product_count	shipper_name	total_amount
Accessories;Tools	1	Ada Lovelace	2025-05-01	10	paid	2	FastShip	70

Hints

1. Reuse the same imputation and aggregation steps before applying the final order filter.

Compute time-aware medians to avoid data leakage

Time-aware median imputation (avoiding data leakage)

When you build models that will be split chronologically on order_date, any statistic you use to impute missing values must be computed using only data that was available up to and including each order's date. Computing a global median across the whole table leaks future information into earlier rows.

Using the schema below, write a single PostgreSQL query that, for every row in order_items, computes three time-aware median unit_price values. Each median is taken only over order_items whose order has order_date <= the current row's order_date, and NULL unit_price values are ignored when computing the medians:

• product_median_to_date — median unit_price among items with the same product_id and order_date <= current row's order_date.
• category_median_to_date — median unit_price among items in the same category_id (joined via products) and order_date <= current row's order_date.
• global_median_to_date — median unit_price among all items with order_date <= current row's order_date.

Use the continuous median (percentile_cont(0.5)), so an even-sized group returns the average of the two middle values. If no non-NULL prices qualify for a given scope, that median should be NULL.

Output

Return exactly these columns, in this order:

order_id, product_id, order_date, unit_price, product_median_to_date, category_median_to_date, global_median_to_date

Where order_date is rendered as a YYYY-MM-DD string and unit_price is the row's raw price (which may be NULL). Sort the result by order_date, then order_id, then product_id (all ascending).

Table customers

column	type	constraints
customer_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL

Sample data:

customer_id	name
1	Ada Lovelace
2	A. Turing

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL
shipper_id	INT
order_date	DATE	NOT NULL

Sample data:

customer_id	order_date	order_id	shipper_id
1	2025-05-01	10	100
1	2025-05-03	11	NULL
2	2025-05-04	12	101
2	2025-05-05	13	100

Table order_items

column	type	constraints
order_id	INT	NOT NULL
product_id	INT	NOT NULL
quantity	INT
unit_price	DECIMAL(10,2)

Sample data:

order_id	product_id	quantity	unit_price
10	501	2	30
10	502	NULL	10
11	501	1	NULL
12	503	3	7.5
13	504	2	NULL
13	505	1	NULL

Table products

column	type	constraints
product_id	INT	PRIMARY KEY
product_name	VARCHAR(100)	NOT NULL
category_id	INT	NOT NULL

Sample data:

category_id	product_id	product_name
9001	501	Widget A
9002	502	Gadget B
9001	503	Gizmo C
9001	504	Widget D
9003	505	Gadget E

Table categories

column	type	constraints
category_id	INT	PRIMARY KEY
category_name	VARCHAR(100)	NOT NULL

Sample data:

category_id	category_name
9001	Tools
9002	Accessories
9003	Misc

Table shippers

column	type	constraints
shipper_id	INT	PRIMARY KEY
shipper_name	VARCHAR(100)	NOT NULL

Sample data:

shipper_id	shipper_name
100	FastShip
101	SureShip

Table payments

column	type	constraints
order_id	INT	PRIMARY KEY
payment_status	VARCHAR(20)
amount	DECIMAL(10,2)

Sample data:

amount	order_id	payment_status
70	10	paid
NULL	11	NULL

Expected output

One row per order_items row with the raw unit_price plus three medians computed only from non-NULL prices whose order_date <= the current row's date, scoped to the same product, same category, and globally; sorted by order_date, order_id, product_id.

category_median_to_date	global_median_to_date	order_date	order_id	product_id	product_median_to_date	unit_price
30	20	2025-05-01	10	501	30	30.00
10	20	2025-05-01	10	502	10	10.00
30	20	2025-05-03	11	501	30	NULL
18.75	10	2025-05-04	12	503	7.5	7.50
18.75	10	2025-05-05	13	504	NULL	NULL
NULL	10	2025-05-05	13	505	NULL	NULL

Hints

1. percentile_cont(0.5) in PostgreSQL is an ordered-set aggregate (WITHIN GROUP (ORDER BY ...)); it is NOT a window function, so a correlated subquery per row is a clean way to scope it.
2. Each median's subquery should filter order_date <= current row's order_date and unit_price IS NOT NULL; add product_id = or category_id = for the product/category scopes.
3. Join order_items to orders for order_date and to products for category_id before you can group by date or category.