Data Analyst SQL Interview Questions: 7 Topics to Master

Data Analyst SQL Interview Questions: 7 Topics to Master

The data analyst SQL interview questions you are most likely to face come from seven topics: filtering and NULLs, aggregation, joins, subqueries and CTEs, window functions, duplicates and data cleaning, and database theory. If you are a fresher, master the first three and you will clear most screening rounds. If you have two or more years of experience, expect the last four, especially window functions, because that is where interviewers separate strong candidates from average ones.

The problem with most question banks is that they list 50 or more items in no useful order. You memorize definitions, then freeze when an interviewer hands you a blank editor and a table you have never seen. Interviewers care less about the definition and more about how you reason through a query out loud.

This article groups the questions into seven topics. Each topic gives you the questions to expect, short answers you can say aloud, a practice query, and what the person across the table is checking. We build hiring software at Olibr, so that last part comes from the interviewer's side. All examples use three tables: customers(customer_id, customer_name, city, signup_date), orders(order_id, customer_id, order_date, amount) and employees(employee_id, employee_name, department_id, salary, manager_id). Queries are written for PostgreSQL, and I flag where MySQL or SQL Server differ.

1. Filtering, sorting and NULL handling

This is the opening round, and it is where SQL interview questions for data analyst freshers start and often end. You need to filter rows, sort them, remove repeats and handle missing data without errors.

Common questions and sample answers

Expect these: "How do you filter and sort results? How do you get unique values? How do you handle NULL?" A good answer is short. WHERE filters rows, ORDER BY sorts ascending unless you add DESC, and DISTINCT returns unique combinations of the selected columns. NULL means unknown, so amount = NULL never matches. Use IS NULL to find missing values and COALESCE to replace them.

SELECT order_id, COALESCE(amount, 0) AS amount
FROM orders
WHERE order_date >= '2026-01-01'
ORDER BY amount DESC;

NULL is not a value but the absence of one, so it never equals anything, not even another NULL.

Practice query and what interviewers check

Practice this: return the five largest orders placed in 2026, treating a missing amount as zero. Use a half-open date range, which stays correct when the column holds timestamps.

SELECT order_id, COALESCE(amount, 0) AS amount
FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'
ORDER BY amount DESC
LIMIT 5;

Interviewers check three things here. They want to see you ask about the columns first, avoid BETWEEN on timestamps, and know that LIMIT becomes TOP in SQL Server and FETCH FIRST in Oracle.

2. Aggregation with GROUP BY and HAVING

Aggregation questions turn raw rows into the metrics a data analyst reports every week. The standard question is the difference between WHERE and HAVING, and the answer is about timing.

Common questions and sample answers

You will be asked: "What is the difference between WHERE and HAVING? What is the difference between COUNT(), COUNT(column) and COUNT(DISTINCT column)?" WHERE filters rows before grouping, and HAVING filters groups after the aggregate is calculated. COUNT() counts every row, while COUNT(column) skips NULLs and COUNT(DISTINCT column) counts unique non-NULL values.

SELECT customer_id, COUNT(*) AS total_orders, SUM(amount) AS total_spent
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY customer_id
HAVING SUM(amount) > 50000;

WHERE trims the rows going into the groups, and HAVING trims the groups coming out.

Practice query and what interviewers check

Practice this: for each customer, count orders under 1,000 and orders of 1,000 or more, in separate columns. This is conditional aggregation, a CASE expression inside an aggregate, and it is the standard way to pivot rows into columns.

SELECT customer_id,
       COUNT(CASE WHEN amount < 1000 THEN 1 END)  AS small_orders,
       COUNT(CASE WHEN amount >= 1000 THEN 1 END) AS large_orders
FROM orders
GROUP BY customer_id;

Interviewers look for whether you state the grain of the result (one row per customer) before you write anything. Saying that sentence aloud shows the analytical habit they hire for.

3. Joins

Join questions test whether you can combine tables without silently losing or multiplying rows. In any set of SQL interview questions and answers for data analyst roles, this is the topic that comes up most often.

Common questions and sample answers

Expect: "What is the difference between INNER JOIN and LEFT JOIN? How do you find customers who never ordered? What is a self join?" INNER JOIN keeps only matching rows, and LEFT JOIN keeps every row from the left table and fills NULLs where the right table has no match. A self join joins a table to itself, for example employees to their managers. MySQL has no FULL OUTER JOIN, so you emulate it with a LEFT JOIN and a RIGHT JOIN combined with UNION.

SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

A filter on the right-hand table inside WHERE quietly turns a LEFT JOIN into an INNER JOIN, so put it in the ON clause.

Practice query and what interviewers check

Practice this: list every employee with their manager's name, including the top manager who has none.

SELECT e.employee_name, m.employee_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

The check behind most join questions is row counts. Interviewers want to hear that you compare counts before and after a join. They also want you to know that joining a one-to-many relationship and then summing a column on the "one" side inflates the totals.

4. Subqueries and CTEs

Subquery questions show whether you can break a messy business question into steps. The question is usually phrased as "write it two ways" or "which would you use and why".

Common questions and sample answers

You will hear: "What is a subquery? What is a CTE? EXISTS or IN? How do you find orders above each customer's own average?" A subquery is a query nested inside another. A CTE is a named result set defined with WITH, and it makes long queries readable and reusable inside one statement. Prefer EXISTS over NOT IN for "has no match" checks, because NOT IN returns no rows at all if the subquery produces even one NULL.

