Answer SQL MCQs with reasoning
Company: Akuna Capital
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
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.
Overview: This question evaluates proficiency in SQL query semantics and reasoning about relational operations—specifically INNER, LEFT, and RIGHT JOIN behavior; GROUP BY and HAVING usage; NULL handling in comparisons and aggregates; window functions such as ROW_NUMBER and RANK; and the logical query processing order—within the Data Manipulation (SQL/Python) domain. It is commonly asked to assess the ability to interpret and justify query outcomes versus alternative answers, testing both conceptual understanding of relational theory and practical application of SQL query behavior.
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.
Tables
customers(customer_id INT, customer_name VARCHAR(50), city VARCHAR(50))
orders(order_id INT, customer_id INT, order_date DATE, status VARCHAR(20))
order_items(order_item_id INT, order_id INT, product_name VARCHAR(100), quantity INT, unit_price DECIMAL(10,2))
Hints
- Start from customers and LEFT JOIN to orders and order_items so that customers without orders are not lost.
- Aggregate spend and counts in a GROUP BY, filter with HAVING on the aggregated values, then apply a window RANK() in an outer query.