Data Analytics SQL for Analysts Interview Questions and Answers

SQL is the skill an analyst is most likely to be tested on live, in front of the interviewer, against a sample schema. The questions are remarkably consistent across companies: a couple of join questions, aggregation with GROUP BY and HAVING, at least one window-function problem, and a pattern classic like the second-highest salary. This page walks those questions with correct, runnable SQL and the reasoning interviewers listen for.

How to answer SQL questions

Talk through which rows the query produces before you optimize anything. Say the logical execution order — FROM and JOIN, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY — because most SQL bugs come from misunderstanding that order, not from a typo.

Q1. Explain the join types with an example.

An INNER JOIN returns only rows with a match in both tables. A LEFT JOIN returns all rows from the left table plus matches from the right, with NULLs where there is no match. RIGHT JOIN is the mirror image, and FULL OUTER JOIN keeps unmatched rows from both sides.

The interview-favorite use of LEFT JOIN is finding rows with no match — customers who never ordered — by joining and filtering for NULL on the right side.

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;   -- customers with zero orders

Interview note: Trap: "put the orders filter in WHERE and it still works?" If you filter o.status = 'shipped' in WHERE on a LEFT JOIN, you silently turn it into an INNER JOIN. Put such conditions in the ON clause.

Q2. What is the difference between WHERE and HAVING?

WHERE filters rows before aggregation; HAVING filters groups after aggregation. Aggregate functions like COUNT and SUM are allowed in HAVING but not in WHERE.

To list customers with more than five orders, the count only exists after grouping, so it must live in HAVING. Filtering by a raw column (say a date range) should go in WHERE so fewer rows enter the grouping — that is both correct and faster.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2026-01-01'   -- row filter, before grouping
GROUP BY customer_id
HAVING COUNT(*) > 5;               -- group filter, after aggregation

Interview note: Follow-up: "can you use a column alias in HAVING?" It depends on the database; standard SQL evaluates HAVING before SELECT aliases exist, so repeating the aggregate is the portable choice.

Q3. Find the second-highest salary.

The cleanest modern answer uses DENSE_RANK to rank salaries and select rank 2, which correctly handles ties. A subquery with MAX also works but breaks when there are duplicate top salaries.

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

The subquery alternative — SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees) — is fine, but the interviewer usually asks about ties. DENSE_RANK gives the second-highest distinct salary even if several employees share the top value.

Interview note: Trap: "use RANK instead of DENSE_RANK?" RANK skips numbers after ties (1,1,3), so if two people tie for first, RANK never returns 2. DENSE_RANK (1,1,2) is safer for the Nth-value pattern.

Q4. What are window functions, and how are they different from GROUP BY?

A window function computes a value across a set of rows related to the current row, but unlike GROUP BY it does not collapse those rows — every original row is preserved. This makes it ideal for rankings, running totals and comparisons to a group value.

The killer example is showing each employee's salary alongside their department average without losing the individual rows:

SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

A GROUP BY would return one row per department; the window function keeps all employees and attaches the department average to each.

Interview note: Follow-up: "what is the difference between PARTITION BY and GROUP BY?" PARTITION BY defines the window without collapsing rows; GROUP BY collapses each group into a single output row.

Q5. How do you find and remove duplicate rows?

Use ROW_NUMBER partitioned by the columns that define a duplicate, ordered by a tiebreaker, then keep row number 1. Rows numbered greater than 1 are the duplicates to remove.

WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY email ORDER BY created_at
         ) AS rn
  FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

This keeps the earliest record per email and deletes later duplicates. Interviewers like this because it shows you can both detect duplicates (GROUP BY email HAVING COUNT(*) > 1) and resolve them deterministically.

Interview note: Trap: "just use DISTINCT?" DISTINCT removes fully identical rows in a SELECT but cannot delete duplicates in place or keep a chosen survivor when only some columns match.

Q6. What is the difference between UNION and UNION ALL?

UNION combines two result sets and removes duplicate rows, which requires a sort or hash step. UNION ALL concatenates them and keeps duplicates, so it is faster. Use UNION ALL unless you specifically need deduplication.

Choosing UNION ALL when you know the sets are disjoint is a small performance signal interviewers notice — an unnecessary UNION pays for a dedup that changes nothing.

Interview note: Follow-up: "do the queries need matching columns?" Yes — same number of columns and compatible types, in the same order.

Q7. How do NULLs behave in SQL, and what surprises people?

NULL means unknown, so comparisons with NULL return unknown, not true. x = NULL is never true; you must use IS NULL. Aggregates like COUNT(column) skip NULLs, and NULLs also drop out of most WHERE conditions.

The classic gotcha: WHERE status != 'active' silently excludes rows where status is NULL, because NULL != 'active' is unknown, not true. Wrapping with OR status IS NULL fixes it. This kind of NULL awareness is exactly what an analyst is trusted to get right.

Interview note: Trap: "COUNT() vs COUNT(column)?" COUNT() counts rows including NULLs; COUNT(column) counts only non-NULL values in that column.

Q8. Write a running total of daily revenue.

Use SUM as a window function ordered by date. The frame runs from the first row to the current row, producing a cumulative total.

SELECT order_date,
       daily_revenue,
       SUM(daily_revenue) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM daily_sales
ORDER BY order_date;

Running totals, moving averages and period-over-period changes (using LAG) are staple analyst tasks, and reaching for a window function rather than a correlated subquery is the fluent answer.

Interview note: Follow-up: "how would you show month-over-month growth?" Use LAG(revenue) OVER (ORDER BY month) and compute the difference or ratio against the current row.

Q9. How do you approach a slow query?

Look at the execution plan first, confirm the filtered and joined columns are indexed, avoid functions on indexed columns in WHERE (which prevent index use), select only needed columns, and reduce rows early with selective WHERE clauses before joins and aggregation.

For an analyst, the most common self-inflicted slowdown is WHERE YEAR(order_date) = 2026, which forces a full scan; rewriting as a range order_date >= '2026-01-01' AND order_date < '2027-01-01' lets the index work. Knowing that indexes speed reads but cost write and storage overhead rounds out the answer.

Interview note: Trap: "add indexes to every column?" No — each index slows inserts and updates and consumes space. Index the columns you actually filter, join and sort on.

What interviewers really test

SQL rounds reward the analyst who can predict exactly which rows a query returns and reach for the right tool — a window function over a self-join, a LEFT JOIN with an IS NULL filter over a NOT IN. Practice by writing complete queries against a small schema until joins, GROUP BY and window functions are automatic. Pair this page with the data cleaning questions, which lean heavily on SQL deduplication and NULL handling, and the experienced analyst set for how these queries scale up. The SQL learning path and a live mock interview will make the syntax disappear so you can focus on the logic.