SQL Interview Questions for Data Analyst: Tips & Answers

For every data analyst aspirant, SQL is the make-or-break skill that separates top candidates from the rest. Hiring managers do not just want to see that you can memorize syntax; they need to confirm you can extract meaningful insights from real databases under pressure. If you are gearing up for your next opportunity, mastering SQL interview questions for data analyst roles is the most impactful investment you can make.

This guide unpacks the scenario-based, technical, and conceptual questions you will face. For each challenge, we explain why the recruiter is asking, deliver a polished sample answer that showcases your analytical thinking, and point out the common pitfalls that trip up unprepared candidates. You will leave this article with a clear mental map of what to expect and exactly how to respond.

We have organized the questions by difficulty and topic—from foundational SELECT statements all the way to advanced window functions and real-world business problems. Use the chapter structure to diagnose your weak spots, drill targeted practice sessions, and walk into the interview room with unshakeable confidence.

Basic SQL Interview Questions for Data Analyst

Write a SELECT Statement to Retrieve All Columns

Interviewers open with this question to confirm you understand the most fundamental data retrieval operation. They are also watching whether you mention the performance implications of using the asterisk wildcard. A thoughtful answer immediately signals you think like a professional data analyst, not just a student.

Example answer:

SELECT * FROM customers;

After showing the basic syntax, you can add: “In an exploratory phase, SELECT * helps me quickly inspect the schema. For production work or dashboard queries, I specify only the needed columns to reduce I/O and network transfer.”

Common mistakes to avoid:

• Forgetting the semicolon or the table name entirely.
• Claiming SELECT * is always fine without acknowledging its performance cost on wide tables.
• Misplacing the asterisk, for example writing SELECT customers.* FROM when a simple * suffices.

How Do You Retrieve Unique Records Using DISTINCT?

This question tests your ability to handle duplicate data, which is a daily task for a data analyst. Recruiters want to see that you can remove noise and produce clean, deduplicated result sets without relying on external tools.

Example answer:

SELECT DISTINCT city FROM customers;

You can strengthen the answer by mentioning: “When I need unique combinations of multiple columns, I list them inside the DISTINCT clause, for instance SELECT DISTINCT city, state FROM customers;.”

Common mistakes to avoid:

• Using DISTINCT as a blanket fix without understanding why duplicates exist.
• Placing DISTINCT after the column name instead of before it.
• Forgetting that DISTINCT works on all columns in the SELECT list, which can lead to unexpected row counts.

Explain the Difference Between WHERE and HAVING Clauses

Employers love this question because it reveals whether you truly grasp the order of SQL execution. Data analysts must know when to filter row-level data versus aggregated results—mixing the two is a classic cause of incorrect reports.

A strong answer: “I use WHERE to filter individual rows before aggregation happens. I use HAVING to filter groups after the GROUP BY operation, often in combination with aggregate functions like SUM or COUNT. For example, if I want departments with more than five employees, HAVING is the correct tool.”

Example code:

SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE start_date > '2022-01-01'
GROUP BY department
HAVING COUNT(*) > 5;

Common mistakes to avoid:

• Putting an aggregate condition inside the WHERE clause, which will throw an error.
• Using HAVING without a GROUP BY and expecting it to behave like WHERE.
• Assuming HAVING and WHERE are interchangeable, which leads to logically flawed queries.

What Is the Purpose of the GROUP BY Clause? Provide an Example

This question evaluates your ability to summarize data. Every data analyst job involves answering business questions such as “total sales per region” or “average order value per customer segment,” and GROUP BY is the backbone of those calculations.

Example answer:

SELECT region, SUM(revenue) AS total_revenue
FROM sales
GROUP BY region
ORDER BY total_revenue DESC;

I follow up by explaining: “The GROUP BY clause collapses rows that share the same value in the specified column. It works hand-in-hand with aggregate functions. I always make sure every non-aggregate column in the SELECT list also appears in the GROUP BY list to avoid ambiguous results.”

