10 SQL Interview Questions for Data Analysts: With Answers

You're staring at a shared editor, a small employees schema on screen, and an interviewer who asks for the second highest salary. Before you type, they're watching whether you ask about ties, NULLs, and what should happen if there's only one distinct value. That's the pattern across SQL interview questions for data analysts. The query matters, but your assumptions matter first.

SQL is a core screening skill because an analysis of data analyst job postings found SQL in 52.9% of listings, according to the 365 Data Science data analyst job market analysis. Interviewers therefore use these ten questions to check whether you can extract, join, aggregate, and validate data, not just recall syntax. Each item below leads with a model answer, then covers the likely follow-up, the edge case that separates strong candidates, and a short practice prompt.

Rehearse out loud. Name your dialect, define the grain, and explain how you'd verify the result before you write the final query. If you can't say why your answer is right, a correct looking result won't save you.

1. Find the Second Highest Salary

Lead with the portable answer: SELECT MAX(salary) AS second_highest FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); The subquery finds the top salary, and the outer query finds the largest value below it. It avoids relying only on LIMIT or OFFSET, which vary by system.

The Follow-Up You'll Hear

Expect, "Now find the nth highest." Use DENSE_RANK() in a subquery, rank salaries descending, then filter where rank equals n. Say aloud that DENSE_RANK treats tied salaries as one distinct level, so two people at the top salary don't push the next salary to third place.

• Edge case: If there's no second distinct salary, state whether you should return NULL or no rows, and match the prompt.
• Trap: Sorting rows and taking the second row fails when the top salary repeats.
• Practice prompt: Rewrite the answer for third highest salary per department, with ties included.

This is the classic screen also known as LeetCode 176, Second Highest Salary. I recommend practicing it in a question bank under data analysis roles so you rehearse the explanation, not just the code.

2. Explain the Difference Between WHERE and HAVING

Use the sentence interviewers want to hear: WHERE filters rows, HAVING filters groups. WHERE runs before GROUP BY and aggregation, so it can't reference COUNT() or SUM(). HAVING runs after grouping, so it can filter on aggregate results.

Say you're reporting departments where the average salary for active employees is high. Use SELECT department, AVG(salary) FROM employees WHERE status = 'active' GROUP BY department HAVING AVG(salary) > 70000; The status filter reduces rows first, then the average condition removes whole departments.

Where Candidates Slip

The follow-up is often a rewrite test. If a HAVING condition doesn't use an aggregate, it usually belongs in WHERE, where it can reduce rows earlier. Also be ready to state the logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

Practical rule: If the condition can be judged on one row alone, it belongs in WHERE. If it needs the group total, it belongs in HAVING.

• Worked example: For customers with more than five recent orders, put the date filter in WHERE and COUNT(*) > 5 in HAVING.
• Practice prompt: Explain, in one breath, why WHERE COUNT(*) > 5 fails.

These fundamentals endure because, as the ACM historical account of 50 years of queries notes, Edgar F. Codd proposed the relational model in 1970 and SQL development followed in the 1970s.

3. Find Duplicate Records in a Table

Start by asking which columns define uniqueness. A duplicate email isn't automatically a duplicate customer, and the interviewer wants to hear you define the grain before you group anything. Then answer with SELECT email, COUNT(*) AS occurrences FROM users GROUP BY email HAVING COUNT(*) > 1;

That returns duplicate keys and counts, not the full rows. To inspect every offending record, join the grouped result back to users or use COUNT(*) OVER (PARTITION BY email) in a subquery and filter where that count is greater than one.

The Edge Case

Real sign-up data contains case and whitespace variants. Mention LOWER() and TRIM() before grouping, then ask whether the business treats those variants as the same person. A pipeline reload that double counts transactions is the same problem at a different grain.

• Follow-up: "Show me the actual rows, not just the emails." Answer with the window function version.
• Trap: Grouping by every column finds only exact row copies and misses duplicate business entities.
• Practice prompt: Find duplicate emails after normalizing case, then return the earliest account in each group.

A useful warning from the SQL Easy guide to SQL interview questions for data analysts is that joining at the wrong grain can repeat facts and inflate counts.

4. Find Customers With No Orders

This is an anti-join, and the clearest answer keeps customers as the base table. Write 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; Customers with no matching order survive the join, with NULL order columns.

Offer the equivalent NOT EXISTS version next, because interviewers often follow up on it. WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id) reads as exactly the business question and behaves predictably.

Why Not NOT IN

Here's the trap that separates memorized answers. If the subquery behind NOT IN returns even one NULL customer_id, the whole query can return no rows, because comparisons with NULL evaluate as unknown. NOT EXISTS doesn't have that failure mode.

• Use case: Dormant customers for re-engagement, or products that have never sold for an inventory review.
• Follow-up: Compare LEFT JOIN, NOT EXISTS, and NOT IN briefly, then recommend NOT EXISTS for safety.
• Practice prompt: Rewrite the query to find products with no returns, then explain the grain.

