1. Write a query to find the second highest salary from an Employees table.
Use a subquery with MAX where salary < (SELECT MAX(salary)), or DENSE_RANK() = 2.
SQL interview questions and answers for data analysts, data engineers and data scientists
SQL is the one round almost every data role shares, analysts, scientists, engineers all get tested on it. These are real SQL interview questions asked for data roles, with worked answers: window functions, joins, aggregation, and the gotchas interviewers actually probe.
How the SQL round is run, and what it scores
What the round actually tests
Whether you can express a business question as a query without hesitating, and whether you know what your own query does to the row count. Almost nobody fails a SQL round on syntax. They fail on a join that silently fans out, a filter in the WHERE clause that turns a LEFT JOIN into an INNER one, or an aggregate that quietly drops the rows with NULLs.
The bar rises with seniority in one specific way: a junior candidate is asked to produce the number, a senior candidate is asked what would make the number wrong.
The questions that repeat
Second-highest salary. Running totals and moving averages. Month-over-month retention or cohort tables. Consecutive-day streaks. Top N within each group. Deduplication where you must say which row you kept and why. Median without a median function. These recur across companies with the column names changed.
Most of them are one window function away from trivial, which is why window functions are the single highest-return thing to drill: ROW_NUMBER, RANK and DENSE_RANK and the difference between them, LAG and LEAD, and a frame clause you can write from memory rather than recognize.
What interviewers score
State the grain before you write anything: one row per what. Interviewers watch for this because it is the habit that prevents fan-out. Then the query, then the edge cases you raise unprompted, ties, NULLs, empty groups, duplicate keys.
Say the complexity or the scan pattern if the table is large, and say which index would help. A candidate who writes a correct query and then explains why it would be slow on a billion rows is scored above one who writes the same query and stops.
How to prepare, and in what order
Window functions first, because they carry the most questions. Then joins with an honest understanding of what each one does to cardinality, then aggregation with GROUP BY and HAVING, then date arithmetic, which is where dialect differences bite. CTEs last, since they are readability rather than capability.
Practice out loud and against a clock. The round is usually thirty to forty minutes for two or three problems, and the failure mode is silence: thinking correctly for four minutes while the interviewer cannot tell whether you are stuck. Narrate the grain, the join, then the filter.
Where candidates lose it
Putting a condition on the right table in WHERE instead of ON, which converts a LEFT JOIN into an INNER JOIN and drops exactly the rows the question was about. Using COUNT where COUNT DISTINCT was meant, then reporting line items as orders. Assuming a join key is unique without saying so. And reaching for a subquery where a window function is both shorter and faster, which reads as unfamiliarity rather than preference.
15 SQL questions
2. Given orders(user_id, order_date, amount), compute 7-day rolling revenue per day.
Aggregate orders to one row per day, then use SUM(daily_revenue) over a six-day date interval plus the current day. Use a date spine when the SQL engine cannot frame by interval.
3. Compute month-over-month user retention from an events table.
Self-join cohort month vs activity month, count distinct users, divide by cohort size.
4. Find users who logged in 3 or more consecutive days.
Use ROW_NUMBER and date - row_number as a group key, then count per group.
5. Explain the difference between INNER, LEFT, and FULL OUTER JOIN.
INNER keeps only matches, LEFT keeps all left rows plus matched right rows, FULL keeps all rows from both sides.
6. Deduplicate rows keeping the most recent record per user.
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) = 1.
7. What is the difference between WHERE and HAVING?
WHERE filters rows before aggregation, HAVING filters groups after GROUP BY.
8. Pivot a long table of (user, metric, value) into one row per user.
Use conditional aggregation: MAX(CASE WHEN metric='x' THEN value END).
9. Calculate the median order value using SQL.
Use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) where supported.
10. Find the top 3 products by revenue within each category.
RANK()/ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) <= 3.
11. fact_orders has an order_ts. dim_customer is SCD Type 2 with valid_from/valid_to. Join each order to the customer attributes that were current when the order was placed.
A range join, not an equality join: ON o.customer_id = d.customer_id AND o.order_ts >= d.valid_from AND o.order_ts < d.valid_to. The half-open interval is what stops an order matching two versions on a boundary. Joining on the customer key alone, or filtering d.is_current = true, silently reports today's attributes for historical orders, the classic SCD mistake, and the reason last year's revenue-by-segment number quietly changes.
12. A daily batch job loads into a target table. Make re-running it for the same day safe, with no duplicates.
MERGE on a stable business key, or delete-then-insert scoped to that day's partition inside one transaction. The job has to be idempotent rather than append-only: keyed on something like (entity_id, load_date) so a re-run replaces its own previous output instead of stacking on top of it. INSERT-only pipelines look correct until the first retry, and then every downstream metric double-counts.
13. Write a query to find gaps in a daily sequence: dates with no rows in a metrics table.
Generate a full date spine (a calendar table or generate_series) and LEFT JOIN the metrics table onto it. The days where the metric side is NULL are the missing ones. Never infer no-data from absent rows without a spine, you cannot otherwise tell zero from missing.
14. What is the difference between a measure and a dimension, and why does it matter for a dashboard?
A dimension is what you slice by (date, region, product), a measure is what you aggregate (revenue, count, rate). Mixing them up (averaging an already-averaged rate, or summing a ratio) is the classic BI bug. A measure has to aggregate correctly at every grain the user can drill to.
15. How would you build a month-over-month growth view in SQL for a dashboard?
Aggregate to month, then LAG the metric over ordered months: (this_month - LAG(metric) OVER (ORDER BY month)) / LAG(metric) OVER (ORDER BY month). Guard against divide-by-zero, and join to a month spine so a missing month reads as a gap rather than being silently skipped.