Common mistakes to avoid:

• Selecting columns that are neither aggregated nor part of the GROUP BY clause, which causes errors in most databases.
• Applying GROUP BY unnecessarily to a single row scenario, harming readability.
• Forgetting to alias the aggregated column, making the output harder to interpret.

Read Also: Top Python Data Analyst Interview Questions [apc_current_year]

Intermediate Filtering and Aggregation Questions for Data Analyst

How Would You Use Aggregate Functions Like COUNT, SUM, and AVG with GROUP BY?

Recruiters ask this to verify you can generate summary statistics that drive business dashboards. They want to see not just syntax but also your awareness of NULL handling, which can silently distort averages and counts.

Example answer:

SELECT product_category,
 COUNT(*) AS total_orders,
 SUM(order_amount) AS total_revenue,
 AVG(order_amount) AS avg_order_value
FROM orders
GROUP BY product_category;

I add: “I prefer COUNT(*) when I need the row count regardless of NULLs. If I need to count only non-NULL values in a specific column, I use COUNT(column_name). With AVG, I double-check whether NULLs should be treated as zeros or excluded.”

Common mistakes to avoid:

• Using AVG on a column that contains NULLs without knowing the database’s default NULL handling.
• Applying SUM on a string column by accident, which results in conversion errors.
• Forgetting to use an alias for the aggregate columns, leaving nondescript output headers.

When Do You Use a CASE Statement in SQL?

This question charts your ability to build conditional logic directly inside a query. Data analysts frequently pivot data, create custom segments, or clean messy values using CASE, and interviewers want proof that you do not export everything to Excel for such transformations.

Example answer:

SELECT customer_name,
 order_amount,
 CASE
 WHEN order_amount > 1000 THEN 'Premium'
 WHEN order_amount BETWEEN 500 AND 1000 THEN 'Standard'
 ELSE 'Budget'
 END AS customer_tier
FROM customers;

I explain: “I use CASE to categorize rows on the fly without altering the underlying table. It also appears frequently inside aggregate functions, for example SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) to count conditions conditionally.”

Common mistakes to avoid:

• Forgetting the END keyword, causing syntax errors.
• Nesting CASE statements too deeply when a simple lookup table or DECODE would be cleaner.
• Mixing data types across different WHEN branches, which can yield unexpected results.

What Is the Difference Between UNION and UNION ALL?

Hiring managers ask this to gauge your understanding of set operations and performance trade-offs. Merging datasets from multiple sources is a daily reality, and using the wrong operator can either miss records or waste resources.

Good answer: “UNION combines result sets and removes duplicate rows, performing an implicit sort. UNION ALL simply stacks rows without deduplication, which is faster. I default to UNION ALL unless deduplication is explicitly required to avoid silent data loss or performance hits.”

Example:

SELECT customer_id FROM online_sales
UNION ALL
SELECT customer_id FROM retail_sales;

Common mistakes to avoid:

• Using UNION when you mean to keep all records, accidentally dropping legitimate duplicate rows.
• Forgetting that UNION requires both SELECT statements to have the same number of columns and compatible data types.
• Assuming UNION ALL is always safe without first understanding whether duplicates need to be preserved.

Explain the Use of LEFT JOIN and Provide a Real-World Scenario

This is a fundamental join question that separates candidates who memorize syntax from those who model business problems relationally. The interviewer wants to see you map a join type to a practical analytics need.

My answer: “I use a LEFT JOIN when I need all records from the left table even if there is no match in the right table, typical for retention analysis. For instance, I join a customer dimension to an orders fact table with a LEFT JOIN to list every customer and their most recent order—customers with no orders still appear with NULL order details.”

SELECT c.customer_id, c.name, MAX(o.order_date) AS last_order
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

Common mistakes to avoid:

• Placing filter conditions on the right table in the WHERE clause instead of the ON clause, which turns the LEFT JOIN into an inner join.
• Forgetting to handle NULLs that arise from unmatched rows, leading to misleading calculations.
• Using too many unnecessary LEFT JOINs without considering performance on large datasets.

