Coding questions · Fannie Mae

Assess SQL joins, unions, windows, dedup, and pandas

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.

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:

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

Customer aggregates, NULL handling, and revenue reconciliation

Using the tables below, return one combined result set for two checks:

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

Table `customers`

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

Sample data:

customer_id	name
1	Alice
2	Bob
3	Carol

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL, FOREIGN KEY REFERENCES customers(customer_id)
order_date	DATE	NOT NULL
amount	DECIMAL(10,2)

Sample data:

amount	customer_id	order_date	order_id
120	1	2025-08-28	101
80	1	2025-08-30	102
60	2	2025-08-29	103
NULL	3	2025-08-30	105

Table `order_items`

column	type	constraints
item_id	INT	PRIMARY KEY
order_id	INT	NOT NULL, FOREIGN KEY REFERENCES orders(order_id)
product_id	INT	NOT NULL
qty	INT	NOT NULL
price	DECIMAL(10,2)	NOT NULL

Sample data:

item_id	order_id	price	product_id	qty
1	101	120	10	1
2	102	40	11	2
3	103	50	12	1

Table `products`

column	type	constraints
product_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	name	product_id
Electronics	Phone	10
Electronics	Cable	11
Media	Book	12

Table `web_orders`

column	type	constraints
web_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	web_order_id
50	1	201	1
50	1	202	2
50	1	202	3

Table `store_orders`

column	type	constraints
store_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	store_order_id
50	1	301	1
50	1	202	2
50	2	302	3

Table `employees`

column	type	constraints
emp_id	INT	PRIMARY KEY
name	VARCHAR(50)	NOT NULL
salary	INT	NOT NULL

Sample data:

emp_id	name	salary
1	Alice	100
2	Bob	200
3	Carol	300
4	Dave	300

Expected output

One combined result set: per-customer order aggregates followed by a revenue reconciliation row. The reconciliation computes SUM(orders.amount) separately from SUM(order_items.qty * order_items.price) to avoid double-counting order amounts across item rows.

avg_amount	customer_id	difference	first_order_date	last_order_date	name	order_count_all	order_count_non_null_amount	result_set	sum_items_value	sum_orders_amount	total_amount
100	1	NULL	2025-08-28	2025-08-30	Alice	2	2	customer_aggregates	NULL	NULL	200
60	2	NULL	2025-08-29	2025-08-29	Bob	1	1	customer_aggregates	NULL	NULL	60
NULL	3	NULL	2025-08-30	2025-08-30	Carol	1	0	customer_aggregates	NULL	NULL	NULL
NULL	NULL	10	NULL	NULL	NULL	NULL	NULL	revenue_reconciliation	250	260	NULL

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.
Use typed NULLs when combining detail rows and summary rows with UNION ALL.

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.

Table `customers`

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

Sample data:

customer_id	name
1	Alice
2	Bob
3	Carol

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL, FOREIGN KEY REFERENCES customers(customer_id)
order_date	DATE	NOT NULL
amount	DECIMAL(10,2)

Sample data:

amount	customer_id	order_date	order_id
120	1	2025-08-28	101
80	1	2025-08-30	102
60	2	2025-08-29	103
NULL	3	2025-08-30	105

Table `order_items`

column	type	constraints
item_id	INT	PRIMARY KEY
order_id	INT	NOT NULL, FOREIGN KEY REFERENCES orders(order_id)
product_id	INT	NOT NULL
qty	INT	NOT NULL
price	DECIMAL(10,2)	NOT NULL

Sample data:

item_id	order_id	price	product_id	qty
1	101	120	10	1
2	102	40	11	2
3	103	50	12	1

Table `products`

column	type	constraints
product_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	name	product_id
Electronics	Phone	10
Electronics	Cable	11
Media	Book	12

Table `web_orders`

column	type	constraints
web_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	web_order_id
50	1	201	1
50	1	202	2
50	1	202	3

Table `store_orders`

column	type	constraints
store_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	store_order_id
50	1	301	1
50	1	202	2
50	2	302	3

Table `employees`

column	type	constraints
emp_id	INT	PRIMARY KEY
name	VARCHAR(50)	NOT NULL
salary	INT	NOT NULL

Sample data:

emp_id	name	salary
1	Alice	100
2	Bob	200
3	Carol	300
4	Dave	300

Expected output