If you need the underlying join and constraint vocabulary refreshed, work through the related DBMS interview questions alongside this prompt.

5. Calculate a Running Total With Window Functions

Answer directly: SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM sales; The window keeps every row visible while the cumulative sum grows, unlike GROUP BY, which collapses rows.

State the frame clause on purpose. Without it, some databases default to RANGE semantics, which groups tied dates together and gives them the same running total. With ROWS, each physical row advances the total even when dates tie, so you must ask which behavior the business wants.

Make the Grain Explicit

If sales has many orders per day, decide whether the running total is per order row or per day. For a quarterly revenue view, aggregate to daily revenue first, then apply the window. For an account balance, order level may be correct.

• Follow-up: Reset the total per customer with PARTITION BY customer_id.
• Trap: Tied timestamps need a tiebreaker, such as order_id, for a stable order.
• Practice prompt: Produce a running balance per account and reconcile the final value to total credits minus debits.

6. Return the Top N Records Per Group

Don't reach for a correlated subquery first. Rank inside each group, then filter the rank in an outer query or CTE: DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) and keep rows where that rank is three or less. That's the top three paid employees per department, ties included.

Watch a worked ranking pattern before you practice variations with different tie rules.

Explain why the filter can't sit in the same WHERE clause. Window functions are evaluated after WHERE, so the rank doesn't exist yet at filtering time. A CTE makes that two step logic readable.

Ask About Ties First

Your ranking function is a business decision. DENSE_RANK can return more than three people if salaries tie at third place. ROW_NUMBER returns exactly three, but picks among ties unless you add a tiebreaker.

• Use case: Top five products per region, after aggregating sales to product grain.
• Follow-up: "What if we need exactly five rows?" Switch to ROW_NUMBER and name the tiebreaker.
• Practice prompt: Return the top two products per category, once with ties and once without.

7. Calculate Month-Over-Month Growth Using LAG

Aggregate first, compare second. Build a monthly CTE with DATE_TRUNC('month', order_date) and SUM(amount) as revenue, then LAG(revenue) OVER (ORDER BY month) pulls the prior month onto the same row. Growth is (revenue - prior_revenue) / NULLIF(prior_revenue, 0), so a zero base returns NULL rather than blowing up the division.

Name the dialect as you write. DATE_TRUNC is PostgreSQL style, while MySQL and SQL Server use different date functions. Stack Overflow's 2025 Developer Survey reported PostgreSQL at 55.6% and MySQL at 40.5% among respondents, so portability is worth stating before you choose syntax.

The Missing Month Trap

A month with no orders never reaches the grouped result, so LAG compares against the last month that had data and calls that month over month. Generate a calendar spine, left join monthly revenue to it, and coalesce missing revenue to zero only when the business treats “no sales” as zero rather than unknown.

• Use case: Monthly revenue in an executive dashboard.
• Follow-up: “Where did growth come from?” If repeat cadence is the question, track how often customers buy again as a separate metric instead of forcing it into the LAG query.
• Practice prompt: Add two empty months, then show the difference between LAG on raw aggregates and LAG on the calendar spine.

For more date-edge drills, especially rolling windows over missing dates, use the OwlApply data analyst interview questions guide.

8. Delete Duplicate Rows While Keeping One Copy

Slow down here, because this question tests judgment as much as syntax. Define the survivor first, usually the row with the lowest id per email. In PostgreSQL, write DELETE FROM person a USING person b WHERE a.email = b.email AND a.id > b.id; In MySQL, use the multi-table delete form with a self join on the same condition.

A portable pattern numbers rows with ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) and deletes where the number exceeds one, where the database supports deleting through that structure. Mention that support varies, so you'd confirm dialect behavior before running it.

What Strong Candidates Say Before Deleting

They refuse to delete blind. Run the equivalent SELECT first, check the row count, wrap the change in a transaction when possible, and know how you'd roll back.

Deleting data in an interview answer without a preview query and a rollback plan signals risk, not confidence.

• Use case: Duplicate accounts after a migration, or repeated event logs inflating conversion.
• Follow-up: "Which row do you keep if IDs don't reflect recency?" Ask for the rule, then order by created_at.
• Practice prompt: Write the SELECT preview and the DELETE as a pair, and ask whether analysts may modify source data at all.

9. Calculate the Median Salary or Value in SQL

Explain the statistic before the syntax. The median resists skew, so a few executive salaries won't drag it upward the way they drag the mean. Interviewers ask this partly to see whether you know which measure fits salaries, home prices, or ticket resolution times.

In PostgreSQL, answer with SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary FROM employees; That's clean, but it isn't universal. Say so, and name the database you're assuming.

The Portable Workaround

MySQL doesn't offer that function, so you rank rows with ROW_NUMBER, count them, and average the middle one or two values depending on whether the count is odd or even. That even count case is the edge case interviewers probe.