Read Also: Technical & Behavioral Data Analyst Interview Questions

Advanced SQL Interview Questions for Data Analyst

How Do You Use a Subquery in a WHERE Clause?

Interviewers want evidence that you can break complex problems into nested logical steps. Data analysts regularly construct lists of IDs or thresholds dynamically, and subqueries are the go-to tool for such in-query calculations.

Example answer:

SELECT product_name, price
FROM products
WHERE category_id IN (SELECT category_id FROM categories WHERE is_active = 1);

I explain: “I use a subquery when I need to filter based on a set of values that must be computed at runtime. I keep the subquery as simple and indexed as possible to avoid performance bottlenecks.”

Common mistakes to avoid:

• Using = with a subquery that returns multiple rows, causing a runtime error.
• Writing deeply nested subqueries that become unreadable when a JOIN would be clearer.
• Forgetting to alias the subquery when it appears in the FROM clause.

What Are Common Table Expressions (CTEs) and Why Are They Useful?

Recruiters probe your knowledge of CTEs to see if you write maintainable, self-documenting code. In a collaborative analytics environment, readability and debuggability matter as much as correctness.

My answer: “A CTE, defined with the WITH clause, creates a temporary named result set that I can reference within the main query. I use CTEs to simplify complex queries, especially when I need to reuse the same subquery logic multiple times or perform recursive hierarchical analysis.”

WITH recent_orders AS (
 SELECT customer_id, MAX(order_date) AS last_date
 FROM orders
 GROUP BY customer_id
)
SELECT c.name, o.total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN recent_orders r ON o.customer_id = r.customer_id AND o.order_date = r.last_date;

Common mistakes to avoid:

• Treating a CTE as a stored temporary table that persists beyond the single statement.
• Creating too many CTEs in one query that obscure the logic instead of clarifying it.
• Assuming CTEs are always materialized; in many databases they function like inline views and are not cached.

Explain a Correlated Subquery with an Example

This question tests your comprehension of query execution order and row-by-row processing. Correlated subqueries are a common tool for row-dependent filtering, and interviewers want to confirm you can explain their performance overhead.

Example answer:

SELECT e.name, e.salary
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

I highlight: “A correlated subquery references a column from the outer query and executes once for each outer row. I use them when the inner logic depends on the current row, but I always check execution plans because they can become slower than window functions or joins on large tables.”

Common mistakes to avoid:

• Using a correlated subquery when a simple JOIN or window function would achieve the same result more efficiently.
• Forgetting to properly alias the outer table, leading to ambiguous column references.
• Underestimating the exponential cost as row counts increase.

How Would You Find the Second Highest Salary Using SQL?

This is a classic technical screening question. It assesses your comfort with sorting, ranking, and handling duplicates, all of which are necessary for accurate percentile and top-N analyses.

One effective answer:

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Alternative using window function if the platform supports it:

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

I stress the difference: “The DENSE_RANK approach handles duplicates correctly and gives the second distinct salary value, which is what most business questions intend.”

Common mistakes to avoid:

• Using LIMIT 1 OFFSET 1 without considering that duplicate top salaries might push the second-highest value into further offsets.
• Returning only the second row without deduplication, which may show a tie for the first salary as “second.”
• Forgetting to explain your chosen approach and the edge cases it covers or misses.

How Do You Use EXISTS vs. IN in a Subquery?

This question reveals knowledge of performance optimization and logical query design. Data analysts often need to filter based on the presence of related records, and the EXISTS vs. IN choice can drastically affect query speed.

My answer: “I use EXISTS when the subquery can benefit from a correlated lookup and I only care about the presence of a match, not the actual values. EXISTS stops scanning as soon as it finds a match. I prefer IN when the list of values is small and static or when the subquery is non-correlated and simple. If the subquery returns NULLs, IN may behave unexpectedly, whereas EXISTS remains logically clean.”

SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Common mistakes to avoid:

• Using NOT IN with a subquery that contains NULLs, which can result in zero rows returned.
• Overlooking that EXISTS with a SELECT * is just as valid; columns inside the subquery are irrelevant.
• Applying IN to a massive subquery result set without testing performance.

Read Also: Data Analyst Interview Questions SQL: Top [apc_current_year] Guide

Window Functions and Analytical SQL Interview Questions for Data Analyst

What Is the ROW_NUMBER() Function and How Do You Use It?

Interviewers ask this to see if you can move beyond basic aggregation and perform row-level calculations that preserve granularity. ROW_NUMBER is essential for deduplication, pagination, and top-N-per-group problems.

Example answer:

SELECT customer_id, order_date,
 ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders;

I explain: “I assign a unique sequential integer to each row within a partition. Then I typically wrap the query in a CTE and filter WHERE rn = 1 to retrieve the latest order per customer. The ORDER BY inside the OVER clause defines the sequence.”

Common mistakes to avoid:

• Relying on ROW_NUMBER without an ORDER BY, which yields non-deterministic ranking.
• Forgetting that ROW_NUMBER treats ties arbitrarily; if ties matter, RANK or DENSE_RANK are better.
• Assuming the function works identically across all SQL dialects—syntax nuances exist.

Explain the Difference Between RANK() and DENSE_RANK()

A data analyst must correctly interpret ranking results for leaderboards, percentiles, and performance tiers. This question checks whether you have internalized the subtle but critical distinction that can skew business reports.

My answer: “RANK assigns the same rank to ties but skips subsequent numbers, whereas DENSE_RANK also assigns the same rank to ties but does not skip numbers. If three employees share the highest salary, both functions assign rank 1. RANK then assigns rank 4 to the next row, while DENSE_RANK assigns rank 2.”

SELECT name, salary,
 RANK() OVER (ORDER BY salary DESC) AS rank_number,
 DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_number
FROM employees;

Common mistakes to avoid:

• Labeling the wrong function in an interview without demonstrating the behavioral difference.
• Using RANK when a continuous ranking is needed for reporting, causing gaps that confuse stakeholders.
• Neglecting to specify PARTITION BY when the ranking should reset across groups.

How Do You Calculate a Running Total or Moving Average with Window Functions?

Business analysts frequently need cumulative metrics and rolling trends. This question tests your ability to apply frame clauses in window functions, showing you can produce time-series insights directly in SQL.

Running total example:

SELECT order_date, daily_revenue,
 SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total
FROM daily_sales;

Moving average example (7-day):

