SQL Interview Questions for Data Scientists – Top 50 with Answers 2026

SQL is tested in virtually every data science and data analyst interview. Unlike Python or ML theory, SQL questions are often practical — you are given a schema and asked to write queries on the spot. This guide covers the 50 most frequently asked SQL interview questions with complete answers, from basic joins to advanced window functions and query optimisation. Mastering these will prepare you for data science roles at any company.

Basic SQL Interview Questions

Q1. What is the difference between WHERE and HAVING?
WHERE filters individual rows before any grouping or aggregation occurs. HAVING filters groups after GROUP BY aggregation. You cannot use aggregate functions like SUM(), AVG(), or COUNT() in a WHERE clause — that causes an error. HAVING exists specifically for filtering aggregated results. Example: to find departments with average salary above 70,000, you use HAVING AVG(salary) > 70000, not WHERE AVG(salary) > 70000. Remember: WHERE filters rows, HAVING filters groups.

Q2. What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN?
INNER JOIN returns only rows that have matching values in both tables — unmatched rows from either table are excluded. LEFT JOIN returns all rows from the left table and matching rows from the right table; where there is no match, the right table columns show NULL. RIGHT JOIN is the mirror: all rows from the right table, NULLs for unmatched left columns. FULL OUTER JOIN returns all rows from both tables, with NULLs where there is no match on either side. In practice, LEFT JOIN is used most frequently — it preserves the “primary” table’s complete records while enriching with optional data from a secondary table.

Q3. What is a CROSS JOIN and when would you use it?
A CROSS JOIN produces the Cartesian product of two tables — every row from the first table is combined with every row from the second. If table A has 100 rows and table B has 10 rows, the result has 1,000 rows. Use cases: generating all combinations (every product with every region for a sales matrix), creating a date dimension table by crossing years with months, or pairing every user with every item for recommendation scoring. Use with caution on large tables — the result grows multiplicatively.

Q4. What is a self join and give a practical example?
A self join joins a table to itself using an alias. Classic use case: an employee table with an employee_id and a manager_id column (where manager_id references employee_id in the same table). To find each employee’s manager name: SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id. Other examples: finding pairs of customers in the same city, identifying products with similar prices, or hierarchical data like organisational charts.

Q5. What is the difference between UNION and UNION ALL?
UNION combines the result sets of two queries and removes duplicate rows — it sorts the combined result to identify and eliminate duplicates, which has a performance cost. UNION ALL combines result sets without removing duplicates — it is faster because no deduplication step is needed. Use UNION when you genuinely need distinct rows. Use UNION ALL when you know there are no duplicates (combining data from different time periods, for example) or when duplicates are acceptable and performance matters. Both queries must have the same number of columns with compatible data types.

Q6. What is the difference between DELETE, TRUNCATE, and DROP?
DELETE removes specific rows based on a WHERE clause (or all rows if no WHERE is given), can be rolled back in a transaction, fires triggers, and is logged row by row — slow on large tables. TRUNCATE removes all rows from a table, cannot be filtered with WHERE, is much faster (deallocates data pages rather than deleting row by row), and in most databases cannot be rolled back. DROP removes the entire table structure along with all its data, indexes, and constraints — the table no longer exists. Memory aid: DELETE is surgical, TRUNCATE is a reset, DROP is demolition.

Joins and Subqueries

Q7. Find all customers who have never placed an order.
This is a classic “find non-matching” query. Three approaches: LEFT JOIN with NULL check: 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. NOT IN subquery: SELECT customer_id, name FROM customers WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM orders). NOT EXISTS (often fastest with indexes): SELECT customer_id, name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id). The LEFT JOIN approach is the most readable and widely understood.

Q8. What is a correlated subquery? Give an example.
A correlated subquery references columns from the outer query, so it is re-executed for each row of the outer query. Example — find employees earning more than the average salary in their department: SELECT name, department, salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department). The inner query uses e.department from the outer query, making it correlated. Correlated subqueries are often slow because they execute once per outer row. They can frequently be rewritten using window functions (salary > AVG(salary) OVER (PARTITION BY department)) for much better performance.

Q9. What is a CTE (Common Table Expression) and when should you use it?
A CTE is a temporary named result set defined with the WITH clause, scoped to the current query. It improves readability by breaking complex queries into named, logical steps that read like a story. It also allows referencing the same subquery multiple times without rewriting it. Recursive CTEs handle hierarchical data (org charts, bill of materials). Use CTEs over subqueries when: the logic is complex and benefits from a named intermediate step; the same subquery is needed multiple times; or the query involves hierarchical recursion. Most databases execute CTEs inline (like a view), so they do not automatically improve performance — use materialised CTEs (or temp tables) when performance matters.

Window Functions — The Most Asked SQL Topic

