SQL interview questions for data analysts: 16 questions by level

This is correct because the WHERE clause filters rows to Miami before ORDER BY sorts the resulting names alphabetically. The tempting wrong choice is the Dallas filter, which would return Ben instead of Ava and Cara. The other options either place ORDER BY in the wrong position or filter on the wrong column/value, so they do not return the requested result.

The correct query uses WHERE to filter row-level values before any aggregation, so it returns order IDs 102 and 103. HAVING is for grouped results, not this simple filter, and the amount >= 100 version would also include the row with amount 100 if it existed. The wrong column in the third option changes the condition to an order ID test, which is unrelated to the requested result.

The correct query groups rows by customer_id and then sums amount within each group, producing one total per customer. The tempting wrong option with WHERE customer_id is invalid for this task because WHERE does not create groups and the condition is not a meaningful comparison. The other distractors either group by the wrong column or sum the wrong field, so they return incorrect aggregates.

The correct query uses SUM(amount) OVER (ORDER BY month) to produce a running total that grows row by row in month order. The tempting wrong option with PARTITION BY month resets the total for each month, so it does not accumulate across rows. The plain SUM without a window returns a single total for the whole table, and OVER () also returns the same grand total on every row.

This query is correct because GROUP BY dept creates department groups and HAVING COUNT(*) >= 2 filters those groups after aggregation. The tempting wrong option uses WHERE with COUNT(*), which is invalid because aggregates are evaluated after WHERE. The other options either group by the wrong column or place WHERE after GROUP BY, which is not valid SQL syntax for this task.

LEFT JOIN keeps all rows from customers and matches orders when a customer has them, so Ben and Cara still appear even if one has no matching order. INNER JOIN would drop customers with no orders. RIGHT JOIN keeps all orders instead, and CROSS JOIN would create every possible pair rather than matching by customer_id.

ORDER BY amount DESC LIMIT 2 sorts the rows from highest amount to lowest and returns only the first two rows, which are orders 102 and 103. The tempting ORDER BY amount DESC is close, but it returns all four rows instead of limiting the result set. ASC would choose the smallest amounts, and WHERE amount > 200 filters by a threshold rather than returning exactly two rows.

RANK() OVER (PARTITION BY region ORDER BY amount DESC) restarts the ranking inside each region and sorts amounts from largest to smallest, so the top sale in East and West both get rank 1. The common mistake is to omit PARTITION BY region, which would rank the whole table as one list. RANK(amount) is not valid window-function syntax, and partitioning by amount would group unrelated rows together.

The correct query groups by customer and uses MIN(order_date) to find each customer's first order, then HAVING filters on that aggregate so only customers whose first order is in 2024 remain. The tempting WHERE version is wrong because it removes 2023 rows before the minimum is calculated, which can change the first order date for customers like 10 and hide customers like 12 entirely. HAVING is the key clause here.

DENSE_RANK() assigns tied amounts the same rank and does not skip rank values, so the second-highest distinct amount in each region is returned correctly. ROW_NUMBER() would break ties arbitrarily and give only one of the 700 rows for East, which is not what the question asks. Filtering amount = 2 is unrelated, and MAX(amount) with HAVING COUNT(*) = 2 checks group size, not rank. This tests ranking semantics, not just sorting.

The condition compares each region’s grouped total against the average of all grouped totals, so the query needs a nested aggregate: one subquery computes totals per region, and the outer HAVING compares each region total to their average. SUM(amount) > AVG(amount) is a different comparison inside the same group and does not compare across regions. WHERE cannot use aggregates like SUM(amount). This is a common analyst-style grouped subquery pattern.

The date filter must be applied before the window function ranks rows, otherwise March orders can affect the per-customer result. The correct query filters February rows first, then uses ROW_NUMBER() to pick the highest amount within each customer. The second option tries to filter on order_date after the subquery but no longer has that column in scope. BETWEEN with an end date of '2024-03-01' is also a common trap because it includes only the boundary date, not the whole month in the intended way.

The correct query uses a window function with PARTITION BY customer_id, so each row is divided by the total spend of that customer while preserving the original rows. The tempting GROUP BY version changes the result set to one row per group and cannot return each order with its own percentage. PARTITION BY order_id would make each denominator just one order, and ORDER BY customer_id creates a running total across customers instead of a per-customer total.

The correct query groups by customer, computes each customer’s latest date with MAX(order_date), and filters the grouped result in HAVING so only customers whose maximum date is in March remain. The tempting WHERE order_date LIKE '2024-03%' filters rows before aggregation, which can incorrectly miss a customer whose latest order is in March but who also has earlier non-March orders. WHERE MAX(...) is invalid, and grouping by order_date changes the grain.

COUNT(DISTINCT user_id) counts each user once per date, which is what the question asks for. COUNT(user_id) would count activity rows, so user 1 on 2024-04-01 would be counted twice because they have two events. The WHERE event = 'view' option changes the metric by excluding clicks, and COUNT(DISTINCT event) counts event types, not users.

The correct solution uses a CTE to pre-aggregate by customer_id, then joins that summary back to the detail rows. This produces one customer total repeated for each order. The second option is missing GROUP BY in the CTE, so it is invalid or collapses to a single row depending on the database. The GROUP BY order-level query returns each order's own amount, not the customer total. The last option aggregates by order_id, which is the wrong grain and cannot produce customer totals.