Answer SQL MCQs with reasoning

Answer multiple-choice questions covering SQL topics: INNER vs LEFT vs RIGHT JOIN semantics; GROUP BY and HAVING; NULL handling in comparisons and aggregates; window functions such as ROW_NUMBER and RANK; and logical query processing order. For each selected answer, briefly justify why it is correct and why at least one alternative is not.

Customer Spend, Joins, Aggregates, NULLs, and Window Ranking

Using the tables defined below, write a single SQL query that returns, for each customer located in 'New York' or 'San Francisco' who either (a) has at least 100 in total spend from completed orders, or (b) has never placed any order, the following columns:

• customer_id
• customer_name
• city
• total_completed_amount: the total revenue from that customer's orders with status = 'COMPLETED'
• completed_order_count: the number of distinct orders with status = 'COMPLETED'
• avg_completed_order_value: total_completed_amount divided by completed_order_count (NULL if the customer has no completed orders)
• spending_rank: the rank of the customer by total_completed_amount in descending order (highest spender has rank 1; ties share the same rank)

Requirements:

• Start from the customers table and join to orders and order_items so that customers with no orders are still included in the result.
• Correctly handle NULLs when computing aggregates for customers with no orders or no completed orders.
• Use GROUP BY and HAVING to filter based on the aggregated total_completed_amount and on whether the customer has any orders.
• Use a window function (such as RANK) to compute the spending_rank over the final set of customers.

Return the result ordered by total_completed_amount in descending order, and then by customer_id ascending.

Table customers

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

Sample data:

city	customer_id	customer_name
New York	1	Alice
San Francisco	2	Bob
New York	3	Carol
San Francisco	4	Dave

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL
order_date	DATE	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

customer_id	order_date	order_id	status
1	2025-05-01	101	COMPLETED
1	2025-05-10	102	CANCELLED
2	2025-05-02	103	COMPLETED
2	2025-05-15	104	COMPLETED
3	2025-05-20	105	PENDING

Table order_items

column	type	constraints
order_item_id	INT	PRIMARY KEY
order_id	INT	NOT NULL
product_name	VARCHAR(100)	NOT NULL
quantity	INT	NOT NULL
unit_price	DECIMAL(10,2)	NOT NULL

Sample data:

order_id	order_item_id	product_name	quantity	unit_price
101	1	Widget A	2	50
101	2	Widget B	1	30
102	3	Widget A	1	50
103	4	Widget C	3	20
104	5	Widget C	4	20
105	6	Widget B	1	30

Expected output

One row per customer in New York or San Francisco who either has at least 100 total spend from completed orders or has never placed any order. Shows total and count of completed orders, the average completed order value, and the rank by total spend.

avg_completed_order_value	city	completed_order_count	customer_id	customer_name	spending_rank	total_completed_amount
70	San Francisco	2	2	Bob	1	140
130	New York	1	1	Alice	2	130
NULL	San Francisco	0	4	Dave	3	0

Hints

1. Start from customers and LEFT JOIN to orders and order_items so that customers without orders are not lost.
2. Aggregate spend and counts in a GROUP BY, filter with HAVING on the aggregated values, then apply a window RANK() in an outer query.