Q10. What are window functions and how do they differ from GROUP BY?
Window functions compute a value for each row based on a “window” of related rows — without collapsing the result set into one row per group. GROUP BY collapses many rows into one aggregate row per group. Window functions keep all original rows and add a computed column. The OVER() clause defines the window: PARTITION BY groups rows (like GROUP BY), ORDER BY defines the sequence within each partition, and ROWS/RANGE defines the frame. Key window functions: ROW_NUMBER() (unique sequential rank), RANK() (same rank for ties, gaps after), DENSE_RANK() (same rank for ties, no gaps), LAG() and LEAD() (access previous/next row), SUM/AVG/COUNT OVER() (running totals and moving averages).

Q11. Write a query to rank employees by salary within each department.

SELECT
    employee_id,
    name,
    department,
    salary,
    RANK()        OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
    DENSE_RANK()  OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
    ROW_NUMBER()  OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS pct_rank
FROM employees;

Q12. Calculate a 3-month rolling average of monthly sales.

SELECT
    month,
    revenue,
    AVG(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3m_avg,
    SUM(revenue) OVER (ORDER BY month) AS cumulative_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_change,
    ROUND(100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY month))
          / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0), 2) AS mom_pct_change
FROM monthly_sales
ORDER BY month;

Q13. Find the second-highest salary in each department.

-- Method 1: Window function (cleanest)
WITH ranked AS (
    SELECT name, department, salary,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rnk = 2;

-- Method 2: Subquery
SELECT name, department, salary
FROM employees e
WHERE salary = (
    SELECT MAX(salary) FROM employees
    WHERE department = e.department AND salary < (
        SELECT MAX(salary) FROM employees WHERE department = e.department
    )
);

Q14. Write a query to find duplicate rows in a table.

-- Find duplicates on specific columns
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- See the full duplicate rows
WITH dupes AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM users
)
SELECT * FROM dupes WHERE rn > 1;

-- Delete duplicates, keeping the earliest
WITH dupes 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 dupes WHERE rn > 1);

Aggregation and Grouping Questions

Q15. What does GROUP BY do and what are the rules for SELECT columns?
GROUP BY collapses multiple rows into one row per unique combination of the grouped columns. Every column in the SELECT clause must either appear in the GROUP BY clause or be wrapped in an aggregate function (SUM, COUNT, AVG, MAX, MIN). This rule exists because once rows are grouped, SQL needs to know how to produce a single value for each non-grouped column. A common interview mistake: selecting a non-aggregated, non-grouped column. Some databases (MySQL with non-strict mode) allow this but return a random value — never rely on this behaviour.

Q16. Explain the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column).
COUNT(*) counts all rows in the group, including rows where all columns are NULL. COUNT(column) counts rows where that specific column is NOT NULL — it ignores NULL values. COUNT(DISTINCT column) counts the number of unique non-NULL values in that column. Example: a table with 100 rows, where 10 have NULL in the email column and 5 users share the same email. COUNT(*) = 100, COUNT(email) = 90, COUNT(DISTINCT email) = 85. Use the appropriate form based on what you are actually measuring.

Q17. Write a query for year-over-year revenue growth by product category.

WITH yearly AS (
    SELECT
        YEAR(order_date)     AS year,
        category,
        SUM(revenue)         AS total_revenue
    FROM orders o
    JOIN products p ON o.product_id = p.product_id
    GROUP BY YEAR(order_date), category
)
SELECT
    category,
    year,
    total_revenue,
    LAG(total_revenue) OVER (PARTITION BY category ORDER BY year) AS prev_year_revenue,
    ROUND(100.0 * (total_revenue - LAG(total_revenue) OVER (PARTITION BY category ORDER BY year))
          / NULLIF(LAG(total_revenue) OVER (PARTITION BY category ORDER BY year), 0), 2)
          AS yoy_growth_pct
FROM yearly
ORDER BY category, year;

Advanced SQL Concepts

Q18. What is an index and how does it improve query performance?
An index is a separate data structure (usually a B-tree) that stores column values in sorted order with pointers to the actual rows. Without an index, a query searches every row sequentially (full table scan: O(n)). With an index, the database uses binary search to find matching values in O(log n) time. Create indexes on columns used in WHERE, JOIN, and ORDER BY clauses. Composite indexes cover multiple columns — column order matters (most selective column first). Indexes cost: extra storage, and every INSERT/UPDATE/DELETE must update the index. Too many indexes slow writes. The trade-off is read performance vs write performance and storage.