The INNER JOIN query returns one row per order with the customer attached; all customers currently have at least one order, so all 4 orders appear. Below are two example rows from the INNER JOIN result.

amount	customer_id	name	order_date	order_id
120	1	Alice	2025-08-28	101
80	1	Alice	2025-08-30	102

Hints

Join customers.customer_id to orders.customer_id.
Use an INNER JOIN when only matching customer/order pairs should appear.
Order deterministically before limiting the sample rows.

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.

Table `customers`

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

Sample data:

customer_id	name
1	Alice
2	Bob
3	Carol

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL, FOREIGN KEY REFERENCES customers(customer_id)
order_date	DATE	NOT NULL
amount	DECIMAL(10,2)

Sample data:

amount	customer_id	order_date	order_id
120	1	2025-08-28	101
80	1	2025-08-30	102
60	2	2025-08-29	103
NULL	3	2025-08-30	105

Table `order_items`

column	type	constraints
item_id	INT	PRIMARY KEY
order_id	INT	NOT NULL, FOREIGN KEY REFERENCES orders(order_id)
product_id	INT	NOT NULL
qty	INT	NOT NULL
price	DECIMAL(10,2)	NOT NULL

Sample data:

item_id	order_id	price	product_id	qty
1	101	120	10	1
2	102	40	11	2
3	103	50	12	1

Table `products`

column	type	constraints
product_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	name	product_id
Electronics	Phone	10
Electronics	Cable	11
Media	Book	12

Table `web_orders`

column	type	constraints
web_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	web_order_id
50	1	201	1
50	1	202	2
50	1	202	3

Table `store_orders`

column	type	constraints
store_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	store_order_id
50	1	301	1
50	1	202	2
50	2	302	3

Table `employees`

column	type	constraints
emp_id	INT	PRIMARY KEY
name	VARCHAR(50)	NOT NULL
salary	INT	NOT NULL

Sample data:

emp_id	name	salary
1	Alice	100
2	Bob	200
3	Carol	300
4	Dave	300

Expected output

The UNION (distinct) query returns one row per unique (order_id, customer_id, amount) across web_orders and store_orders. With the sample data, there are four distinct rows.

amount	customer_id	order_id
50	1	201
50	1	202
50	1	301
50	2	302

Hints

UNION removes duplicate rows; UNION ALL preserves them.
The duplicate row in the sample is order_id 202 for customer_id 1.
Use an inner join between the two source tables to find overlapping order_ids without INTERSECT.

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.

Table `customers`

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

Sample data:

customer_id	name
1	Alice
2	Bob
3	Carol

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL, FOREIGN KEY REFERENCES customers(customer_id)
order_date	DATE	NOT NULL
amount	DECIMAL(10,2)

Sample data:

amount	customer_id	order_date	order_id
120	1	2025-08-28	101
80	1	2025-08-30	102
60	2	2025-08-29	103
NULL	3	2025-08-30	105

Table `order_items`

column	type	constraints
item_id	INT	PRIMARY KEY
order_id	INT	NOT NULL, FOREIGN KEY REFERENCES orders(order_id)
product_id	INT	NOT NULL
qty	INT	NOT NULL
price	DECIMAL(10,2)	NOT NULL

Sample data:

item_id	order_id	price	product_id	qty
1	101	120	10	1
2	102	40	11	2
3	103	50	12	1

Table `products`

column	type	constraints
product_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	name	product_id
Electronics	Phone	10
Electronics	Cable	11
Media	Book	12

Table `web_orders`

column	type	constraints
web_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	web_order_id
50	1	201	1
50	1	202	2
50	1	202	3

Table `store_orders`

column	type	constraints
store_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	store_order_id
50	1	301	1
50	1	202	2
50	2	302	3

Table `employees`

column	type	constraints
emp_id	INT	PRIMARY KEY
name	VARCHAR(50)	NOT NULL
salary	INT	NOT NULL

Sample data:

emp_id	name	salary
1	Alice	100
2	Bob	200
3	Carol	300
4	Dave	300

Expected output

For part (a), the top-order-per-customer query returns one row per customer with the highest non-NULL order amount. With the sample data, customer 1's top order is 120.00 (order_id=101) and customer 2's top order is 60.00 (order_id=103); customer 3 is ignored because their only order has NULL amount.

amount	customer_id	order_id
120	1	101
60	2	103

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.

Table `customers`

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

Sample data:

customer_id	name
1	Alice
2	Bob
3	Carol

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL, FOREIGN KEY REFERENCES customers(customer_id)
order_date	DATE	NOT NULL
amount	DECIMAL(10,2)

Sample data:

amount	customer_id	order_date	order_id
120	1	2025-08-28	101
80	1	2025-08-30	102
60	2	2025-08-29	103
NULL	3	2025-08-30	105

Table `order_items`

column	type	constraints
item_id	INT	PRIMARY KEY
order_id	INT	NOT NULL, FOREIGN KEY REFERENCES orders(order_id)
product_id	INT	NOT NULL
qty	INT	NOT NULL
price	DECIMAL(10,2)	NOT NULL

Sample data:

item_id	order_id	price	product_id	qty
1	101	120	10	1
2	102	40	11	2
3	103	50	12	1

Table `products`

column	type	constraints
product_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	name	product_id
Electronics	Phone	10
Electronics	Cable	11
Media	Book	12

Table `web_orders`

column	type	constraints
web_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	web_order_id
50	1	201	1
50	1	202	2
50	1	202	3

Table `store_orders`

column	type	constraints
store_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	store_order_id
50	1	301	1
50	1	202	2
50	2	302	3

Table `employees`

column	type	constraints
emp_id	INT	PRIMARY KEY
name	VARCHAR(50)	NOT NULL
salary	INT	NOT NULL

Sample data:

emp_id	name	salary
1	Alice	100
2	Bob	200
3	Carol	300
4	Dave	300

Expected output

The SELECT from top_spenders_2025_08 returns one row per customer who has non-NULL order amounts in August 2025, with their total spend. With the sample data, Alice has 200.00 and Bob has 60.00; Carol's NULL order is ignored.

customer_id	total_amount
1	200
2	60

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.

Table `customers`

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

Sample data:

customer_id	name
1	Alice
2	Bob
3	Carol

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL, FOREIGN KEY REFERENCES customers(customer_id)
order_date	DATE	NOT NULL
amount	DECIMAL(10,2)

Sample data:

amount	customer_id	order_date	order_id
120	1	2025-08-28	101
80	1	2025-08-30	102
60	2	2025-08-29	103
NULL	3	2025-08-30	105

Table `order_items`

column	type	constraints
item_id	INT	PRIMARY KEY
order_id	INT	NOT NULL, FOREIGN KEY REFERENCES orders(order_id)
product_id	INT	NOT NULL
qty	INT	NOT NULL
price	DECIMAL(10,2)	NOT NULL

Sample data:

item_id	order_id	price	product_id	qty
1	101	120	10	1
2	102	40	11	2
3	103	50	12	1

Table `products`

column	type	constraints
product_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	name	product_id
Electronics	Phone	10
Electronics	Cable	11
Media	Book	12

Table `web_orders`

column	type	constraints
web_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	web_order_id
50	1	201	1
50	1	202	2
50	1	202	3

Table `store_orders`

column	type	constraints
store_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	store_order_id
50	1	301	1
50	1	202	2
50	2	302	3

Table `employees`

column	type	constraints
emp_id	INT	PRIMARY KEY
name	VARCHAR(50)	NOT NULL
salary	INT	NOT NULL

Sample data:

emp_id	name	salary
1	Alice	100
2	Bob	200
3	Carol	300
4	Dave	300

Expected output

Both approaches should return the same deduplicated set of (order_id, customer_id, amount) from web_orders. With the sample data there are two distinct logical orders: order_id 201 and 202.

amount	customer_id	order_id
50	1	201
50	1	202

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.

Table `customers`

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

Sample data:

customer_id	name
1	Alice
2	Bob
3	Carol

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL, FOREIGN KEY REFERENCES customers(customer_id)
order_date	DATE	NOT NULL
amount	DECIMAL(10,2)

Sample data:

amount	customer_id	order_date	order_id
120	1	2025-08-28	101
80	1	2025-08-30	102
60	2	2025-08-29	103
NULL	3	2025-08-30	105

Table `order_items`

column	type	constraints
item_id	INT	PRIMARY KEY
order_id	INT	NOT NULL, FOREIGN KEY REFERENCES orders(order_id)
product_id	INT	NOT NULL
qty	INT	NOT NULL
price	DECIMAL(10,2)	NOT NULL

Sample data:

item_id	order_id	price	product_id	qty
1	101	120	10	1
2	102	40	11	2
3	103	50	12	1

