Assess SQL joins, unions, windows, dedup, and pandas
Company: Fannie Mae
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Using the schema and sample data below, answer all sub-questions. Unless stated, assume ANSI SQL and explain any dialect-specific choices.
Schema:
- customers(customer_id PK, name)
- orders(order_id PK, customer_id FK->customers, order_date DATE, amount DECIMAL(10,2))
- order_items(item_id PK, order_id FK->orders, product_id FK->products, qty INT, price DECIMAL(10,2))
- products(product_id PK, name, category)
- web_orders(order_id, customer_id, amount)
- store_orders(order_id, customer_id, amount)
- employees(emp_id PK, name, salary INT)
Sample tables (tiny):
customers
| customer_id | name |
|------------:|-------|
| 1 | Alice |
| 2 | Bob |
| 3 | Carol |
orders
| order_id | customer_id | order_date | amount |
|---------:|------------:|-------------|---------|
| 101 | 1 | 2025-08-28 | 120.00 |
| 102 | 1 | 2025-08-30 | 80.00 |
| 103 | 2 | 2025-08-29 | 50.00 |
| 105 | 3 | 2025-08-30 | NULL |
order_items
| item_id | order_id | product_id | qty | price |
|--------:|---------:|-----------:|----:|--------|
| 1 | 101 | 10 | 1 | 120.00 |
| 2 | 102 | 11 | 2 | 40.00 |
| 3 | 103 | 12 | 1 | 50.00 |
products
| product_id | name | category |
|-----------:|-------|--------------|
| 10 | Phone | Electronics |
| 11 | Cable | Electronics |
| 12 | Book | Media |
web_orders
| order_id | customer_id | amount |
|---------:|------------:|-------:|
| 201 | 1 | 50.00 |
| 202 | 1 | 50.00 |
| 202 | 1 | 50.00 |
store_orders
| order_id | customer_id | amount |
|---------:|------------:|-------:|
| 301 | 1 | 50.00 |
| 202 | 1 | 50.00 |
| 302 | 2 | 50.00 |
employees
| emp_id | name | salary |
|------:|-------|-------:|
| 1 | Alice | 100 |
| 2 | Bob | 200 |
| 3 | Carol | 300 |
| 4 | Dave | 300 |
Tasks:
1) Aggregations and NULLs: Write queries that compute per-customer: (a) COUNT(*), COUNT(amount), SUM(amount), AVG(amount), MIN/MAX(order_date). Show the result difference caused by orders.amount being NULL for customer_id=3. Explain COUNT(column) vs COUNT(*), and how AVG ignores NULLs. Also compute total revenue from order_items using SUM(qty*price) and reconcile it to SUM(orders.amount); explain any mismatch and how to detect inconsistencies.
2) Joins: (a) INNER JOIN customers↔orders to list customers who placed orders. (b) LEFT JOIN customers↔orders to include customers with zero orders; return 0 as totals for those. (c) FULL OUTER JOIN customers↔orders and explain which rows appear only on one side. (d) CROSS JOIN products↔(SELECT DISTINCT category) to illustrate Cartesian output and how to limit it. Provide queries and one or two expected result rows to prove understanding.
3) UNION vs UNION ALL: Combine web_orders and store_orders into a single set of (order_id, customer_id, amount). (a) Using UNION ALL, count total rows and the number of duplicate order_ids. (b) Using UNION (distinct), show deduplicated rows. (c) Return only the order_ids that appear in both sources without using INTERSECT.
4) Window functions: (a) For each customer, rank their orders by amount DESC and return the top 1 per customer using ROW_NUMBER. (b) Compute a running total of amount per customer ordered by order_date; show partition boundaries. (c) For August 2025, compute each customer’s rank by monthly spend using SUM(amount) OVER(PARTITION BY customer_id) and then DENSE_RANK across customers. Explain why a window does not collapse rows like GROUP BY.
5) View vs table: Create a view top_spenders_2025_08(customer_id, total_amount) over orders. Is it updatable? Under what conditions? What happens if the base table adds a NOT NULL column or if the view definition references a column later dropped? Discuss pros/cons of views vs materialized tables for this case and how to refresh a materialized view (name your chosen RDBMS).
6) Hide duplicated rows (select-time): Return one row per distinct (order_id, customer_id, amount) from web_orders without deleting data. Show two approaches: DISTINCT and ROW_NUMBER() filtering; discuss performance and which preserves deterministic choice.
7) Remove duplicated rows (data-change): In web_orders, delete true duplicates keeping the lowest ROW_NUMBER by (order_id, customer_id, amount) and explain how you’d do this safely in a transaction with a reproducible tie-breaker. Provide the DELETE…CTE you would run.
8) LC #177 (Nth highest salary): Using employees(name, salary), write a query that returns the Nth highest distinct salary given a parameter :n. Show outputs for n=1,2,3 on the sample data and explain behavior when n exceeds the number of distinct salaries.
9) Improve SQL efficiency: Propose an indexing strategy for the above workloads (consider orders(customer_id, order_date), order_items(order_id), and covering indexes). Show how to rewrite one query to be sargable (e.g., avoid functions on indexed columns), and explain when EXISTS outperforms IN and when a window function can replace a self-join. Describe how you’d verify improvements using an execution plan and timing.
10) Python pandas merge vs join vs concat: Given DataFrames below, specify the exact pandas calls (with how=, on=, axis=, ignore_index=, validate=) to: (a) left-join spend onto cities; (b) inner-join both then right-join to keep ids only in df_b; (c) stack df_a and a new rows frame vertically; (d) align on index instead of column; (e) explain when concat vs merge is appropriate.
DataFrames:
df_a
| id | city |
|---:|------|
| 1 | SF |
| 2 | NYC |
| 3 | LA |
df_b
| id | spend |
|---:|------:|
| 2 | 80 |
| 3 | 20 |
| 4 | 10 |
df_c
| id | tag |
|---:|-----|
| 3 | vip |
Overview: This question evaluates proficiency in SQL and pandas data-manipulation techniques, including aggregations with NULL semantics, various join types, set operations and deduplication, window functions, and reconciling normalized and denormalized sources.
Customer aggregates, NULL handling, and revenue reconciliation
Using the tables below, return one combined result set for two checks:
1. result_set = 'customer_aggregates': one row per customer with COUNT(*), COUNT(amount), SUM(amount), AVG(amount), MIN(order_date), and MAX(order_date) over orders. This should show how the NULL amount for customer_id = 3 affects SUM/AVG/COUNT(amount).
2. result_set = 'revenue_reconciliation': one summary row comparing SUM(orders.amount) to SUM(order_items.qty * order_items.price), with their difference. Compute the order amount total separately from the item total so orders are not double-counted by the order_items join.
Use columns result_set, customer_id, name, order_count_all, order_count_non_null_amount, total_amount, avg_amount, first_order_date, last_order_date, sum_orders_amount, sum_items_value, and difference. Fields that do not apply to a row should be NULL.
Tables
customers(customer_id INT, name VARCHAR(50))
orders(order_id INT, customer_id INT, order_date DATE, amount DECIMAL(10,2))
order_items(item_id INT, order_id INT, product_id INT, qty INT, price DECIMAL(10,2))
products(product_id INT, name VARCHAR(100), category VARCHAR(50))
web_orders(web_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
store_orders(store_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
employees(emp_id INT, name VARCHAR(50), salary INT)
Hints
- Use COUNT(column) to show how NULL values are excluded from that count.
- Compute order-level and item-level revenue totals in separate CTEs before comparing them.
Inner, outer, and cross joins between customers, orders, and products
Using the same schema, write the INNER JOIN between customers and orders that lists customers who placed orders, returning customer_id, name, order_id, order_date, and amount. For the executable sample output, return the first two rows ordered by customer_id and order_id.
In your explanation, also describe how the result would differ for a LEFT JOIN from customers to orders, a FULL OUTER JOIN between customers and orders, and a CROSS JOIN between products and the distinct product categories.
Tables
customers(customer_id INT, name VARCHAR(50))
orders(order_id INT, customer_id INT, order_date DATE, amount DECIMAL(10,2))
order_items(item_id INT, order_id INT, product_id INT, qty INT, price DECIMAL(10,2))
products(product_id INT, name VARCHAR(100), category VARCHAR(50))
web_orders(web_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
store_orders(store_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
employees(emp_id INT, name VARCHAR(50), salary INT)
Hints
- Join customers.customer_id to orders.customer_id.
- Use an INNER JOIN when only matching customer/order pairs should appear.
UNION ALL vs UNION and finding overlapping order_ids
Using web_orders and store_orders, return the distinct combined set of (order_id, customer_id, amount) across both tables. Use UNION, not UNION ALL, so duplicate rows are removed, and order the output by order_id, customer_id, and amount.
In your explanation, also describe how you would use UNION ALL to count total combined rows and how to find order_ids that appear in both sources without using INTERSECT.
Tables
customers(customer_id INT, name VARCHAR(50))
orders(order_id INT, customer_id INT, order_date DATE, amount DECIMAL(10,2))
order_items(item_id INT, order_id INT, product_id INT, qty INT, price DECIMAL(10,2))
products(product_id INT, name VARCHAR(100), category VARCHAR(50))
web_orders(web_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
store_orders(store_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
employees(emp_id INT, name VARCHAR(50), salary INT)
Hints
- UNION removes duplicate rows; UNION ALL preserves them.
- The duplicate row in the sample is order_id 202 for customer_id 1.
Window functions: ranking, running totals, and spend ranking
Using the `orders` table, write a PostgreSQL window-function query that returns the single highest-amount non-NULL order for each customer. Rank orders per customer by `amount DESC`; if a tie occurs, use the smaller `order_id` first. Return exactly `order_id`, `customer_id`, and `amount`, ordered by `customer_id`.
Tables
customers(customer_id INT, name VARCHAR(50))
orders(order_id INT, customer_id INT, order_date DATE, amount DECIMAL(10,2))
order_items(item_id INT, order_id INT, product_id INT, qty INT, price DECIMAL(10,2))
products(product_id INT, name VARCHAR(100), category VARCHAR(50))
web_orders(web_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
store_orders(store_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
employees(emp_id INT, name VARCHAR(50), salary INT)
Hints
- Use `ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, order_id ASC)`.
- Filter the ranked CTE to `rn = 1`.
Creating a view of top spenders for August 2025
Define a view top_spenders_2025_08(customer_id, total_amount) over the orders table that summarizes each customer’s total non-NULL amount for orders in August 2025 (order_date between '2025-08-01' and '2025-08-31'). Then select from this view to see the results ordered by total_amount DESC. Discuss in words whether this view is updatable, how schema changes to the base table (e.g., adding a NOT NULL column or dropping a referenced column) might affect it, and pros/cons of using a plain view versus a materialized view for reporting.
Tables
customers(customer_id INT, name VARCHAR(50))
orders(order_id INT, customer_id INT, order_date DATE, amount DECIMAL(10,2))
order_items(item_id INT, order_id INT, product_id INT, qty INT, price DECIMAL(10,2))
products(product_id INT, name VARCHAR(100), category VARCHAR(50))
web_orders(web_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
store_orders(store_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
employees(emp_id INT, name VARCHAR(50), salary INT)
Hints
- Define the view using a GROUP BY over customer_id with a WHERE filter restricting order_date to August 2025 and excluding NULL amounts.
- Aggregating views (with GROUP BY) are generally not updatable in most RDBMSs; think about how that affects INSERT/UPDATE operations through the view.
Hiding duplicate rows from web_orders at query time
Without modifying the data in web_orders, write two queries that return one row per distinct (order_id, customer_id, amount) from web_orders: (a) using SELECT DISTINCT, and (b) using ROW_NUMBER() OVER(PARTITION BY order_id, customer_id, amount ORDER BY web_order_id) and filtering to the first occurrence. Discuss which approach is more efficient and how using ROW_NUMBER gives deterministic control over which physical row you keep when there are duplicates.
Tables
customers(customer_id INT, name VARCHAR(50))
orders(order_id INT, customer_id INT, order_date DATE, amount DECIMAL(10,2))
order_items(item_id INT, order_id INT, product_id INT, qty INT, price DECIMAL(10,2))
products(product_id INT, name VARCHAR(100), category VARCHAR(50))
web_orders(web_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
store_orders(store_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
employees(emp_id INT, name VARCHAR(50), salary INT)
Hints
- SELECT DISTINCT is the simplest way to hide duplicates when you only care about the distinct combinations of columns.
- ROW_NUMBER() OVER(PARTITION BY ...) with ORDER BY web_order_id lets you deterministically keep, for example, the earliest inserted row.
Deleting true duplicate rows from web_orders safely
Assume web_orders has a surrogate primary key web_order_id. Using the sample data (which contains duplicate logical rows for order_id=202), write a DELETE statement that removes true duplicates and keeps the row with the lowest web_order_id for each distinct (order_id, customer_id, amount). Use a CTE with ROW_NUMBER() OVER(PARTITION BY order_id, customer_id, amount ORDER BY web_order_id) and delete rows where rn > 1. Then SELECT from web_orders to show the remaining rows. Your DELETE should be safe and deterministic.
Tables
customers(customer_id INT, name VARCHAR(50))
orders(order_id INT, customer_id INT, order_date DATE, amount DECIMAL(10,2))
order_items(item_id INT, order_id INT, product_id INT, qty INT, price DECIMAL(10,2))
products(product_id INT, name VARCHAR(100), category VARCHAR(50))
web_orders(web_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
store_orders(store_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
employees(emp_id INT, name VARCHAR(50), salary INT)
Hints
- Use a CTE to assign ROW_NUMBER within each duplicate group and then delete where rn > 1.
- Always order ROW_NUMBER() by a stable surrogate key (like web_order_id) so that the surviving row is deterministic.
Nth highest distinct salary using window functions
Using the employees(emp_id, name, salary) table, write a query that can be used to retrieve the Nth highest DISTINCT salary. Implement it by assigning a dense rank over salaries ordered descending and exposing both the rank and the salary. Show the ranks and salaries for the sample data, and explain how you would filter for a specific N (e.g., N=1,2,3). Also describe what happens when N exceeds the number of distinct salaries (no row is returned).
Tables
customers(customer_id INT, name VARCHAR(50))
orders(order_id INT, customer_id INT, order_date DATE, amount DECIMAL(10,2))
order_items(item_id INT, order_id INT, product_id INT, qty INT, price DECIMAL(10,2))
products(product_id INT, name VARCHAR(100), category VARCHAR(50))
web_orders(web_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
store_orders(store_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
employees(emp_id INT, name VARCHAR(50), salary INT)
Hints
- First derive distinct salaries, then apply DENSE_RANK() OVER(ORDER BY salary DESC) to get the rank of each one.
- To get the Nth highest salary, wrap this query and filter WHERE n = :n (or a literal value like 2).
Indexing strategy and sargable predicates
Propose an indexing strategy for the workloads above, focusing on orders(customer_id, order_date) and order_items(order_id). Then, show how to rewrite a non-sargable filter into a sargable one, for example changing a function on an indexed date column into a range predicate so the index can be used. Also briefly explain when EXISTS can outperform IN and when a window function can replace a self-join. Implement your indexing proposal with CREATE INDEX statements and include one example sargable SELECT query that benefits from the indexes.
Tables
customers(customer_id INT, name VARCHAR(50))
orders(order_id INT, customer_id INT, order_date DATE, amount DECIMAL(10,2))
order_items(item_id INT, order_id INT, product_id INT, qty INT, price DECIMAL(10,2))
products(product_id INT, name VARCHAR(100), category VARCHAR(50))
web_orders(web_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
store_orders(store_order_id INT, order_id INT, customer_id INT, amount DECIMAL(10,2))
employees(emp_id INT, name VARCHAR(50), salary INT)
Hints
- Composite indexes like (customer_id, order_date) help both lookups by customer and range scans on dates when combined appropriately in predicates.
- To make a predicate sargable, avoid wrapping indexed columns in functions; instead, rewrite to a range on the raw column (e.g., order_date between start and end).
pandas merge vs join vs concat for combining DataFrames
You have three pandas DataFrames df_a(id, city), df_b(id, spend), and df_c(id, tag) with the sample data below. Write the exact pandas calls (including how=, on=, axis=, ignore_index=, validate= where relevant) to: (a) left-join spend from df_b onto city rows in df_a; (b) inner-join df_a and df_b, and also produce a right join that keeps all ids from df_b; (c) vertically stack df_a and a new_rows DataFrame with the same columns; (d) perform a join aligned on the index instead of on an explicit column; (e) briefly explain (in comments or a string) when concat is appropriate versus merge/join. Return one row per sub-task with the corresponding pandas code snippet.
Tables
df_a(id INT, city VARCHAR(10))
df_b(id INT, spend INT)
df_c(id INT, tag VARCHAR(10))
Hints
- Use merge for key-based joins and concat for stacking aligned frames.
- The validate argument documents the expected one-to-one relationship.