SELECT order_date, daily_revenue,
 AVG(daily_revenue) OVER (ORDER BY order_date 
 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

I note: “The ROWS BETWEEN clause defines the window frame. Without it, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which works for running totals but must be explicitly adjusted for fixed-length rolling periods.”

Common mistakes to avoid:

• Using ORDER BY alone for a moving average without a frame clause, which does not produce the intended rolling window.
• Counting dates with missing entries as part of the window, requiring a full date spine for accuracy.
• Assuming the default frame is always a physical row count; behavior varies by database.

Provide an Example of Using PARTITION BY

This question verifies you can group analytical calculations without collapsing the result set. Recruiters look for the ability to compute per-segment statistics—e.g., rank of each product within its category—while retaining detail rows.

Example:

SELECT product_name, category, price,
 AVG(price) OVER (PARTITION BY category) AS avg_category_price,
 RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category
FROM products;

I clarify: “PARTITION BY resets the window function for each unique value in the specified column, similar to a logical GROUP BY that does not collapse rows. It is indispensable for year-over-year comparisons and intra-group ranking.”

Common mistakes to avoid:

• Confusing PARTITION BY with GROUP BY and expecting the output row count to reduce.
• Omitting the ORDER BY inside OVER when using ranking functions, which are order-dependent.
• Creating windows on high-cardinality columns without an index, causing sluggish performance.

When Would You Use LEAD() or LAG()?

These functions enable row-to-row comparisons without self-joins, a pattern data analysts use for period-over-period analysis, cohort tracking, and anomaly detection. Interviewers ask this to see if you can simplify traditionally complex SQL.

Example answer:

SELECT order_date, revenue,
 LAG(revenue, 1) OVER (ORDER BY order_date) AS prev_day_revenue,
 revenue - LAG(revenue, 1) OVER (ORDER BY order_date) AS daily_change
FROM daily_sales;

“I use LAG to access a previous row’s value based on a specified order. LEAD does the same for following rows. They eliminate the need for a self-join on a sequence, making the query cleaner and often faster.”

Common mistakes to avoid:

• Forgetting to handle NULLs that LAG returns for the first row.
• Using LAG without the ORDER BY clause, resulting in non-deterministic previous values.
• Applying an offset larger than the partition size without checking for missing data.

Read Also: What Are the Most Common Data Analyst Interview Questions?

Data Cleaning SQL Interview Questions for Data Analyst

How Do You Handle NULL Values While Writing Queries?

NULLs are a reality in every database, and mishandling them is one of the quickest ways to produce incorrect reports. Recruiters probe your NULL strategy to ensure you do not silently exclude records or miscalculate averages.

My answer: “I always inspect NULLs early. I use IS NULL or IS NOT NULL for filtering, and I rely on COALESCE or IFNULL (depending on the SQL dialect) to replace NULLs with default values. For aggregates like AVG, I confirm whether NULLs should be treated as missing data or meaningful zeros.”

SELECT employee_name,
 COALESCE(bonus, 0) AS bonus_cleaned
FROM payroll;

Common mistakes to avoid:

• Using = NULL instead of IS NULL, which returns no rows.
• Replacing NULLs with arbitrary substitutes without documenting the logic.
• Assuming COUNT(column) and COUNT(*) behave identically when NULLs exist.

Explain the COALESCE() and NULLIF() Functions

This question tests your data transformation toolkit. Data analysts constantly reshape messy columns into conformed dimensions, and these two functions give you precise control over NULL logic.

Answer: “COALESCE returns the first non-NULL value in its argument list. I use it to fill down default values. NULLIF returns NULL if two arguments are equal; otherwise it returns the first argument. I apply NULLIF to convert placeholder values like zero or empty string into NULL before cleaning.”

-- Replace zero discount with NULL for accurate analysis
SELECT order_id, NULLIF(discount, 0) AS real_discount
FROM orders;

Common mistakes to avoid:

• Passing arguments of incompatible data types to COALESCE, causing implicit conversion errors.
• Using NULLIF to trap division by zero but forgetting to handle the resulting NULL in subsequent calculations.
• Assuming COALESCE can accept an unlimited number of arguments in all database systems.

How Would You Identify and Remove Duplicate Rows from a Table?

Duplicate management is a core data stewardship task. An analyst must be able to deduplicate without losing integrity, and this question reveals your methodical approach to SQL cleaning.

Identification:

SELECT customer_id, email, COUNT(*)
FROM customers
GROUP BY customer_id, email
HAVING COUNT(*) > 1;

Removal using a CTE with ROW_NUMBER:

WITH dedup AS (
 SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
 FROM customers
)
DELETE FROM customers
WHERE (customer_id, created_at) IN (SELECT customer_id, created_at FROM dedup WHERE rn > 1);

I stress: “I always back up data or run a SELECT to confirm the rows flagged for deletion before executing a destructive operation.”

Common mistakes to avoid:

• Deleting rows without a reliable ordering column, which can remove the wrong duplicate.
• Running a DELETE without a transaction, making rollback impossible.
• Using DISTINCT to create a new table while ignoring related foreign-key dependencies.

How Do You Update Data Using a CASE Expression?

Updating records conditionally is a frequent operation in data preparation. This question demonstrates your ability to run safe, targeted data modifications without resorting to multiple scripts.

UPDATE products
SET price = CASE
 WHEN category = 'Electronics' THEN price * 0.9
 WHEN category = 'Books' THEN price * 0.95
 ELSE price
END;

I mention: “Before executing, I always wrap the update in a transaction and run a SELECT with the same CASE logic to verify the new values. This prevents irreversible mass changes.”

Common mistakes to avoid:

• Omitting the ELSE branch, which sets rows not matching any condition to NULL.
• Running an UPDATE without a WHERE clause, altering the entire table unintentionally.
• Assuming the CASE expression order doesn’t matter; the first matching WHEN wins.

What Is the Difference Between CAST and CONVERT?

Data types frequently need harmonizing when joining tables or loading data. Interviewers check your ability to transform types efficiently and handle format cultural issues like dates.

Answer: “CAST is ANSI-standard and portable across databases. CONVERT is specific to SQL Server and offers style parameters for formatting dates and other types. I default to CAST for readability and compatibility unless I need format-specific conversions.”

SELECT CAST(order_date AS DATE) AS clean_date,
 CAST(unit_price AS DECIMAL(10,2)) AS precise_price
FROM raw_orders;

Common mistakes to avoid:

• Using database-specific CONVERT styles without noting the portability caveat.
• Attempting to CAST an unparseable string without first cleaning it with TRY_CAST or NULLIF.
• Ignoring potential truncation when casting from a wider type to a narrower one.

Read Also: How to Prepare for Data Analyst Interview Questions

Performance Tuning SQL Interview Questions for Data Analyst

What Are Indexes and How Do They Speed Up Queries?

A data analyst who understands indexing can collaborate effectively with engineering teams and troubleshoot slow dashboards. This question tests whether you can think beyond writing correct queries to writing efficient ones.

My explanation: “An index is a database structure that provides a fast lookup path to rows, similar to a book’s index. When I filter on an indexed column, the engine can locate matching rows without scanning the entire table. However, indexes add overhead to write operations, so they must be chosen judiciously—commonly on columns used in WHERE, JOIN, and ORDER BY clauses.”

Common mistakes to avoid:

• Assuming an index is always used; query structure and function wrapping can prevent index usage.
• Creating indexes on every column without considering read/write trade-offs.
• Expecting a single-column index to benefit multi-column filter conditions without testing composite indexes.

How Do You Analyze a Query Execution Plan?

Interviewers present this scenario to gauge your debugging methodology. Real-world data often chokes on poorly optimized queries, and knowing how to interpret the plan shows senior-level thinking.

Answer: “I use commands like EXPLAIN (in PostgreSQL and MySQL) or view the estimated execution plan in SQL Server Management Studio. I look for full table scans, expensive sort operations, and index usage. If I see a table scan on a large table, I investigate whether an index can be added or whether the query can be rewritten to use the existing index.”

I add: “I also pay attention to the cost percentages and row estimates. A large discrepancy between estimated and actual rows often signals outdated statistics.”

Common mistakes to avoid:

• Interpreting the plan without understanding the database optimiser’s decision heuristics.
• Focusing only on the cost percentage without examining the operator sequence.
• Assuming an execution plan is static; parameter sniffing and data changes can alter it.

Why Should You Avoid SELECT * in Production Queries?

This is a litmus test for production readiness. While exploratory work benefits from SELECT *, data analysts must know the concrete downsides—extra I/O, bloated memory usage, and schema fragility.

Concise answer: “I avoid SELECT * in production because it fetches unnecessary columns, increasing network latency and memory pressure. It also breaks queries if the underlying table schema changes, for example when a column is added or removed. I explicitly list only the columns required for the analysis or report.”

Common mistakes to avoid:

• Defending SELECT * inside subqueries or views, where the impact multiplies.
• Claiming it’s never acceptable without acknowledging valid rapid-prototyping contexts.
• Forgetting that SELECT * can retrieve sensitive columns not intended for the output.

What Are the Best Practices for Writing Efficient Joins?

Joins are the heart of analytical SQL, and inefficient join logic can grind an ETL pipeline to a halt. This question evaluates your defensive coding habits.

My guidelines: “I always join on indexed columns when possible and ensure join key data types match to avoid implicit conversion. I filter tables before joining using subqueries or CTEs to reduce row counts early. For multiple joins, I consider the order and choose inner joins first where possible. I also test with representative data volumes.”

Example pattern:

SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01';

Common mistakes to avoid:

• Joining on a function like UPPER(column), which prevents index usage.
• Writing cartesian joins accidentally by omitting the join condition.
• Using a full OUTER JOIN without truly needing unmatched rows from both sides.

How Do You Optimize Subqueries vs. Joins?

An advanced data analyst must make intelligent choices between equivalent logical structures. This question probes your understanding of query optimizer behavior and maintainability trade-offs.

Answer: “I test both approaches. Often a JOIN with proper indexing outperforms a correlated subquery because it can be processed as a single set operation rather than row-by-row. However, a well-placed EXISTS subquery can be very efficient for existence checks. I write both versions, compare execution plans, and pick the one with lower logical reads. Readability also matters, so I sometimes choose a CTE or window function over a nested subquery even if performance is similar.”

Common mistakes to avoid:

• Blindly converting all subqueries to joins without evaluating how the optimizer handles them.
• Ignoring that subqueries in the SELECT list may execute per row, severely degrading performance.
• Forgetting to consider NULL semantics when switching from NOT EXISTS to a LEFT JOIN with an IS NULL check.

Read Also: Tableau Interview Questions for Data Analyst Jobs

Scenario-Based SQL Interview Questions for Data Analyst

How Would You Analyze Monthly Sales Trends from an eCommerce Database?

Scenario questions test your end-to-end thinking. The interviewer wants to see you connect a vague business request to a concrete query strategy, handling date truncation, aggregation, and growth calculation.

I would outline steps: “First, I confirm the sales table granularity and date column. I use a DATE_TRUNC or FORMAT function to group by month. I sum the revenue per month, then use the LAG window function to calculate month-over-month percentage change.”

SELECT month,
 total_revenue,
 LAG(total_revenue) OVER (ORDER BY month) AS prev_month,
 ROUND((total_revenue - LAG(total_revenue) OVER (ORDER BY month)) * 100.0 / 
 LAG(total_revenue) OVER (ORDER BY month), 2) AS mom_growth
FROM (
 SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total_revenue
 FROM orders
 GROUP BY 1
) monthly_sales;

Common mistakes to avoid:

• Using string formatting for months without preserving sort order, causing alphabetical sorting.
• Ignoring seasonal adjustments or incomplete months for the current period.
• Presenting raw output without mentioning the business context and potential data gaps.

Describe How to Identify Customer Churn Using SQL

Defining churn solely with SQL showcases your ability to operationalize business metrics. Recruiters want to hear a logical definition and a clean implementation.

My approach: “I define a churn window, say 30 days without a purchase. I find each customer’s last order date using a GROUP BY with MAX, then compare it to a reference date. Customers whose max order date falls outside the active window are considered churned.”

SELECT customer_id,
 MAX(order_date) AS last_order,
 CASE WHEN MAX(order_date) < CURRENT_DATE - INTERVAL '30 days' THEN 'Churned' ELSE 'Active' END AS status
FROM orders
GROUP BY customer_id;

Common mistakes to avoid:

• Using a fixed churn definition without discussing how it changes by business model.
• Failing to exclude new customers who have not yet had time to churn.
• Storing churn status in a table without a strategy for refreshing it over time.

You Are Given Two Tables, Orders and Returns. Calculate the Return Rate by Product Category

This tests your ability to join, aggregate, and compute ratios accurately—a bread-and-butter analytics task. The interviewer watches for correct treatment of products with zero returns.

SELECT p.category,
 COUNT(DISTINCT o.order_id) AS total_orders,
 COUNT(DISTINCT r.return_id) AS total_returns,
 ROUND(COUNT(DISTINCT r.return_id) * 100.0 / NULLIF(COUNT(DISTINCT o.order_id), 0), 2) AS return_rate
FROM orders o
JOIN products p ON o.product_id = p.product_id
LEFT JOIN returns r ON o.order_id = r.order_id
GROUP BY p.category;

I highlight: “I use a LEFT JOIN on returns so categories with no returns are included. The NULLIF prevents division by zero.”

Common mistakes to avoid:

• Using an inner join on returns and losing categories with zero returns.
• Double-counting returns because of a join granularity mismatch.
• Dividing by total orders without handling zero-order edge cases.

How Would You Create a Cohort Retention Analysis with SQL?

Cohort analysis is a pillar of product analytics. Interviewers ask this to validate your ability to think in user lifecycle terms and write non-trivial date arithmetic.

My method: “I assign each customer to a cohort based on their first purchase month. I then track their activity in subsequent periods using a calendar table or dynamic date ranges. I compute the number of distinct customers who made a purchase in each period relative to their cohort month.”

WITH first_purchase AS (
 SELECT customer_id, MIN(DATE_TRUNC('month', order_date)) AS cohort_month
 FROM orders
 GROUP BY customer_id
),
cohort_activity AS (
 SELECT f.cohort_month,
 (EXTRACT(YEAR FROM o.order_date)*12 + EXTRACT(MONTH FROM o.order_date)) -
 (EXTRACT(YEAR FROM f.cohort_month)*12 + EXTRACT(MONTH FROM f.cohort_month)) AS period,
 COUNT(DISTINCT o.customer_id) AS active_users
 FROM first_purchase f
 JOIN orders o ON f.customer_id = o.customer_id
 GROUP BY f.cohort_month, period
)
SELECT * FROM cohort_activity ORDER BY cohort_month, period;

Common mistakes to avoid:

• Treating cohort month as a string without numeric interval calculation.
• Ignoring the need to normalize active users by cohort size for retention percentage.
• Producing a pivot table in SQL when the reporting layer should handle that presentation.

The Stakeholder Wants a Dashboard – What Metrics Would You Query?

This open-ended question probes your consultative and design skills. You need to demonstrate that you translate a fuzzy request into concrete, maintainable SQL pipelines.

My structured answer: “I first clarify the business goal—revenue growth, operational efficiency, or customer engagement. I then define the granularity and dimensions: daily, by region, by product line. I query core metrics such as sum of sales, count of orders, average order value, and month-over-month growth. I also build queries for dynamic filters like date range and top-performing categories. All queries are written as views or materialized tables that the BI tool can consume efficiently.”

Common mistakes to avoid:

• Jumping straight to writing SQL without asking clarifying questions.
• Over-engineering the query with heavy joins before understanding data refresh frequency.
• Neglecting to discuss how to handle time zones, currency, and data freshness.

Kesimpulan

Walking into a data analyst interview without rigorous SQL preparation is a gamble you do not need to take. The questions covered in this guide—from basic SELECT retrieval to complex window functions and real-world scenario reasoning—mirror exactly what hiring managers in 2026 use to separate standout candidates from the crowd. By internalizing not just the syntax but also the why behind every question, you position yourself as a problem solver, not just a query writer.

Focus on the practical patterns: anticipate NULL pitfalls, articulate the trade-offs between EXISTS and IN, explain partition ranking with confidence, and always connect your SQL logic back to the business question. Use the sample answers as launchpads for your own variations. Practice on real datasets, simulate time pressure, and narrate your thought process aloud. This active preparation transforms technical knowledge into interview-winning communication.

The SQL landscape continues to evolve, but the foundations of clear, efficient, and analytical querying are timeless. Return to this article as a checklist before every interview, and remember that every question is an opportunity to demonstrate your data-driven mindset. Good luck—your next role is just a well-crafted query away.