Table `products`

column	type	constraints
product_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	name	product_id
Electronics	Phone	10
Electronics	Cable	11
Media	Book	12

Table `web_orders`

column	type	constraints
web_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	web_order_id
50	1	201	1
50	1	202	2
50	1	202	3

Table `store_orders`

column	type	constraints
store_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	store_order_id
50	1	301	1
50	1	202	2
50	2	302	3

Table `employees`

column	type	constraints
emp_id	INT	PRIMARY KEY
name	VARCHAR(50)	NOT NULL
salary	INT	NOT NULL

Sample data:

emp_id	name	salary
1	Alice	100
2	Bob	200
3	Carol	300
4	Dave	300

Expected output

After deleting true duplicates and keeping the row with the smallest web_order_id per (order_id, customer_id, amount), web_orders should contain only two rows: one for order_id 201 and one for a single copy of order_id 202.

amount	customer_id	order_id	web_order_id
50	1	201	1
50	1	202	2

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

Table `customers`

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

Sample data:

customer_id	name
1	Alice
2	Bob
3	Carol

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL, FOREIGN KEY REFERENCES customers(customer_id)
order_date	DATE	NOT NULL
amount	DECIMAL(10,2)

Sample data:

amount	customer_id	order_date	order_id
120	1	2025-08-28	101
80	1	2025-08-30	102
60	2	2025-08-29	103
NULL	3	2025-08-30	105

Table `order_items`

column	type	constraints
item_id	INT	PRIMARY KEY
order_id	INT	NOT NULL, FOREIGN KEY REFERENCES orders(order_id)
product_id	INT	NOT NULL
qty	INT	NOT NULL
price	DECIMAL(10,2)	NOT NULL

Sample data:

item_id	order_id	price	product_id	qty
1	101	120	10	1
2	102	40	11	2
3	103	50	12	1

Table `products`

column	type	constraints
product_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	name	product_id
Electronics	Phone	10
Electronics	Cable	11
Media	Book	12

Table `web_orders`

column	type	constraints
web_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	web_order_id
50	1	201	1
50	1	202	2
50	1	202	3

Table `store_orders`

column	type	constraints
store_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	store_order_id
50	1	301	1
50	1	202	2
50	2	302	3

Table `employees`

column	type	constraints
emp_id	INT	PRIMARY KEY
name	VARCHAR(50)	NOT NULL
salary	INT	NOT NULL

Sample data:

emp_id	name	salary
1	Alice	100
2	Bob	200
3	Carol	300
4	Dave	300

Expected output

The query should return each distinct salary with its dense rank when ordered from highest to lowest. With the sample data, the 1st highest salary is 300, 2nd is 200, and 3rd is 100.

n	salary
1	300
2	200
3	100

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.

Table `customers`

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

Sample data:

customer_id	name
1	Alice
2	Bob
3	Carol

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL, FOREIGN KEY REFERENCES customers(customer_id)
order_date	DATE	NOT NULL
amount	DECIMAL(10,2)

Sample data:

amount	customer_id	order_date	order_id
120	1	2025-08-28	101
80	1	2025-08-30	102
60	2	2025-08-29	103
NULL	3	2025-08-30	105

Table `order_items`

column	type	constraints
item_id	INT	PRIMARY KEY
order_id	INT	NOT NULL, FOREIGN KEY REFERENCES orders(order_id)
product_id	INT	NOT NULL
qty	INT	NOT NULL
price	DECIMAL(10,2)	NOT NULL

Sample data:

item_id	order_id	price	product_id	qty
1	101	120	10	1
2	102	40	11	2
3	103	50	12	1

Table `products`

column	type	constraints
product_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	name	product_id
Electronics	Phone	10
Electronics	Cable	11
Media	Book	12

Table `web_orders`

column	type	constraints
web_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	web_order_id
50	1	201	1
50	1	202	2
50	1	202	3

Table `store_orders`

column	type	constraints
store_order_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	customer_id	order_id	store_order_id
50	1	301	1
50	1	202	2
50	2	302	3

Table `employees`

column	type	constraints
emp_id	INT	PRIMARY KEY
name	VARCHAR(50)	NOT NULL
salary	INT	NOT NULL

Sample data:

emp_id	name	salary
1	Alice	100
2	Bob	200
3	Carol	300
4	Dave	300

Expected output

