SQL Interview Questions for Data Analyst 2026With Answers — Beginner to Advanced

Q1: What is the difference between WHERE and HAVING?

WHERE filters rows before grouping — it works on raw data. HAVING filters after grouping — it works on aggregated results. Example: WHERE salary > 50000 filters individual rows. HAVING COUNT(*) > 5 filters groups after GROUP BY. You cannot use aggregate functions (SUM, COUNT, AVG) in a WHERE clause — that is what HAVING is for.

Q2: What is the difference between INNER JOIN, LEFT JOIN and RIGHT JOIN?

INNER JOIN returns only rows that have matching records in both tables. LEFT JOIN returns all rows from the left table and the matched rows from the right — unmatched right side rows show as NULL. RIGHT JOIN is the opposite. In practice: use INNER JOIN when you only want complete matches. Use LEFT JOIN when you want all records from the main table even if the related data is missing — for example, all customers even if they have made no orders.

Q3: How do you find duplicate records in a table?

Use GROUP BY and HAVING. Example: SELECT email, COUNT(*) as count FROM customers GROUP BY email HAVING COUNT(*) > 1. This returns all email addresses that appear more than once. To see the full duplicate rows, wrap this in a subquery or use a CTE with ROW_NUMBER().

Q4: What is the difference between COUNT(*) and COUNT(column_name)?

COUNT(*) counts every row including nulls. COUNT(column_name) counts only the non-null values in that column. If a column has 5 nulls out of 100 rows, COUNT(*) returns 100 and COUNT(column_name) returns 95. Use COUNT(*) for total row counts. Use COUNT(column_name) when you want to know how many rows have a value in a specific field.

Q5: How do you find the second highest salary from an employees table?

Method 1 using subquery: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). Method 2 using LIMIT/OFFSET: SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1. Method 3 using ROW_NUMBER: SELECT salary FROM (SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rn FROM employees) t WHERE rn = 2.

Q6: What is a NULL value and how do you handle it in SQL?

NULL means the absence of a value — it is not zero, not empty string, just unknown. NULL does not equal anything, not even itself — so WHERE column = NULL does not work. Use IS NULL and IS NOT NULL. To replace NULLs use COALESCE(column, replacement) or ISNULL(column, replacement) in SQL Server. Be careful with aggregations — SUM and AVG automatically ignore NULLs, but COUNT(*) includes rows with NULLs.

Q1: What is a CTE and when would you use it instead of a subquery?

CTE (Common Table Expression) is a named temporary result set defined with WITH. It makes complex queries readable and can be referenced multiple times in the same query. Use a CTE when: (1) you need to reference the same subquery more than once, (2) the query has multiple levels of nesting that make a subquery hard to read, (3) you are writing a recursive query. Example: WITH monthly_sales AS (SELECT month, SUM(revenue) as total FROM orders GROUP BY month) SELECT * FROM monthly_sales WHERE total > 100000.

Q2: Explain window functions with an example.

Window functions perform calculations across a set of rows related to the current row without collapsing them like GROUP BY does. Common ones: ROW_NUMBER() assigns a unique number per row within a partition. RANK() assigns rank with gaps for ties. LAG() and LEAD() access previous and next row values. SUM() OVER() creates a running total. Example: SELECT employee_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank FROM employees. This ranks employees by salary within each department without losing any rows.

Q3: How do you calculate month-over-month growth in SQL?

Use LAG() window function: SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) as prev_month, ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 / LAG(revenue) OVER (ORDER BY month), 2) as growth_pct FROM monthly_revenue. This creates a previous month column and calculates the percentage change. The first month will have NULL for growth since there is no previous month.

Q4: What is the difference between UNION and UNION ALL?

UNION combines result sets and removes duplicate rows. UNION ALL combines result sets and keeps all rows including duplicates. UNION ALL is faster because it does not need to sort and deduplicate. Use UNION when you need distinct results. Use UNION ALL when you know there are no duplicates or you want to keep them — for example, combining two different log tables where the same event cannot appear in both.

Q5: How would you find customers who have not placed any orders?

Three approaches: (1) LEFT JOIN with NULL check: SELECT c.customer_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.customer_id IS NULL. (2) NOT EXISTS: SELECT customer_id FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id). (3) NOT IN: SELECT customer_id FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders). The LEFT JOIN approach is usually the most efficient.

Q6: Write a query to find the top 3 products by sales in each category.

Use ROW_NUMBER() or RANK() with PARTITION BY: WITH ranked_products AS (SELECT category, product_name, total_sales, ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sales DESC) as rn FROM product_sales) SELECT category, product_name, total_sales FROM ranked_products WHERE rn <= 3. This partitions the ranking by category, so each category gets its own top 3 independently.

Q1: What is the difference between RANK(), DENSE_RANK() and ROW_NUMBER()?

ROW_NUMBER() assigns a unique sequential number — no ties. If two employees have the same salary, one gets 1 and the other gets 2 arbitrarily. RANK() assigns the same rank to ties but skips the next rank. Two people at rank 1 means the next rank is 3, not 2. DENSE_RANK() assigns the same rank to ties but does NOT skip. Two people at rank 1 means the next rank is 2. Use DENSE_RANK() when you want continuous ranking, RANK() when you want to show the true positional gap caused by ties.

Q2: How do you pivot rows into columns in SQL?

In SQL Server use PIVOT operator. In MySQL or PostgreSQL use conditional aggregation with CASE: SELECT department, SUM(CASE WHEN month = "Jan" THEN revenue ELSE 0 END) as Jan, SUM(CASE WHEN month = "Feb" THEN revenue ELSE 0 END) as Feb, SUM(CASE WHEN month = "Mar" THEN revenue ELSE 0 END) as Mar FROM sales GROUP BY department. This converts rows (one row per month) into columns (one column per month). Interviewers ask this frequently for BI scenarios.

Q3: A query is running slowly. How do you start debugging it?

Step 1: Run EXPLAIN or EXPLAIN ANALYZE to see the query plan — look for full table scans (Seq Scan) on large tables. Step 2: Check if the columns in WHERE, JOIN and ORDER BY have indexes. Step 3: Look at the row count at each step — is a JOIN creating a massive intermediate table? Step 4: Check for functions on columns in WHERE clauses — WHERE YEAR(order_date) = 2025 cannot use an index, but WHERE order_date BETWEEN "2025-01-01" AND "2025-12-31" can. Step 5: Consider breaking the query into smaller CTEs or temp tables.

Write queries, do not just read them

Reading a SQL answer and writing it from scratch are completely different skills. Use free tools like SQLiteOnline, DB Fiddle or MySQL Workbench. Create a sample table and write every query in this guide yourself.

Focus on JOINs and GROUP BY first

These two appear in at least 80% of data analyst SQL tests. Know every JOIN type cold. Know how GROUP BY, HAVING, COUNT, SUM, AVG and MAX work together without hesitation.

Practise on real datasets

Kaggle has free datasets. Load the Superstore or E-commerce dataset into MySQL and write real business queries — top customers, monthly trends, product returns. Interview answers from real experience sound completely different from textbook answers.

Learn to explain your query out loud

Interviewers often ask you to explain what a query does after you write it. Practise reading your SQL aloud: "Here I am joining orders to customers on customer_id to get the full customer record for each order, then grouping by customer to sum their revenue..."

Time yourself

In real interviews you have 10–15 minutes per SQL question. Practise under time pressure — set a timer and write the query before it runs out. Speed comes from repetition, not from reading more guides.