SQL Scenario Questions for Data Analyst Interviews

SELECT SUM(order_total) AS shipped_revenue
FROM Orders
WHERE status = 'Shipped';

One aggregate, one filter. The interview point is noticing that “revenue” is ambiguous until you pin down which statuses count — a good analyst asks that question out loud before writing the query.

SELECT p.name, SUM(o.order_total) AS revenue
FROM Orders o
JOIN Products p ON p.product_id = o.product_id
WHERE o.status <> 'Cancelled'
GROUP BY p.name
ORDER BY revenue DESC
LIMIT 3;

Join to get the product name, WHERE before grouping so cancelled orders never enter the totals, then sort and cut. Putting the status filter in HAVING instead would work here but describes the wrong intent — the rows should be excluded before summing, not after.

SELECT c.tier, COUNT(*) AS orders, AVG(o.order_total) AS avg_order_value
FROM Orders o
JOIN Customers c ON c.customer_id = o.customer_id
GROUP BY c.tier
ORDER BY avg_order_value DESC;

A join plus GROUP BY on the joined column. Note that AVG is per order, not per customer — if the interviewer wants average per customer, that is a different query with a subquery or two GROUP BYs, and spotting that ambiguity scores points.

SELECT e.first_name, e.last_name
FROM Employees e
LEFT JOIN Orders o ON o.emp_id = e.emp_id
WHERE o.order_id IS NULL
ORDER BY e.first_name;

The anti-join pattern: LEFT JOIN keeps every employee, and the unmatched ones carry NULL in the right-hand columns — filtering on that NULL keeps exactly the employees with no match. NOT IN and NOT EXISTS answer it too; NOT IN needs care around NULLs.

SELECT MONTH(order_date) AS order_month, COUNT(*) AS orders
FROM Orders
GROUP BY MONTH(order_date)
ORDER BY order_month;

Grouping by a date part is the backbone of trend reporting. Remember its honest limitation: a month with zero orders produces no row, so a dashboard that must show empty months needs a calendar table to join against.

SELECT country, COUNT(*) AS customers
FROM Customers
GROUP BY country
ORDER BY customers DESC, country;

Simple, but the tie-break matters: without the second ORDER BY key, equal counts come back in unpredictable order, and a report that shuffles between runs erodes trust in the numbers.

SELECT d.dept_name, SUM(o.order_total) AS shipped_revenue
FROM Orders o
JOIN Employees e ON e.emp_id = o.emp_id
JOIN Departments d ON d.dept_id = e.dept_id
WHERE o.status = 'Shipped'
GROUP BY d.dept_name
ORDER BY shipped_revenue DESC;

A two-hop join: Orders knows the employee, the employee knows the department. In this dataset the answer is a single row — only Sales handles orders — and saying “the data shows only one department closes orders” is exactly the kind of observation interviewers want to hear.

SELECT ROUND(SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS cancelled_pct
FROM Orders;

The conditional-count trick: CASE turns each row into 1 or 0, SUM counts the ones, and dividing by COUNT(*) gives the share. The * 100.0 keeps the arithmetic in decimals — integer division would round the whole thing to zero in many databases.

SELECT order_id, order_total
FROM Orders
WHERE order_total > (SELECT AVG(order_total) FROM Orders)
ORDER BY order_total DESC;

A scalar subquery: the inner SELECT produces one number, and the outer WHERE compares every row against it. The follow-up interviewers like — “above their own customer’s average” — turns it into a correlated subquery, which is the harder cousin worth practising next.

SELECT c.company, SUM(o.order_total) AS lifetime_spend
FROM Customers c
JOIN Orders o ON o.customer_id = c.customer_id
GROUP BY c.company
ORDER BY lifetime_spend DESC
LIMIT 1;

Group, sort, take one. Worth saying in an interview: LIMIT 1 silently drops ties — if two customers tie for the top spot, only one appears. Handling ties properly is a RANK() job, which is where window functions enter.