After creating the suggested indexes, the sargable SELECT query returns all orders on 2025-08-30. With the sample data, there are two such orders (order_ids 102 and 105).

amount	customer_id	order_date	order_id
80	1	2025-08-30	102
NULL	3	2025-08-30	105

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.

Table `df_a`

column	type	constraints
id	INT	PRIMARY KEY
city	VARCHAR(10)	NOT NULL

Sample data:

city	id
SF	1
NYC	2
LA	3

Table `df_b`

column	type	constraints
id	INT	PRIMARY KEY
spend	INT	NOT NULL

Sample data:

id	spend
2	80
3	20
4	10

Table `df_c`

column	type	constraints
id	INT	PRIMARY KEY
tag	VARCHAR(10)	NOT NULL

Sample data:

id	tag
3	vip

Expected output

The query returns one row per pandas task (a–e), with a task label and a string containing the appropriate pandas code snippet to perform the requested merge/join/concat operation.

pandas_code	task
df_a.merge(df_b, how='left', on='id', validate='one_to_one')	a
inner_join = df_a.merge(df_b, how='inner', on='id', validate='one_to_one'); right_join = df_a.merge(df_b, how='right', on='id', validate='one_to_one')	b
result = pd.concat([df_a, new_rows], axis=0, ignore_index=True)	c
df_a.set_index('id').join(df_c.set_index('id'), how='left', validate='one_to_one')	d
Use merge/join to combine columns based on key relationships; use concat to stack or align entire DataFrames along rows or columns when their schemas are compatible.	e

Hints

Use merge for key-based joins and concat for stacking aligned frames.
The validate argument documents the expected one-to-one relationship.
When returning code snippets from SQL, keep table-like identifiers inside string literals from being rewritten by the SQL engine.

Customer aggregates, NULL handling, and revenue reconciliation

Table customers

Table orders

Table order_items

Table products

Table web_orders

Table store_orders

Table employees

Expected output

Hints

Inner, outer, and cross joins between customers, orders, and products

Table customers

Table orders

Table order_items

Table products

Table web_orders

Table store_orders

Table employees

Expected output

Hints

UNION ALL vs UNION and finding overlapping order_ids

Table customers

Table orders

Table order_items

Table products

Table web_orders

Table store_orders

Table employees

Expected output

Hints

Window functions: ranking, running totals, and spend ranking

Table customers

Table orders

Table order_items

Table products

Table web_orders

Table store_orders

Table employees

Expected output

Hints

Creating a view of top spenders for August 2025

Table customers

Table orders

Table order_items

Table products

Table web_orders

Table store_orders

Table employees

Expected output

Hints

Hiding duplicate rows from web_orders at query time

Table customers

Table orders

Table order_items

Table products

Table web_orders

Table store_orders

Table employees

Expected output

Hints

Deleting true duplicate rows from web_orders safely

Table customers

Table orders

Table order_items

Table products

Table web_orders

Table store_orders

Table employees

Expected output

Hints

Nth highest distinct salary using window functions

Table customers

Table orders

Table order_items

Table products

Table web_orders

Table store_orders

Table employees

Expected output

Hints

Table `customers`

Table `orders`

Table `order_items`

Table `products`

Table `web_orders`

Table `store_orders`

Table `employees`

Table `customers`

Table `orders`

Table `order_items`

Table `products`

Table `web_orders`

Table `store_orders`

Table `employees`

Table `customers`

Table `orders`

Table `order_items`

Table `products`

Table `web_orders`

Table `store_orders`

Table `employees`

Table `customers`

Table `orders`

Table `order_items`

Table `products`

Table `web_orders`

Table `store_orders`

Table `employees`

Table `customers`

Table `orders`

Table `order_items`

Table `products`

Table `web_orders`

Table `store_orders`

Table `employees`

Table `customers`

Table `orders`

Table `order_items`

Table `products`

Table `web_orders`

Table `store_orders`

Table `employees`

Table `customers`

Table `orders`

Table `order_items`

Table `products`

Table `web_orders`

Table `store_orders`

Table `employees`

Table `customers`

Table `orders`

Table `order_items`

Table `products`

Table `web_orders`

Table `store_orders`

Table `employees`

Table `customers`

Table `orders`

Table `order_items`

Table `products`

Table `web_orders`

Table `store_orders`

Table `employees`

Table `df_a`

Table `df_b`

Table `df_c`