What SQL Interviews Actually Test
Most analyst SQL interviews are not testing whether you memorized syntax. They are testing whether you can translate a business question into a correct query, and whether you understand what your query is actually doing to the underlying data. Expect a mix of writing queries from a prompt, reading and debugging someone else's query, and explaining what a query returns given a sample table. Some companies also ask you to optimize a slow query or explain an execution plan at a high level.
The format varies. Some interviews use a shared SQL editor connected to a real database so you can run and check your work. Others are whiteboard or shared-doc style, where you write SQL without running it. Ask which format to expect before the interview so you can practice the right way.
Joins and Aggregations You Need Cold
You should be able to write inner, left, right, and full outer joins without hesitating, and explain the difference in plain language: an inner join only keeps rows that match on both sides, a left join keeps every row from the left table and fills in nulls when there is no match. A common trap question is what happens to row counts after a join when the join key has duplicates on one side, since that can silently multiply rows and inflate a metric.
Know GROUP BY and HAVING well enough to explain why HAVING filters after aggregation while WHERE filters before it. Practice writing queries that combine a join with a GROUP BY, since that combination is where most real analyst work actually happens: joining orders to customers, then aggregating revenue by customer segment, for example.
- Self-joins for comparing rows within the same table, like finding employees who earn more than their manager
- COUNT(DISTINCT column) versus COUNT(column) and when the difference matters
- NULL handling in aggregates, since NULL is excluded from COUNT and AVG by default
Window Functions Come Up More Than You Would Think
Window functions separate analysts who learned SQL from a tutorial from analysts who use it daily. Be comfortable with ROW_NUMBER, RANK, and DENSE_RANK, and be able to explain the difference between them when there are ties. A very common interview task is 'find the top N rows per group', which is solved with ROW_NUMBER() OVER (PARTITION BY group ORDER BY value DESC) wrapped in a subquery or CTE, then filtering where the row number equals 1 or is less than N.
Also practice LAG and LEAD for comparing a row to the previous or next row, which comes up in questions about month-over-month change or detecting gaps in a sequence. Running totals with SUM() OVER (ORDER BY date) are another frequent ask. If you can explain PARTITION BY as 'restart the calculation for each group' in your own words, you are in good shape.
Query Performance and Reading an Execution Plan
Senior analyst roles often probe whether you understand why a query is slow, not just whether it returns the right answer. You do not need to be a database administrator, but you should know that indexes speed up lookups and joins on the indexed columns, that a query scanning an entire large table is usually the first suspect for slowness, and that functions applied to a column in a WHERE clause can prevent an index from being used.
If asked to read an EXPLAIN plan, focus on identifying whether the database is doing a full table scan versus an index scan, and roughly how many rows are being processed at each step. You do not need to memorize every operator name for every database engine, just be able to reason about where the cost is coming from.
How to Practice Without a Real Database in Front of You
Set up a free local database or a browser-based SQL sandbox and load in a small sample dataset, even a simple orders and customers table you make up yourself. Write ten to fifteen realistic business questions against it: revenue by month, top customers by spend, customers who purchased in one period but not the next. Actually running your queries and seeing wrong results teaches you far more than reading solutions.
When you review someone else's SQL solution, do not just check whether it produced the right output. Read it line by line and predict what each clause does before running it. That habit is exactly what interviewers are checking for when they hand you a query and ask you to explain it.
Common Query Mistakes That Trip Up Analysts
A handful of mistakes show up constantly in analyst SQL interviews, and knowing them ahead of time means you can actively guard against them while you write. The most common is a join that quietly fans out row counts because the join key has duplicates on one side, which can inflate a sum or count without any error being thrown. Another is comparing a column to NULL using equals instead of IS NULL, which silently returns no rows instead of the rows you actually wanted, since NULL is never equal to anything in standard SQL, including itself.
- Using SELECT * in a query meant for a report, which breaks or produces confusing output later if someone adds a column to the table
- Grouping by a raw timestamp instead of truncating it to a day or month, which splits what should be one group into many because of second-level differences
- Writing a correlated subquery that runs once per outer row when a join or a window function would do the same work in a single pass
- Forgetting that COUNT(column) skips NULL values while COUNT(*) does not, which can quietly change a result depending on which one you use
If you catch yourself about to write one of these, saying out loud that you are deliberately avoiding it, for example noting you are using IS NULL instead of equals because of how SQL treats NULL comparisons, actually turns a potential mistake into a signal of strong fundamentals.