Q19. What is query execution plan and how do you use EXPLAIN?
The execution plan shows exactly how the database will execute a query — which indexes it will use, whether it will do a full table scan or an index scan, join algorithms, sort operations, and estimated row counts. Use EXPLAIN SELECT ... in MySQL/PostgreSQL to see the plan. Look for: sequential scans on large tables (add an index), nested loop joins on large tables (may need hash join), large estimated row counts that do not match actual rows (stale statistics — run ANALYZE). EXPLAIN ANALYZE (PostgreSQL) actually executes the query and shows real vs estimated row counts, revealing optimiser mistakes.

Q20. What is COALESCE and when do you use it?
COALESCE(value1, value2, ..., valueN) returns the first non-NULL argument in the list. It is the SQL equivalent of a null-coalescing operator. Use cases: providing a default value — COALESCE(discount, 0) to replace NULL discounts with 0 for calculations; cleaning data — COALESCE(phone_mobile, phone_home, phone_work, 'No phone') to find the first available phone number; avoiding divide-by-zero — revenue / NULLIF(units, 0) with COALESCE to provide a safe default. NULLIF(a, b) returns NULL if a = b, else returns a — the complementary function.

Q21. Write a pivot query to show monthly revenue by product in columns.

-- CASE WHEN pivot (works in all databases)
SELECT
    product,
    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,
    SUM(CASE WHEN month = 'Q1'  THEN revenue ELSE 0 END) AS Q1_total
FROM monthly_revenue
GROUP BY product;

Q22–30 (Common patterns you must know):

Q22. Find customers who made purchases in every month of 2025. GROUP BY customer_id, HAVING COUNT(DISTINCT MONTH(order_date)) = 12.

Q23. Find the most recent order for each customer. ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn, then WHERE rn = 1.

Q24. Calculate the running total of sales by date. SUM(revenue) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING).

Q25. Find customers whose total spend is above the median. Use a subquery with PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_spend) or self-join approach.

Q26. What is a stored procedure vs a function? Stored procedures execute a series of SQL statements and can have side effects (INSERT, UPDATE, DELETE). Functions return a value and cannot have side effects (in most databases) — they can be used in SELECT statements.

Q27. What is a view? When would you use a materialised view? A view is a saved SELECT query — no data is stored, it runs the query on access. A materialised view stores the query result physically and refreshes periodically — dramatically faster for complex aggregations accessed frequently.

Q28. What is the difference between a primary key and a unique key? A primary key uniquely identifies each row, cannot be NULL, and there can be only one per table. A unique key also enforces uniqueness but can be NULL (behaviour varies by database) and a table can have multiple unique keys.

Q29. What is normalisation? Name the normal forms. Normalisation reduces data redundancy and improves integrity by organising tables. 1NF: atomic values, no repeating groups. 2NF: 1NF + no partial dependencies (non-key columns depend on the full primary key). 3NF: 2NF + no transitive dependencies (non-key columns depend only on the primary key, not on other non-key columns).

Q30. What are transactions and ACID properties? A transaction is a unit of work that either completes fully or not at all. ACID: Atomicity (all or nothing), Consistency (database remains in a valid state), Isolation (concurrent transactions do not interfere), Durability (committed changes survive system failure).

SQL for Data Analysis Patterns

-- Cohort retention analysis
WITH cohorts AS (
    SELECT user_id, MIN(DATE_TRUNC('month', created_at)) AS cohort_month
    FROM users GROUP BY user_id
),
activity AS (
    SELECT DISTINCT user_id, DATE_TRUNC('month', event_date) AS active_month
    FROM events
)
SELECT
    c.cohort_month,
    DATEDIFF('month', c.cohort_month, a.active_month) AS months_since_join,
    COUNT(DISTINCT a.user_id) AS retained_users
FROM cohorts c
JOIN activity a ON c.user_id = a.user_id
GROUP BY 1, 2
ORDER BY 1, 2;

Tips for SQL Interviews

Start by restating the problem to confirm your understanding before writing any code. Sketch out the logic in plain English first — "I need to join orders to customers, then filter to last 30 days, then group by category." Write clean, readable SQL with consistent indentation and meaningful aliases. When stuck, break the problem into smaller subproblems using CTEs. If you spot an edge case (NULL values, division by zero, no matching rows), mention it — interviewers reward candidates who think about data quality. Practice on real datasets using Mode Analytics, Leetcode SQL problems, or HackerRank SQL challenges until window functions feel natural.

Conclusion

SQL mastery for data science interviews comes down to three areas: joins (especially LEFT JOIN and self-joins), window functions (ROW_NUMBER, RANK, LAG/LEAD, running totals), and aggregation patterns (GROUP BY with HAVING, pivot with CASE WHEN). These three topics appear in the majority of SQL interview questions across every company. Practice writing queries by hand without autocomplete, because interviews are handwritten or on a plain editor. The goal is to think in sets, not rows — SQL is a declarative language and the best SQL writers describe what they want, not how to compute it step by step.