WITH customer_avg AS (
    SELECT customer_id, AVG(amount) AS avg_amount
    FROM orders
    GROUP BY customer_id
)
SELECT o.order_id, o.customer_id, o.amount
FROM orders o
JOIN customer_avg a ON a.customer_id = o.customer_id
WHERE o.amount > a.avg_amount;

Name each step of a query like a CTE and the interviewer can follow your thinking without asking.

Practice query and what interviewers check

Practice this: find customers who placed at least one order in 2026, using EXISTS.

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.order_date >= '2026-01-01'
);

Interviewers mostly check readability and explanation. A correct but unreadable 40-line nested query scores lower than a three-CTE version you narrate step by step. Many will also ask you to rewrite the answer as a JOIN, so be ready to say when each form is clearer.

5. Window functions

Window functions are the dividing line between junior and mid-level data analyst SQL interview questions. A window function calculates across related rows but keeps every row in the result, unlike GROUP BY.

Common questions and sample answers

Expect: "Explain ROW_NUMBER, RANK and DENSE_RANK. How do you get the top N per group? How do you compare a month with the previous month?" The ranking difference is easiest to show with tied salaries.

Salary	ROW_NUMBER	RANK	DENSE_RANK
90,000	1	1	1
80,000	2	2	2
80,000	3	2	2
70,000	4	4	3

RANK leaves a gap after a tie, DENSE_RANK does not, and ROW_NUMBER never ties. For top N per group, rank inside a subquery and filter outside.

SELECT *
FROM (
    SELECT employee_name, department_id, salary,
           DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
    FROM employees
) t
WHERE rnk <= 2;

You cannot filter on a window function in WHERE, so wrap it in a subquery or CTE first.

Practice query and what interviewers check

Practice this: calculate monthly sales and show the months where sales fell compared with the previous month.

WITH monthly AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS sales
    FROM orders
    GROUP BY 1
),
compared AS (
    SELECT month, sales, LAG(sales) OVER (ORDER BY month) AS prev_sales
    FROM monthly
)
SELECT month, sales, prev_sales
FROM compared
WHERE sales < prev_sales;

MySQL has no DATE_TRUNC, so use DATE_FORMAT(order_date, '%Y-%m-01') instead. Interviewers check that you know PARTITION BY versus ORDER BY inside OVER, and that you handle the first month, where LAG returns NULL.

6. Duplicates and data cleaning

Real tables are messy, so interviewers test whether you can find bad data and fix it safely. This topic combines duplicates, set operators and the delete commands.

Common questions and sample answers

You will be asked: "How do you find duplicates? UNION versus UNION ALL? DELETE versus TRUNCATE versus DROP?" To find duplicates, group by the columns that define a duplicate and keep groups with HAVING COUNT(*) > 1. UNION removes duplicate rows, and UNION ALL keeps them and is faster, so default to UNION ALL when you know the inputs do not overlap. DELETE removes rows matching a WHERE condition, TRUNCATE empties the whole table quickly, and DROP removes the table and its structure.

SELECT customer_name, city, COUNT(*) AS occurrences
FROM customers
GROUP BY customer_name, city
HAVING COUNT(*) > 1;

Preview with a SELECT before you DELETE, and say out loud that you would.

Practice query and what interviewers check

Practice this: delete duplicate customers but keep the one with the lowest customer_id.

DELETE FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM (
        SELECT customer_id,
               ROW_NUMBER() OVER (PARTITION BY customer_name, city
                                  ORDER BY customer_id) AS rn
        FROM customers
    ) t
    WHERE rn > 1
);

Interviewers listen for how you define "duplicate" (same name, or same name and city?) and whether you wrap destructive statements in a transaction. Candidates who ask those questions first sound like people who have cleaned production data before.

7. Indexes, keys and normalization

Theory questions are short, and a wrong answer here costs more than it should. Analysts are not expected to design schemas, but they are expected to explain why a query is slow and how tables relate.

Common questions and sample answers

The usual set is below. Keep each answer to two sentences and offer an example if asked.

Question	Short answer
Primary key vs foreign key	A primary key uniquely identifies a row. A foreign key points to a primary key in another table and enforces the relationship.
What is an index?	A structure that speeds up lookups and joins on a column, at the cost of storage and slower writes.
Clustered vs non-clustered	A clustered index relates to how the table data is ordered. A non-clustered index is a separate structure pointing to the rows. Details vary by database.
What is normalization?	Splitting data into related tables to cut duplication and update anomalies, typically up to third normal form.
What are ACID properties?	Atomicity, consistency, isolation and durability, the guarantees of a transaction.
SQL vs NoSQL	SQL stores structured data in tables with a fixed schema. NoSQL uses flexible models such as documents or key-value pairs.

An analyst is rarely asked to design the schema, but is always expected to explain why a query is slow.

Practice query and what interviewers check

Practice this: a report filtering orders by customer and date takes minutes. What do you do? Run EXPLAIN to read the plan, look for a full table scan, then consider an index on the filter columns.

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

Interviewers check that you diagnose before you fix, and that you name the trade-off: indexes help reads but slow inserts and updates. If you do not own the database, say you would take the plan to the DBA.

Turn these questions into practice

Seven topics cover most of what a SQL screening for data analysts throws at you: filtering and NULLs, aggregation, joins, subqueries, window functions, data cleaning and theory. Do not memorize the answers above. Type each practice query into a real database, change the tables, break it on purpose and fix it. Then explain your solution aloud in two sentences, because that is the skill interviewers are scoring.

The last step is practicing under pressure. Olibr's AI mock interview lets you pick a role, answer by voice or text, and get each answer scored with a model answer to compare. You get one free mock interview every week, and extra ones cost INR 99 each, GST included. Run one before your real round and fix the gaps it shows.