Advanced SQL Interview Questions for 2026: 8 Key Answers

SQL shows up widely in data-role interviews, where candidates need to explain how their queries behave beyond clean sample data. Consider a live coding round: you write a query, then the interviewer asks how it handles tied values, NULL timestamps, or duplicates. The follow-up tests judgment as much as syntax.

Strong candidates state the table's grain, identify assumptions about missing or duplicate values, and explain why they chose a ranking function, window frame, or isolation level. A job posting analysis reported by COITB offers one view of SQL's presence in data analyst postings, though hiring requirements vary by role and source.

SQL began at IBM in the early 1970s as SEQUEL. ANSI published the SQL standard as ANSI X3.135-1986, and ISO published ISO 9075 in 1987, according to ANSI's account of the SQL standard. SQL is declarative: you specify the result, while the database engine determines how to produce it.

The questions below focus on the follow-up interviewers use to distinguish a memorized pattern from a reasoned answer. For each, be ready to explain the query's assumptions and the edge case that could change the result.

1. How Would You Find the Second-Highest Salary in Each Department?

Start by asking what second highest means. Is it the second distinct salary, or the second employee after sorting? That one clarification decides your function, and interviewers listen for it before they care about the query itself.

For the second distinct salary, use DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) in a CTE, then filter where the rank equals 2 in an outer query. Tied employees share a rank and no rank is skipped, so two people at the top salary don't push the next salary to rank 3. For exactly one row per department, ROW_NUMBER() is the better choice, with a stable secondary sort such as employee_id to make the pick deterministic.

The Follow Up on Ties and Nulls

You can't filter a window function result directly in WHERE in most dialects, which is why the CTE or subquery matters. Say that out loud. Then handle NULL salaries explicitly, since the ordering of nulls varies by engine and an unknown salary shouldn't be treated as the highest or lowest.

Practical rule: If the interviewer says second highest salary, default to distinct values and DENSE_RANK(). If they say second highest paid employee, switch to ROW_NUMBER() and defend your tie breaker.

A compensation analyst uses this same pattern to compare pay bands across departments while keeping employee level rows intact. That retention of rows is the point. As PostgreSQL's window function tutorial distinguishes it, a windowed calculation preserves each row while calculating over a related set, whereas GROUP BY with a regular aggregate collapses rows into fewer output rows.

RANK() sits between the two, sharing ranks for ties but leaving gaps afterward. Name all three functions and when you'd reject each one. That comparison, not the final SELECT, is what separates a memorized answer from a reasoned one.

2. How Do You Return the Latest Record for Each Customer?

This is deduplication disguised as a simple lookup. History tables hold many rows per customer, profile updates, order statuses, account events, and you need exactly one current row without losing customers who have unusual histories.

Rank rows inside each customer partition and keep the first. A CTE with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC, record_id DESC), filtered with WHERE rn = 1, does the job cleanly. The record_id tie breaker matters because two updates can share a timestamp, especially after batch loads or backfills, and without it your result can change between runs.

One Row or Every Tied Row

Ask whether ties should return one chosen row or all rows tied for the latest timestamp. If all ties are required, RANK() or a join back to MAX(updated_at) per customer is more honest than forcing ROW_NUMBER() to pick a winner arbitrarily. Interviewers often change this requirement mid question to see if you'll adapt instead of defending your first query.

• Null timestamps: Decide whether a missing updated_at means oldest, unknown, or excluded, and filter or order accordingly.
• Late arriving records: A row inserted today can describe an event from last month, so confirm whether latest means event time or load time.
• Deleted or superseded rows: If the table has status flags, filter them before ranking, not after.

In production this pattern selects each customer's current profile or most recent order status for downstream reporting. The failure mode is silent. Pick the wrong ordering column and every dashboard still renders, just with stale addresses and closed accounts showing as active. Naming that risk shows you've debugged it before.

3. How Would You Calculate a Running Total and a Moving Average?

A running total and a moving average look similar in syntax and behave very differently in interviews. Both live or die on frame semantics, the part candidates most often leave to database defaults.

