SQL / DATA ANALYST INTERVIEWS / 2026 EDITION
Top 30 SQL Interview Questions, Answered Without the Bluff
No padded theory, no copy-pasted definitions. These are the 30 questions that actually decide Data Analyst and Business Analyst interviews — with the exact answer, the query, and the mistake most candidates make on each one.
SELECT confidence
FROM practice
WHERE consistency = true
AND excuses = false;
Jump to a section
Every answer below is written the way you'd say it out loud in an interview — then backed by the actual SQL so you can verify it, not just recite it. Tap any question to expand it.
SQL Fundamentals
QUESTIONS 1–6
01What's the actual difference between WHERE and HAVING?+
WHERE filters individual rows before any grouping happens. HAVING filters groups after GROUP BY has aggregated them. If your condition references an aggregate function like SUM() or COUNT(), it has to be in HAVING — the engine hasn't computed that aggregate yet at the WHERE stage.
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE order_status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) > 5;WHERE COUNT(*) > 5 and the query fails. Saying why it fails — not just that it does — is what separates a pass from a maybe.02DELETE vs TRUNCATE vs DROP — when do you use each?+
DELETE removes rows one at a time, is logged, can be filtered with WHERE, and can be rolled back. TRUNCATE deallocates all rows at once, resets identity columns, is minimally logged, and is much faster — but it's all-or-nothing, no WHERE clause. DROP removes the entire table structure, not just the data.
03UNION vs UNION ALL — what's the real cost difference?+
Both stack the results of two queries with matching column structures. UNION removes duplicates, which means the engine has to sort and compare every row — expensive at scale. UNION ALL keeps every row including duplicates and skips that step entirely.
UNION ALL unless you specifically need deduplication. This is a common performance question disguised as a syntax question.04CHAR vs VARCHAR — does it actually matter?+
CHAR(n) is fixed-length — it always stores n characters, padding shorter values with spaces. VARCHAR(n) is variable-length and only stores what you give it, plus a small length header. Use CHAR for values that are genuinely fixed-width (like a 2-letter country code); use VARCHAR for everything else. CHAR can be marginally faster for fixed-width lookups, but the storage savings from VARCHAR almost always win in analytics tables.
05Primary key vs unique key — aren't they the same thing?+
Both enforce uniqueness, but a table can have only one primary key and it can't contain NULL. A table can have multiple unique keys, and unique keys can allow one NULL (in most databases). The primary key is also what other tables reference as a foreign key by default.
06IN vs EXISTS — which one should you actually reach for?+
IN compares a value against a list or subquery result — the engine typically evaluates the full subquery first. EXISTS checks only whether at least one matching row exists and can stop as soon as it finds one. On large tables, EXISTS generally outperforms IN, especially when the subquery returns a large or unindexed result set. IN also breaks in unexpected ways if the subquery can return NULL — EXISTS doesn't have that problem.
-- Customers who have placed at least one order
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);Aggregation & Grouping
QUESTIONS 7–11
07Write a query to find the second-highest salary.+
The classic. The naive approach breaks on ties; the clean approach uses DENSE_RANK or a simple offset subquery:
-- Approach 1: offset subquery
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- Approach 2: DENSE_RANK (handles ties correctly, scales to Nth)
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 2;RANK and DENSE_RANK here — interviewers ask a follow-up about duplicate salaries almost every time.08Extend that to the Nth highest salary.+
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = :N;This is the same pattern as Q7 — interviewers are testing whether you generalize a solution instead of memorizing one specific number.
09How is GROUP BY actually different from ORDER BY?+
GROUP BY collapses multiple rows into one row per group, enabling aggregate functions. ORDER BY just changes the display order of rows or groups — it doesn't reduce row count at all. Sounds obvious, but candidates regularly confuse "sorted" with "aggregated" under pressure.
10COALESCE vs ISNULL/NVL — why does it matter which one you pick?+
COALESCE is ANSI-standard SQL and works across PostgreSQL, SQL Server, MySQL, and Oracle. ISNULL (SQL Server) and NVL (Oracle) are vendor-specific. COALESCE also accepts more than two arguments and returns the first non-null value in the list, which ISNULL/NVL can't do directly.
SELECT COALESCE(shipping_address, billing_address, 'No address on file') AS address
FROM customers;11COUNT(*) vs COUNT(1) vs COUNT(column) — is there really a difference?+
COUNT(*) counts every row regardless of NULL values. COUNT(1) behaves identically in every modern optimizer — this is a myth-buster question, not a real distinction anymore. COUNT(column) only counts rows where that specific column is not null, which is the one that actually changes your result.
Joins
QUESTIONS 12–17
12Explain INNER JOIN vs LEFT JOIN with a business example.+
INNER JOIN returns only rows with a match in both tables. LEFT JOIN returns every row from the left table, with NULLs filling in where there's no match on the right.
-- Only customers who have orders
SELECT c.name, o.order_id
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
-- Every customer, orders or not
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;13Find customers who have never placed an order.+
This is the LEFT JOIN pattern interviewers use most often to test whether you understand NULLs, not just syntax:
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;14What's a self join, and where does it show up in real data?+
A self join joins a table to itself, usually to compare rows within the same table — the textbook example is matching employees to their managers, where both are stored in the same employees table.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;15UNION vs JOIN — people mix these up, what's the real distinction?+
A JOIN combines columns from two tables side by side, based on a matching condition. A UNION stacks rows from two queries on top of each other — same columns, more rows. They solve completely different problems; the only thing they share is that both involve two result sets.
16What's a CROSS JOIN actually useful for?+
A CROSS JOIN returns every combination of rows from both tables — the Cartesian product. It's rarely what you want by accident (it's a classic cause of runaway query results), but it's genuinely useful for generating combinations: every product paired with every warehouse, every date paired with every store, etc.
17How do you find duplicate rows in a table?+
SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;To pull the actual duplicate rows (not just the count), pair this with a window function — see Q22.
Subqueries & CTEs
QUESTIONS 18–21
18CTE vs subquery — is a CTE just a "prettier" subquery?+
Functionally similar, but not the same tool. A CTE (WITH clause) is named, readable, and can be referenced multiple times in the same query without repeating logic. A subquery is inline, gets harder to read when nested, and generally can't be reused within the same statement. CTEs also support recursion — subqueries don't.
19What is a correlated subquery, and why is it slower?+
A correlated subquery references a column from the outer query, which means it can't be evaluated once and reused — it has to run once per outer row. That's what makes it slower than a plain subquery on large tables.
-- Employees earning above their department's average
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);20When would you reach for EXISTS instead of IN with a subquery?+
Same principle as Q6, applied to subqueries specifically: EXISTS stops at the first match and is safe with NULLs; IN evaluates the full subquery result and can behave unexpectedly if that result contains NULL. Default to EXISTS for large or uncertain subquery results.
21Explain a recursive CTE with a real example.+
A recursive CTE repeatedly references itself to walk through hierarchical data — an org chart, a bill-of-materials, a category tree. It has an "anchor" query (the starting point) and a "recursive" query (the step that walks up or down the hierarchy).
WITH RECURSIVE org_chart AS (
-- anchor: the top of the hierarchy
SELECT employee_id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- recursive: walk down one level at a time
SELECT e.employee_id, e.name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart ORDER BY level;Window Functions
QUESTIONS 22–26
22ROW_NUMBER vs RANK vs DENSE_RANK — what actually differs?+
All three number rows within an ordered window, but they treat ties differently: ROW_NUMBER always gives a unique number, even to tied rows. RANK gives tied rows the same number, then skips the next rank (1, 2, 2, 4). DENSE_RANK gives tied rows the same number with no gap (1, 2, 2, 3).
created_at, and delete everything where ROW_NUMBER() > 1.23How do you calculate a running total?+
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM sales;The window frame defaults to "everything from the start up to the current row" when you use ORDER BY without an explicit frame — that's exactly what a running total needs.
24What are LAG and LEAD used for?+
LAG pulls a value from a previous row; LEAD pulls a value from a following row, within the same ordered window — without needing a self join.
SELECT order_date, revenue,
LAG(revenue) OVER (ORDER BY order_date) AS prev_day_revenue
FROM daily_sales;25How do you calculate month-over-month growth?+
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
ROUND(
(revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month) * 100, 2
) AS growth_pct
FROM monthly_revenue;This is one of the most common "business scenario" questions — it's really just Q24 (LAG) applied to a KPI.
26What does PARTITION BY actually do?+
PARTITION BY splits the result set into independent groups before the window function is applied — the calculation restarts for each partition. It's the window-function equivalent of GROUP BY, except rows stay ungrouped and individually visible in the output.
-- Rank products within each category, not across the whole table
SELECT product_id, category, sales,
RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rank_in_category
FROM products;Performance & Database Design
QUESTIONS 27–30
27Clustered vs non-clustered index — what's the mental model?+
A clustered index determines the physical order rows are stored on disk — a table can have only one. A non-clustered index is a separate structure that points back to the actual rows, and a table can have many. Think of a clustered index as the phone book itself (sorted by name), and a non-clustered index as a separate lookup card that points you to a page.
28A query is slow. What do you actually do?+
Run EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) to see the execution plan. Look for sequential scans on large tables where an index scan should be happening, check whether joins are using indexed columns, and check for functions applied to indexed columns in the WHERE clause — that silently disables the index. Then: add the missing index, rewrite the predicate to be sargable, or restructure the join order.
EXPLAIN as your first move, before guessing at fixes, is what interviewers are actually listening for.29Normalization vs denormalization — when do you break the rules?+
Normalization organizes data to eliminate redundancy and protect consistency — ideal for transactional (OLTP) systems where you're writing data constantly. Denormalization intentionally introduces redundancy to reduce joins and speed up reads — common in analytics/reporting (OLAP) systems and data warehouses, where read speed matters more than write efficiency.
30What are ACID properties, and why should a data analyst care?+
Atomicity — a transaction fully succeeds or fully fails, no partial writes. Consistency — a transaction moves the database from one valid state to another. Isolation — concurrent transactions don't interfere with each other. Durability — once committed, the change survives a crash. As an analyst, this matters most when you're debugging why a report doesn't match source data mid-transaction, or explaining why an aggregate looked different five minutes apart.
One-Screen Cheat Sheet
| Topic | Remember this |
|---|---|
| WHERE vs HAVING | WHERE filters rows, HAVING filters groups |
| IN vs EXISTS | EXISTS stops early, handles NULLs safely, usually faster |
| RANK vs DENSE_RANK vs ROW_NUMBER | Gaps on ties / no gaps on ties / always unique |
| Subquery vs CTE | CTE is named, reusable, and supports recursion |
| UNION vs UNION ALL | UNION dedupes and costs more; default to UNION ALL |
| Slow query, first step | Run EXPLAIN before touching the query |
