Manipulate and merge DataFrames correctly

Given three pandas DataFrames:

customers
customer_id, join_date, tier
101, 2025-01-02, gold
102, 2025-02-10, silver
103, 2025-03-05, gold

products
model_id, model_name, msrp
1, Sedan, 20000
2, SUV, 30000

orders
order_id, order_date, customer_id, model_id, qty, unit_price, status
1, 2025-08-30, 102, 2, 1, 30000, completed
2, 2025-09-01, 103, 2, 2, 29000, completed
3, 2025-08-26, 101, 1, 1, 19500, returned

Tasks: (a) Filter to completed orders, then add revenue = qty*unit_price and drop status; (b) merge orders with products and customers using keys (order->products one-to-one, order->customers many-to-one) and enforce merge validation to catch duplicates; (c) compute discount_pct = clip(1 - unit_price/msrp, lower=0, upper=0.5) and impute missing msrp with the median per model_name; (d) add first_purchase_date per customer via groupby/transform, then keep only each customer’s first purchase per model; (e) ensure no SettingWithCopy warnings and set dtypes (categorical for tier and model_name; datetime for dates); (f) return columns [customer_id, model_name, revenue, discount_pct, first_purchase_date] sorted by revenue desc. Provide idiomatic, vectorized pandas code that is idempotent.

Compute customer revenue and discounts from car orders

You are given three tables describing a car dealership: customers, products, and orders.

customers(customer_id PK, join_date, tier)
products(model_id PK, model_name, msrp)   -- msrp may be NULL
orders(order_id PK, order_date, customer_id, model_id, qty, unit_price, status)

Each order references exactly one product (orders.model_id = products.model_id) and exactly one customer (orders.customer_id = customers.customer_id). Note that several products can share the same model_name (e.g. multiple trims of an "SUV"), and a product's msrp may be missing (NULL).

Write one PostgreSQL query that produces, for each customer's first completed purchase of each model, the revenue and a clipped discount percentage. Specifically:

1. Filter to orders whose status = 'completed' only. All later steps use only these orders.
2. Revenue: revenue = qty * unit_price.
3. Joins: join the completed orders to products on model_id and to customers on customer_id.
4. Imputed MSRP: if a product's msrp is NULL, replace it with the median msrp of the other (non-NULL) products that share the same model_name; otherwise keep the original msrp. Use PERCENTILE_CONT(0.5) for the median.
5. Discount: using the (possibly imputed) MSRP, compute 1 - unit_price / msrp, then clip it to the range [0, 0.5] (values below 0 become 0, values above 0.5 become 0.5) and round to 4 decimal places. Call this discount_pct.
6. first_purchase_date: for each customer, the earliest order_date across all of that customer's completed orders (regardless of model).
7. One row per (customer, model): keep only each customer's earliest completed order for each (customer_id, model_id) pair (break date ties however you like; the sample data has no ties).
8. Output: return columns customer_id, model_name, revenue, discount_pct, first_purchase_date, ordered by revenue descending (with customer_id, then model_name ascending as deterministic tie-breakers).

Use window functions where appropriate.

Table customers

column	type	constraints
customer_id	INT	PRIMARY KEY, NOT NULL
join_date	DATE	NOT NULL
tier	VARCHAR(10)	NOT NULL

Sample data:

customer_id	join_date	tier
101	2025-01-02	gold
102	2025-02-10	silver
103	2025-03-05	gold

Table products

column	type	constraints
model_id	INT	PRIMARY KEY, NOT NULL
model_name	VARCHAR(20)	NOT NULL
msrp	DECIMAL(10,2)

Sample data:

model_id	model_name	msrp
1	Sedan	20000
2	SUV	30000
3	SUV	40000
4	SUV	NULL

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
order_date	DATE	NOT NULL
customer_id	INT	NOT NULL
model_id	INT	NOT NULL
qty	INT	NOT NULL
unit_price	DECIMAL(10,2)	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

customer_id	model_id	order_date	order_id	qty	status	unit_price
102	2	2025-08-30	1	1	completed	30000
103	2	2025-09-01	2	2	completed	29000
101	1	2025-08-26	3	1	returned	19500
103	2	2025-08-20	4	1	completed	28000
103	4	2025-09-05	5	1	completed	25000
101	1	2025-08-15	6	1	completed	12000

Expected output

One row per customer's first completed purchase of each model, with revenue, a clipped/rounded discount_pct (using median-imputed MSRP for the NULL-msrp SUV), and the customer's overall first_purchase_date, sorted by revenue descending.

customer_id	discount_pct	first_purchase_date	model_name	revenue
102	0.0000	2025-08-30	SUV	30000.00
103	0.0667	2025-08-20	SUV	28000.00
103	0.2857	2025-08-20	SUV	25000.00
101	0.4000	2025-08-15	Sedan	12000.00

Hints

1. PostgreSQL does not allow PERCENTILE_CONT(...) OVER (...). Compute the per-model_name median in a separate GROUP BY CTE (filtering out NULL msrp), then join it back and COALESCE it into the missing values.
2. Clip a value into [0, 0.5] with GREATEST(LEAST(x, 0.5), 0), and remember ROUND(x, 4) in Postgres needs x to be numeric — cast with ::numeric. Use NULLIF(msrp, 0) to avoid divide-by-zero.
3. Use two windows: MIN(order_date) OVER (PARTITION BY customer_id) for the customer's overall first purchase date, and ROW_NUMBER() OVER (PARTITION BY customer_id, model_id ORDER BY order_date) to keep only the first purchase per model.