Write the total explicitly as SUM(amount) OVER (PARTITION BY account_id ORDER BY transaction_date, transaction_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Spelling out the frame prevents surprises when peers with the same ordering value get grouped together under default RANGE behavior in some engines. Finance teams use this exact shape for cumulative balances, and it's also a staple in broader preparation for data analyst technical interview questions where time series logic comes up early.

A moving average narrows the frame, for example the current row plus six preceding rows. That smooths daily sign ups or sales, but it answers a row question, not a calendar question.

Rows Versus Calendar Time

If weekends or missing days are absent from the data, a seven row average isn't a seven day average. For a true calendar window, aggregate to one row per day first, then use a date range frame where your database supports it or join to a calendar table so missing dates exist as explicit rows.

• Specify the frame: Never rely on an implied default you can't explain.
• Order deterministically: Add a unique tie breaker for same day transactions.
• State the grain: Say whether you're averaging transactions, days, or accounts.

The follow up is usually about gaps. Candidates who've only seen clean sample data freeze. Candidates who mention missing dates before being asked sound like analysts who've shipped the report.

4. How Do You Identify Gaps and Islands in a Sequence of Dates or Events?

Islands are consecutive runs. Gaps are what's missing between them. Product analysts use this to find streaks of daily activity, and operations teams use it to catch days when reporting failed to run without notice.

The classic date islands trick is elegant once you see it. Assign ROW_NUMBER() over distinct dates per user, subtract that number from the date, and consecutive dates produce the same resulting value. Group by user and that derived key, and each group is one island with a start date, end date, and length from MIN, MAX, and COUNT.

When Events Are Not Daily Dates

Event sequences rarely arrive as neat calendar days. For irregular spacing, use LAG(event_time) to compare each row with its predecessor, flag rows where the gap exceeds a business defined threshold, then cumulatively sum those flags to build a run identifier. A new session after thirty quiet minutes and a new streak after a missed day are the same pattern with different thresholds.

Prepare the data before applying either technique. Convert timestamps to the intended date grain, remove duplicate dates per entity first, and decide how null event times are treated. Duplicates are the most common reason the subtraction trick appears to fail in a live round.

Test the first row, the last row, a single day island, and a sequence with duplicates. If your logic survives those four, it probably survives the interviewer's version too.

Explain the invariant in plain language. Consecutive values move together, so subtracting a matching sequence holds their group key constant. Interviewers reward that sentence more than flawless date arithmetic syntax in an unfamiliar dialect.

5. How Would You Compute a Conversion Funnel From Event Data?

A user signs up, activates, then purchases, but the event log contains repeated signups and events arriving out of order. Before writing SQL, define which events count, the required order, the time window, and whether the analysis tracks a user or a session. These choices determine what “converted” means; a query can run correctly and still answer the wrong business question.

For a user-level funnel, aggregate each step's qualifying timestamp first. For example, calculate signup_time as MIN(CASE WHEN event_name = 'signup' THEN event_time END) and use the same pattern for activation and purchase. Then enforce sequence explicitly: count activation only when activation_time > signup_time, and purchase only when purchase_time > activation_time. If signup occurred at 10:00, activation at 10:05, and purchase at 10:20, all three steps qualify. If purchase occurred at 09:55, it does not qualify, even though the user has a purchase event.

Where Funnels Quietly Go Wrong

The likely follow-up is whether the funnel must happen in one session or can span sessions. Set that rule, choose a window measured in hours or days, and decide whether a repeated first-step event restarts the funnel. Users may repeat events, skip steps on mobile, or convert across devices, so those choices affect who advances. Report raw counts alongside clearly labeled step to step and overall conversion rates; they answer different questions.

• Write the contract first: State event order, time window, and unit of analysis.
• Validate with timelines: Trace a handful of users' event histories by hand.
• Check duplicates: Count distinct users or sessions as defined, rather than raw event rows.

Metric definition is part of the SQL interview, not decoration. Commentary on what SQL interviews are really testing discusses case-style assessments built around defining metrics. For a signup-to-trial-to-paid funnel, explain the event rules, how you would reconcile the counts, and how the result informs a decision. That reasoning shows whether the query measures the intended conversion, not just whether it returns rows.

6. How Do Recursive CTEs Traverse Hierarchies or Graphs?

Recursion earns its place when depth is unknown in advance. You can't write seven self joins for an org chart that might be twelve levels deep next quarter. A recursive CTE handles the traversal in one structure.

It has two parts you should name precisely. The anchor member selects starting rows, such as a top level manager or a root category. The recursive member joins the CTE's current results back to the base table to fetch the next level, employees whose manager_id matches the current employee_id, combined with UNION ALL until no new rows appear. This hierarchy pattern, alongside other structured problem solving, also appears in preparation for software engineer technical interview questions.

Termination, Cycles, and Direction

State your join direction before writing it. Traversing down from a manager to all reports is a different join from walking up from an employee to the chief executive, and mixing them up produces empty results that waste interview time. Track a level or depth column as you go, since interviewers almost always ask for it next.

Real hierarchies contain cycles, especially in graph like data such as category relationships or bill of materials expansions where a component can appear under multiple parents. Track visited identifiers or the accumulated path, and stop when a node repeats. In a strict tree that check is cheap insurance, and in a graph it's the difference between finishing and looping forever.

Add a sensible depth limit where the business allows one, and mention indexes on the parent and child join columns. Each recursive step is effectively another join against the table, so an unindexed parent column turns a neat answer into repeated full scans on an HR report listing every indirect report under a senior manager.

7. What Is the Difference Between Transaction Isolation Levels, and How Do They Prevent Anomalies?

Isolation levels govern what one transaction can see while others are writing. Candidates often recite the four standard levels in order and stop. Interviewers want the anomalies each level permits or prevents, and the cost of preventing them.

A dirty read means reading uncommitted data that might roll back. A non repeatable read means re reading a row and finding it changed by a committed transaction. A phantom read means re running a range query and finding new rows inserted by someone else. Stronger isolation removes more of these surprises and typically costs more locking, blocking, or retries.

The video below walks through transaction behavior in a visual format that pairs well with the definitions above.

Implementation matters as much as the standard names. Many systems use multiversion concurrency control, where readers see a snapshot instead of waiting for writers, and locking behavior and snapshot semantics vary between PostgreSQL, MySQL, and SQL Server. A level name doesn't guarantee identical behavior across engines, so say you'd verify the target database documentation and test with concurrent transactions.

• Start from the anomaly: Ask which inconsistency the application cannot tolerate, rather than picking a level by habit.
• Keep transactions short: Long transactions hold resources and amplify blocking under any level.
• Plan for retries: Serialization failures and deadlocks need handling in application code, not surprise.

A payment or inventory workflow usually justifies stronger consistency because two transactions must not both spend the same balance or stock. A dashboard query can often tolerate slightly stale data. Making that trade off explicitly is the senior answer.

8. How Would You Diagnose and Optimize a Slow SQL Query?

Don't guess. Measure. A strong answer starts with the actual execution plan and observed latency, then works through scans, join strategies, row estimates, sorting, and data volume in that plan before proposing any fix.

Run EXPLAIN, or your engine's actual plan facility, and compare estimated row counts with actual counts when available. Large gaps point to stale statistics or poor cardinality estimates, which lead the optimizer to choose bad join orders. Then look for avoidable full scans on large tables, repeated work, implicit type conversions, and functions wrapped around indexed filter columns, each of which can prevent index use. Related integration and pipeline performance topics surface in interview questions on SSIS, where slow lookups cause similar symptoms.

Fix One Thing, Then Remeasure

For a report filtering orders by customer and date, a composite index can help when its column order matches the filtering and access pattern. It's not free. Every index adds write cost and storage, so weigh read gains against insert and update load before recommending it.

Change one factor at a time and benchmark with representative data. Three simultaneous fixes and a faster result teach you nothing about which one worked.

Representative matters. The TPC DS benchmark suite contains 99 complex queries across 24 tables in one documented implementation described in StarRocks' TPC DS benchmark documentation, modeling decision support workloads rather than toy statements. You don't need that scale in an interview, but citing workload dependence, spills, concurrency, and cost shows production reasoning instead of memorized tuning rules.

How the eight advanced SQL interview questions compare by core technique, typical use, and common pitfall

Item	Core SQL Technique	Typical Use Case	Common Pitfall
Second-highest salary per department	Window ranking with DENSE_RANK() or ROW_NUMBER() partitioned by department	Compare salaries while retaining employee-level details	Ties require an explicit ranking rule; window results often need an outer query or CTE
Latest record per customer	ROW_NUMBER() partitioned by customer and ordered by timestamp descending	Select a current profile, latest status, or most recent event	Tied timestamps can make the selected row nondeterministic
Running total and moving average	Window aggregates with explicit ordering and frame clauses	Track balances or smooth sales and sign-ups over time	A row-based frame may not represent a fixed calendar-time interval
Gaps and islands in dates or events	Use LAG() or date-minus-row-number grouping to identify runs and gaps	Find consecutive activity periods or missing reporting dates	Duplicates, inconsistent date grain, or unnormalized timestamps can distort results
Conversion funnel from event data	Conditional aggregation or ordered event matching	Measure progression through product or subscription steps	Simple counts may include users who completed steps out of order
Recursive CTEs for hierarchies or graphs	Anchor query plus recursive member in a CTE	Traverse reporting lines, category trees, or component structures	Cycles or large graphs can cause excessive or unbounded traversal
Transaction isolation levels	Configure transaction isolation to control concurrent reads and writes	Protect consistency in payment or inventory workflows	Database behavior varies, and stronger isolation can reduce concurrency
Diagnose and optimize a slow SQL query	Inspect execution plans, then measure targeted changes	Improve query latency for reports or application workloads	Guessing at indexes or testing only small datasets can mislead; indexes also add write costs

From Model Answers to Interview-Ready Habits

Knowing eight patterns isn't the same as performing under time pressure. The gap is usually verbal. Candidates can write DENSE_RANK() correctly at home and then go quiet when asked why they didn't choose RANK(), or how their funnel changes if conversion must happen inside one session. Practice has to include the explanation, not only the query.

Pick two questions a day. Write the query by hand without autocomplete, then answer the follow ups out loud. How are ties handled, what changes with nulls or duplicates, what breaks at larger volume, and which index or isolation level would you choose and why? Keep each spoken answer short enough that an interviewer can interrupt with a harder variant, because a good one will.

Dialect awareness belongs in that rehearsal too. Interview preparation should prioritize the systems teams run, and Stack Overflow's 2025 Developer Survey reports usage of PostgreSQL at 55.6%, MySQL at 40.5%, SQLite at 37.5%, and Microsoft SQL Server at 30.1% across respondents in its 2025 technology results. Learn portable SQL first, including window functions, CTEs, null semantics, transactions, and indexing, then layer on engine-specific behavior such as PostgreSQL execution plans or SQL Server clustered and nonclustered indexes. Translating one solution across dialects is itself a credible interview answer.

You should also prepare for AI in the room, even when no tool is present. Interviewers increasingly ask how you'd prove an AI generated query is correct before deploying it. That skepticism is well placed. In Stack Overflow's 2025 developer results, 36% of surveyed developers learned to use AI enabled tools for work or career advancement in the previous year, yet 46% said they do not trust AI tool output, up from 31% the prior year. Strong answers cover schema and grain validation, null and duplicate checks, adversarial test cases, execution plans, permission boundaries, and comparison against trusted totals. AI drafts faster, but it makes silent semantic errors cheaper to produce and more expensive to miss.

That accountability point generalizes beyond AI. SQL was required in 70% of data related roles reviewed by Dataspace, as reported in Dataspace's review of top data skills in 2024, making it the most prevalent skill in that sample. Prevalence means interviewers can assume syntax and spend their time on metric definition, validation, and business consequences instead. Define the metric, state your assumptions, reconcile against a known total, and say what decision the result would change.

Rehearsing these explanations in a timed setting, such as the AI Mock Interview sessions at interviewpilot.app, builds calm delivery that written practice alone doesn't. Interview Pilot is one option for that kind of structured rehearsal while you work through advanced SQL interview questions. The goal isn't to memorize eight model answers. It's to make your reasoning audible, so the follow up question feels like the easier half of the conversation.

---

If the follow-up questions are where you lose momentum, practice them out loud instead of rereading queries. Interview Pilot offers mock interview sessions and a searchable question bank so you can rehearse advanced SQL interview questions, from window functions to slow query diagnosis, under realistic time pressure. Visit Interview Pilot to run your next practice round before your actual interview.