Quick Overview

This question evaluates data-wrangling and ETL competencies including normalization of inconsistent schemas, performant joins, aggregation, and hierarchical imputation strategies in pandas, as well as handling of missing values and final schema validation.

Unify 7 tables and impute missing values

Company: Boston Consulting Group

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Online Assessment

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.

Overview: This question evaluates data-wrangling and ETL competencies including normalization of inconsistent schemas, performant joins, aggregation, and hierarchical imputation strategies in pandas, as well as handling of missing values and final schema validation.

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.

Tables

customers(customer_id INT, name VARCHAR(100))

orders(order_id INT, customer_id INT, shipper_id INT, order_date DATE)

order_items(order_id INT, product_id INT, quantity INT, unit_price DECIMAL(10,2))

products(product_id INT, product_name VARCHAR(100), category_id INT)

categories(category_id INT, category_name VARCHAR(100))

shippers(shipper_id INT, shipper_name VARCHAR(100))

payments(order_id INT, payment_status VARCHAR(20), amount DECIMAL(10,2))

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].

Tables

customers(customer_id INT, name VARCHAR(100))

orders(order_id INT, customer_id INT, shipper_id INT, order_date DATE)

order_items(order_id INT, product_id INT, quantity INT, unit_price DECIMAL(10,2))

products(product_id INT, product_name VARCHAR(100), category_id INT)

categories(category_id INT, category_name VARCHAR(100))

shippers(shipper_id INT, shipper_name VARCHAR(100))

payments(order_id INT, payment_status VARCHAR(20), amount DECIMAL(10,2))

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).

Tables

customers(customer_id INT, name VARCHAR(100))

orders(order_id INT, customer_id INT, shipper_id INT, order_date DATE)

order_items(order_id INT, product_id INT, quantity INT, unit_price DECIMAL(10,2))

products(product_id INT, product_name VARCHAR(100), category_id INT)

categories(category_id INT, category_name VARCHAR(100))

shippers(shipper_id INT, shipper_name VARCHAR(100))

payments(order_id INT, payment_status VARCHAR(20), amount DECIMAL(10,2))

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.

Loading coding console...