Manipulate and merge DataFrames correctly
Company: Boston Consulting Group
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates proficiency in pandas-based data manipulation and merging, covering join validation, vectorized revenue and discount calculations, missing-value imputation, groupby/transform usage, dtype setting, and avoiding SettingWithCopy issues.
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.
Tables
customers(customer_id INT, join_date DATE, tier VARCHAR(10))
products(model_id INT, model_name VARCHAR(20), msrp DECIMAL(10,2))
orders(order_id INT, order_date DATE, customer_id INT, model_id INT, qty INT, unit_price DECIMAL(10,2), status VARCHAR(20))
Hints
- 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.
- 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.