• Follow-up: "What happens with NULL salaries?" Note that percentile calculations typically exclude NULLs, and confirm that's intended.
• Trap: Returning the middle row without averaging two middle values for even counts.
• Practice prompt: Compute median resolution time per support tier and compare it with the mean.

For broader rehearsal beyond SQL syntax, pair this with the data analyst technical interview questions and answers so you can explain skew to a non-technical interviewer.

10. Explain RANK, DENSE_RANK, and ROW_NUMBER

Don't define these in abstract terms. Put three salaries on the table, 100, 100, 90, and walk through the outputs. ROW_NUMBER gives 1, 2, 3, because every row gets a unique number. RANK gives 1, 1, 3, sharing the rank and then skipping. DENSE_RANK gives 1, 1, 2, sharing without gaps.

That tiny example answers faster and more convincingly than memorized definitions. Then connect each function to a decision the business makes.

Pick by Business Meaning

Use ROW_NUMBER for deduplication, where you must keep exactly one record per key. Use RANK for competition style leaderboards where a tie consumes places. Use DENSE_RANK for nth highest value questions and tiered reporting where gaps would confuse readers.

• Edge case: ROW_NUMBER among ties is arbitrary unless you add a tiebreaker column, such as created_at or id.
• Follow-up: "Which one finds the second highest salary with ties?" Answer DENSE_RANK, and tie it back to question one.
• Practice prompt: Rank products by revenue three ways and explain which output you'd show a sales leader.

If ranking logic is your weak spot, drill it further with the advanced SQL interview questions after you can reproduce the three salary example from memory.

How the 10 SQL interview questions compare by skill tested, pitfall, and use case

Item	Core Skill	Key Pitfall	Common Use Case
Find the Second Highest Salary	Subqueries and ranking functions	Handling ties and no second distinct value	Salary ranking
Explain the Difference Between WHERE and HAVING	Filtering rows versus aggregated groups	Using HAVING for row-level filters	Filtering departments by average salary
Find Duplicate Records in a Table	GROUP BY and HAVING	Defining which columns make a record duplicate	Data quality checks
Find Customers With No Orders	Anti-joins and NULL handling	NOT IN can fail when the subquery contains NULL	Finding inactive customers
Calculate a Running Total With Window Functions	Cumulative window calculations	Default frame behavior with tied values	Tracking cumulative revenue
Return the Top N Records Per Group	Partitioned ranking functions	Choosing the right function when ties occur	Finding top products per region
Calculate Month-Over-Month Growth Using LAG	Time-series aggregation and comparison	Missing periods or division by zero	Reporting monthly revenue growth
Delete Duplicate Rows While Keeping One Copy	Safe data deletion and row ranking	Permanently deleting the wrong rows	Cleaning duplicate customer records
Calculate the Median Salary or Value in SQL	Percentiles and ordered data	Database support and even-sized datasets	Reporting median income or resolution time
Explain RANK, DENSE_RANK, and ROW_NUMBER	Ranking functions and tie behavior	Nondeterministic ordering when ties lack a tiebreaker	Leaderboards and deduplication

Turn These Ten Answers Into Interview Practice

Pick three questions today and answer them out loud without looking at the query. Start by stating the grain, the dialect, and the tie and NULL rules, then write the SQL. That order feels backwards at first, but it's exactly what interviewers reward, because it proves the metric will be right before the syntax exists.

Next, break your own answer on purpose. Add a tied salary, a missing month, a duplicate email with different capitalization, or a NULL foreign key, and check whether your query still returns what the business asked for. If it doesn't, you've found the follow-up question before the interviewer asks it. That habit separates candidates who memorized patterns from analysts who can defend a number in a dashboard review.

Then add a timer and a second dialect. Rewrite the median in syntax your target employer doesn't use, explain what changes, and keep the business definition fixed. Do the same for date truncation and deleting duplicates. Portability isn't trivia. It's you showing you won't ship a correct query in the wrong system or a wrong metric in the right one.

Reconcile one result every time you practice. Match your running total to a simple sum, your top N list to a grouped total, or your month-over-month output to the raw monthly revenue beside it. Interviewers trust candidates who verify, and hiring managers trust analysts whose numbers survive that check on the job.

We built our question bank for this kind of rehearsal, with search across question text and filters by question type, and full access is included on paid plans. Our full question bank contains 10,000 questions, and the data analyst list covers SQL, metrics, dashboards, data quality, and stakeholder communication. Use it to rotate prompts so you're reasoning, not reciting.

Finish with one mock round where someone interrupts you mid-answer with the follow-ups in this guide. If you can hold your assumptions, adjust for ties, and still finish cleanly, you're ready for the editor to open.

---

Ready to rehearse these SQL prompts under real interview pressure, with mock sessions and a searchable bank of data analyst questions on joins, window functions, and metrics? Visit Interview Pilot to practice out loud, get feedback on your answers, and walk into your next technical screen with your assumptions ready.