SQL interviews for data roles test more than syntax. Candidates must reason about table grain, joins, missing data, aggregation, window functions, dates, duplicates, and the business meaning of a result. Data Analyst interviews usually emphasize analytical queries and metrics; Data Scientist interviews often combine SQL with experimentation or product cases; Data Engineer interviews add modeling, reliability, and performance.
How to use these SQL interview questions
Choose questions by role, then move from beginner to advanced. Before reading an answer, state the output grain, keys, assumptions, NULL behavior, tie handling, and validation check. The examples use portable concepts where possible; always adapt syntax to the interview dialect.
Which SQL topics should each data role prepare?
| Role | Priorities |
|---|---|
| Data Analyst | Joins, aggregation, dates, windows, funnels, retention, and metric definitions |
| Data Scientist | Analytical SQL, experiment units, feature extraction, missingness, and leakage prevention |
| Data Engineer | Modeling, incremental processing, late data, idempotency, plans, partitioning, and transactions |
| Analytics Engineer | Grain, semantic models, testing, dimensional modeling, and maintainable transformations |
100 SQL questions by topic
- SQL fundamentals and NULL behavior (10)
- Aggregation and conditional logic (10)
- Joins and relationship grain (12)
- Subqueries, CTEs, and set operations (10)
- Window functions (15)
- Dates, text, and semi-structured data (10)
- Product and business analytics (13)
- Data modeling and warehousing (10)
- Query plans and performance (7)
- Transactions and reliability (3)
SQL fundamentals and NULL behavior
1. What is SQL's logical query-processing order?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: A useful conceptual order is FROM and JOIN, WHERE, GROUP BY, HAVING, window calculations, SELECT, DISTINCT, ORDER BY, then LIMIT or FETCH. Optimizers may execute differently, but this order explains why a SELECT alias is often unavailable in WHERE.
Common mistake: Confusing written clause order with logical evaluation order.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
2. What is the difference between WHERE and HAVING?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: WHERE filters rows before aggregation; HAVING filters groups after GROUP BY. Use WHERE for row-level conditions whenever possible, and HAVING for conditions that depend on aggregates.
Common mistake: Using HAVING for ordinary row filters and doing unnecessary aggregation work.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
3. How does NULL behave in SQL?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: NULL represents an unknown or missing value and participates in three-valued logic: comparisons such as value = NULL are not true. Use IS NULL or IS NOT NULL, and decide explicitly whether missing rows belong in metrics.
Common mistake: Testing NULL with = or !=.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
4. When should you use COALESCE?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: COALESCE returns the first non-NULL expression and is useful for display defaults or intentionally defined fallback logic. Do not use it to hide missingness before deciding whether NULL and zero have the same business meaning.
Common mistake: Replacing NULL with zero when missing and zero mean different things.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
5. How do you use CASE in a query?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: CASE adds conditional logic to SELECT, ORDER BY, aggregation, and other expressions. Make branches mutually understandable, include an intentional ELSE, and ensure every branch resolves to a compatible type.
Common mistake: Omitting ELSE and silently producing NULL for unmatched rows.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
6. What does DISTINCT do, and when is it a warning sign?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: DISTINCT removes duplicate result rows across all selected columns. It is appropriate when uniqueness is part of the requested output, but it can conceal a bad join or wrong grain when added only to make inflated counts disappear.
Common mistake: Using DISTINCT as a repair for an unexplained many-to-many join.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
7. What is the difference between UNION and UNION ALL?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: UNION ALL concatenates compatible result sets and preserves duplicates; UNION also deduplicates the combined result. Prefer UNION ALL unless the requested semantics require set deduplication.
Common mistake: Paying the cost of UNION or deleting legitimate duplicates without intending to.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
8. How do DELETE, TRUNCATE, and DROP differ?
Roles: Data Engineer, Analytics Engineer · Difficulty: Beginner · Dialect: Dialect-specific
Answer: DELETE removes qualifying rows and commonly supports a WHERE clause. TRUNCATE removes all rows using engine-specific semantics, while DROP removes the table object itself; transaction, trigger, identity, and permission behavior varies by database.
Common mistake: Presenting transaction or rollback behavior as portable across engines.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
9. What are primary and foreign keys?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: A primary key identifies each row in a table; a foreign key references a candidate or primary key in another table and can enforce referential integrity. Analytical warehouses may document rather than enforce these constraints, so validate uniqueness and relationships explicitly.
Common mistake: Assuming a declared key is clean without checking the data.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
10. How do CHAR and VARCHAR differ?
Roles: Data Engineer · Difficulty: Beginner · Dialect: Dialect-specific
Answer: CHAR is fixed length and VARCHAR is variable length, but storage, padding, comparison, and index behavior differ by engine. In an interview, state the target database before making performance claims.
Common mistake: Claiming one type is universally faster.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
Aggregation and conditional logic
11. What is the difference between COUNT(*) and COUNT(column)?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: COUNT(*) counts rows, while COUNT(column) counts rows where that expression is non-NULL. COUNT(DISTINCT column) counts distinct non-NULL values and may be substantially more expensive.
Common mistake: Using COUNT(nullable_column) when the requested denominator is all rows.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
12. What is conditional aggregation?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: Conditional aggregation places CASE or a dialect-specific filter inside an aggregate to compute multiple segmented metrics in one grouped query. Define the denominator separately so counts and rates remain interpretable.
Common mistake: Returning NULL instead of zero because the CASE has no ELSE.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
13. How should GROUP BY columns relate to the output grain?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: The GROUP BY keys define one output row per unique key combination. State that target grain before writing aggregates, and verify every selected non-aggregate expression is functionally dependent on it.
Common mistake: Grouping by an extra column that silently changes one row per user into one row per user-event type.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
14. How do you find the second-highest distinct salary?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: Rank distinct salaries in descending order with DENSE_RANK and keep rank 2, or select MAX(salary) below the overall maximum. Clarify whether ties should return all employees and what to return when fewer than two distinct salaries exist.
Common mistake: Using OFFSET without defining tie behavior or deterministic ordering.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
15. How do aggregate functions treat NULL?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: Most aggregates ignore NULL inputs; COUNT(*) is the important row-count exception. AVG(x) therefore divides by non-NULL x values, not all rows, so disclose whether missing values should be excluded, imputed, or counted separately.
Common mistake: Assuming AVG treats NULL as zero.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
16. How do you calculate each category's percentage of a total?
Roles: Data Analyst, Data Scientist · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Aggregate to category grain, then divide each category value by a windowed sum across the aggregated rows. Use decimal arithmetic and guard against a zero denominator.
Common mistake: Dividing row-level values before establishing the requested category grain.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
17. How do you identify duplicate business keys?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: Group by the expected business key and keep groups with COUNT(*) greater than one. Then inspect differing attributes and ingestion timestamps before choosing a deterministic survivor.
Common mistake: Calling rows duplicates based on every column when the business key is narrower.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
18. How can one query calculate several metrics at the same grain?
Roles: Data Analyst, Data Scientist · Difficulty: Beginner · Dialect: Portable SQL
Answer: Group once at the target grain and use separate aggregates, including conditional aggregates, for each metric. Confirm that every metric shares a compatible denominator and time window.
Common mistake: Joining separately aggregated subqueries at incompatible grains.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
19. How do you divide safely in SQL?
Roles: Data Analyst, Data Scientist · Difficulty: Beginner · Dialect: Portable SQL
Answer: Use NULLIF(denominator, 0) or the engine's safe-divide function, then decide whether an undefined rate should stay NULL or display as zero. Cast integer inputs when the dialect would otherwise perform integer division.
Common mistake: Converting an undefined rate to zero without stating that business choice.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
20. When are GROUPING SETS, ROLLUP, or CUBE useful?
Roles: Data Analyst, Data Engineer · Difficulty: Advanced · Dialect: Dialect-specific
Answer: They compute several aggregation levels in one query, such as detail, subtotal, and grand total. Use GROUPING metadata to distinguish generated subtotal NULLs from real NULL dimension values.
Common mistake: Treating every NULL in a rollup output as a subtotal.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
Joins and relationship grain
21. What is the difference between INNER JOIN and LEFT JOIN?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: INNER JOIN keeps only matched row combinations; LEFT JOIN preserves every left row and fills unmatched right columns with NULL. The output row count can exceed either input when keys are not unique.
Common mistake: Assuming LEFT JOIN preserves the left table's row count.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
22. When would you use FULL OUTER JOIN or CROSS JOIN?
Roles: All data roles · Difficulty: Intermediate · Dialect: Portable SQL
Answer: FULL OUTER JOIN keeps unmatched rows from both inputs and is useful for reconciliation. CROSS JOIN creates every pair and is appropriate for intentional grids such as dates by segments, but dangerous when accidental.
Common mistake: Creating a Cartesian product because a join predicate is missing.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
23. Why can a many-to-many join inflate revenue?
Roles: All data roles · Difficulty: Intermediate · Dialect: Portable SQL
Answer: If both sides contain multiple rows per join key, every matching combination is produced and additive measures repeat. Profile key cardinality, aggregate to compatible grains, or model the relationship explicitly before joining.
Common mistake: Fixing inflated sums with DISTINCT instead of repairing the relationship.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
24. How do you find rows in one table with no match in another?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: Use NOT EXISTS with a correlated key predicate, or LEFT JOIN followed by a right-key IS NULL check. NOT EXISTS is often clearer and avoids NULL pitfalls associated with NOT IN.
Common mistake: Using NOT IN when the subquery can contain NULL.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
25. What is a self join?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: A self join relates rows within the same table, such as employees to managers or events to prior states. Use clear aliases and verify whether the relationship is one-to-one, one-to-many, or recursive.
Common mistake: Joining a hierarchy only one level when the question requires arbitrary depth.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
26. How do you join three or more tables safely?
Roles: All data roles · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Write down each table's grain and expected key cardinality, then join incrementally and check row counts and key uniqueness after each step. Aggregate measures only after fan-out risk is controlled.
Common mistake: Writing all joins at once and debugging only the final inflated metric.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
27. How do you join to the latest dimension record?
Roles: Data Engineer, Analytics Engineer · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Rank dimension rows within each business key by effective or ingestion timestamp and keep one deterministic latest row before joining. For historical reporting, use an as-of join instead so facts see the dimension version valid at event time.
Common mistake: Using today's dimension value for historical facts without deciding whether that is intended.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
28. When is a composite join key necessary?
Roles: All data roles · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Use every column required to identify the relationship at the intended grain—for example account_id plus effective_date or tenant_id plus order_id. Test uniqueness on the composite key rather than assuming one convenient column is globally unique.
Common mistake: Joining multi-tenant data on an ID that is unique only within each tenant.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
29. What happens when join keys contain NULL?
Roles: All data roles · Difficulty: Intermediate · Dialect: Dialect-specific
Answer: Standard equality does not match NULL to NULL. Decide whether missing keys should remain unmatched, be compared with a null-safe operator, or be repaired upstream; coalescing to a sentinel can create false matches.
Common mistake: Coalescing all missing keys to the same value and manufacturing relationships.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
30. What are semi joins and anti joins?
Roles: All data roles · Difficulty: Intermediate · Dialect: Portable SQL
Answer: A semi join returns left rows that have at least one match; an anti join returns left rows with none. SQL commonly expresses them with EXISTS and NOT EXISTS without selecting right-side columns.
Common mistake: Using a normal join and DISTINCT when only existence is needed.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
31. When should EXISTS be preferred to JOIN?
Roles: All data roles · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Use EXISTS when the question asks whether a related row exists and no right-side columns are needed. A JOIN expresses row combination and can duplicate the left side when multiple matches exist.
Common mistake: Using JOIN for existence and changing the left table's grain.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
32. How do you perform a time-range or as-of join?
Roles: Data Engineer, Data Scientist · Difficulty: Advanced · Dialect: Portable SQL
Answer: Join on the business key plus a bounded time condition, such as fact_time between effective_start and effective_end. Ensure intervals do not overlap, define endpoint inclusivity, and use a deterministic rule if several records qualify.
Common mistake: Using only the entity key and leaking future dimension or feature values.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
Subqueries, CTEs, and set operations
33. When should you use a CTE instead of a subquery?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: Use the form that makes grain and transformations easiest to verify. CTEs are valuable for named steps and reuse within a statement, but they do not automatically improve performance and can be materialized differently by each optimizer.
Common mistake: Assuming every CTE is cached or faster.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
34. What is a recursive CTE?
Roles: Data Engineer · Difficulty: Advanced · Dialect: Dialect-specific
Answer: A recursive CTE has an anchor query and a recursive member that repeatedly references prior results. It is useful for hierarchies and graph-like traversal; include termination logic and consider cycle handling.
Common mistake: Writing recursion without a termination or cycle strategy.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
35. What is a correlated subquery?
Roles: All data roles · Difficulty: Intermediate · Dialect: Portable SQL
Answer: A correlated subquery references columns from the outer row and is logically evaluated in that context. Optimizers may decorrelate it, but for large workloads compare it with joins or window functions and inspect the plan.
Common mistake: Assuming it literally executes once per row in every engine or is always slow.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
36. How do IN and EXISTS differ?
Roles: All data roles · Difficulty: Intermediate · Dialect: Portable SQL
Answer: IN compares a value with a set; EXISTS tests whether any qualifying row exists. Positive forms are often equivalent after optimization, while NOT IN can produce no matches when its set includes NULL, making NOT EXISTS safer for anti joins.
Common mistake: Ignoring three-valued logic in NOT IN.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
37. What requirements must UNION inputs satisfy?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: Each branch must return the same number of columns in corresponding positions with compatible types. Column names come from the first branch in many systems, so alias deliberately and align meaning—not merely types.
Common mistake: Combining columns that are type-compatible but semantically unrelated.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
38. What do INTERSECT and EXCEPT do?
Roles: All data roles · Difficulty: Intermediate · Dialect: Dialect-specific
Answer: INTERSECT returns rows present in both sets; EXCEPT returns rows from the first set absent from the second. Duplicate handling and availability vary by dialect, so specify whether set or multiset behavior is required.
Common mistake: Forgetting that default set operations commonly deduplicate.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
39. How do you delete duplicate rows while keeping one survivor?
Roles: Data Engineer · Difficulty: Intermediate · Dialect: Dialect-specific
Answer: Rank rows within the business key using a deterministic preference such as latest ingestion timestamp plus a tie-breaker, inspect the ranked result, then delete rows above rank 1 using the engine's supported syntax. Back up or transact the change first.
Common mistake: Deleting without a deterministic tie-breaker or preview.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
40. How can CTEs make multi-step metrics safer?
Roles: Data Analyst, Analytics Engineer · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Use one named CTE per grain-changing step: clean inputs, aggregate each source, then join compatible results. Naming the grain in comments or CTE names makes fan-out and denominator mistakes easier to detect.
Common mistake: Hiding several grain changes inside one dense SELECT.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
41. Can a CTE hurt performance?
Roles: Data Engineer · Difficulty: Advanced · Dialect: Dialect-specific
Answer: Yes. Depending on the database and version, a CTE may be inlined, materialized, or optimized with hints. Inspect the execution plan and avoid assuming the behavior of PostgreSQL, MySQL, SQL Server, and cloud warehouses is identical.
Common mistake: Treating CTE performance folklore as portable fact.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
42. How do MERGE and UPSERT differ?
Roles: Data Engineer, Analytics Engineer · Difficulty: Advanced · Dialect: Dialect-specific
Answer: Both reconcile incoming rows with existing rows, but syntax, match rules, concurrency behavior, and supported actions vary widely. Define the business key, duplicate-source behavior, and idempotency before choosing a dialect-specific statement.
Common mistake: Allowing multiple source rows to match one target row unpredictably.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
Window functions
43. How do window functions differ from GROUP BY?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: GROUP BY collapses rows to one row per group; window functions calculate across related rows while preserving the input row grain. This makes windows useful for ranks, comparisons, and running metrics.
Common mistake: Using a window when the requested output should contain one row per group.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
44. How do ROW_NUMBER, RANK, and DENSE_RANK differ?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: ROW_NUMBER assigns a unique sequence, RANK gives ties the same rank and leaves gaps, and DENSE_RANK gives ties the same rank without gaps. Choose based on the requested tie behavior and add deterministic ordering for ROW_NUMBER.
Common mistake: Using ROW_NUMBER without a tie-breaker and getting unstable survivors.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
45. How do you return the top N rows within each group?
Roles: All data roles · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Rank rows with a window partitioned by the group and ordered by the metric, then filter the rank in an outer query or QUALIFY where supported. State whether ties may return more than N rows.
Common mistake: Applying a global LIMIT instead of ranking within each group.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
46. How do you calculate a running total?
Roles: All data roles · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Use SUM(value) OVER with an ordering column and an explicit ROWS frame. Ensure the ordering is deterministic and decide how tied timestamps should behave.
Common mistake: Relying on the default RANGE frame and accidentally grouping peers.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
47. How do you calculate a rolling seven-day average?
Roles: Data Analyst, Data Scientist · Difficulty: Intermediate · Dialect: Portable SQL
Answer: First aggregate to one row per calendar day, join a calendar spine if missing dates must count, then use a seven-row frame or an engine-appropriate time-range frame. Clarify whether the current day is included.
Common mistake: Using seven rows when the data can have missing dates or several rows per day.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
48. What do LAG and LEAD do?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: LAG reads a prior row and LEAD reads a following row within the defined partition and order. They are useful for change, interval, and sequence calculations; use defaults carefully and make ordering deterministic.
Common mistake: Forgetting PARTITION BY and comparing across entities.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
49. Why can LAST_VALUE return a surprising result?
Roles: All data roles · Difficulty: Advanced · Dialect: Portable SQL
Answer: LAST_VALUE uses the current window frame, whose default may end at the current row's peer group rather than the partition end. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when you need the partition's final value.
Common mistake: Assuming the default frame covers the entire partition.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
50. How do you calculate each row's share of its group?
Roles: Data Analyst, Data Scientist · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Divide the row value by SUM(value) OVER (PARTITION BY group_key), using decimal arithmetic and a zero-denominator guard. Confirm the input already has the intended row grain.
Common mistake: Calculating share on duplicated rows created by an earlier join.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
51. How do you calculate a median with SQL?
Roles: Data Analyst, Data Scientist · Difficulty: Advanced · Dialect: Dialect-specific
Answer: Use a dialect-supported percentile function such as PERCENTILE_CONT where available, or derive the middle ordered value or values using row numbers and counts. State whether interpolation is acceptable and how NULLs are handled.
Common mistake: Using an approximate percentile without disclosing it.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
52. How do you calculate a cumulative distinct count?
Roles: Data Analyst, Data Scientist · Difficulty: Advanced · Dialect: Dialect-specific
Answer: A portable pattern marks the first occurrence of each entity with ROW_NUMBER, then cumulatively sums those first-occurrence flags. Some engines support distinct window aggregates, but availability and cost vary.
Common mistake: Using COUNT(DISTINCT entity) at each date without controlling the time grain or cost.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
53. How do you sessionize event data?
Roles: Data Analyst, Data Scientist · Difficulty: Advanced · Dialect: Portable SQL
Answer: Order events within each user, use LAG to compare timestamps, flag a new session when the inactivity gap exceeds the threshold, then cumulatively sum the flags. Define whether an exact-threshold gap starts a new session.
Common mistake: Computing gaps without partitioning by user.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
54. How do you solve gaps-and-islands problems?
Roles: Data Analyst, Data Engineer · Difficulty: Advanced · Dialect: Portable SQL
Answer: Create a grouping key that stays constant across consecutive values, often by subtracting ROW_NUMBER from a date or by cumulatively summing break flags. Then group by entity and that derived island key.
Common mistake: Assuming dates are consecutive without checking duplicates and missing days.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
55. What is the difference between ROWS and RANGE frames?
Roles: All data roles · Difficulty: Advanced · Dialect: Dialect-specific
Answer: ROWS counts physical rows relative to the current row; RANGE groups peers or uses value-based boundaries according to dialect semantics. With tied ordering values, RANGE can include more rows than expected.
Common mistake: Omitting an explicit frame in a running or rolling calculation.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
56. What does QUALIFY do?
Roles: All data roles · Difficulty: Intermediate · Dialect: GoogleSQL and selected warehouses
Answer: QUALIFY filters after window functions, allowing conditions on ranks without an outer subquery. It is supported by GoogleSQL and some warehouses but is not portable to every database.
Common mistake: Presenting QUALIFY as universal SQL.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
57. How do you make a window ordering deterministic?
Roles: All data roles · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Add a stable tie-breaker after the primary sort key, such as event_id after event_timestamp. Without it, functions such as ROW_NUMBER, LAG, and running totals may produce different valid results across runs.
Common mistake: Assuming timestamps or scores are unique.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
Dates, text, and semi-structured data
58. How do you group timestamps by day, week, or month?
Roles: All data roles · Difficulty: Beginner · Dialect: Dialect-specific
Answer: Use the dialect's date truncation function or construct a canonical period start. Define the timezone before truncation and prefer half-open intervals for filtering.
Common mistake: Truncating UTC timestamps when the business day is defined in local time.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
59. How should you compare timestamps across time zones?
Roles: Data Analyst, Data Engineer · Difficulty: Advanced · Dialect: Dialect-specific
Answer: Store or interpret instants consistently, convert to the required business timezone, and only then derive local dates or hours. Named zones handle daylight-saving transitions more safely than fixed offsets.
Common mistake: Treating PST or EST as a constant offset year-round.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
60. How do you filter the last 30 complete days?
Roles: All data roles · Difficulty: Beginner · Dialect: Dialect-specific
Answer: Define whether today is included and use a half-open interval such as timestamp >= start and timestamp < end. Avoid wrapping indexed timestamp columns in functions when a boundary comparison works.
Common mistake: Mixing rolling 30 times 24 hours with 30 calendar days.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
61. Why use a calendar spine?
Roles: Data Analyst, Analytics Engineer · Difficulty: Intermediate · Dialect: Portable SQL
Answer: A calendar spine supplies rows for dates with no events, enabling correct zero-filled trends, rolling windows, and period comparisons. Cross join it only to the required entities and bounded date range.
Common mistake: Interpreting a missing row as a measured zero without generating the date.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
62. How do you concatenate strings portably?
Roles: All data roles · Difficulty: Beginner · Dialect: Dialect-specific
Answer: Concatenation syntax and NULL behavior vary: engines may use CONCAT, the || operator, or +. State the dialect and decide whether any NULL should nullify the result or be replaced intentionally.
Common mistake: Assuming one concatenation operator works everywhere.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
63. When should you use regular expressions in SQL?
Roles: Data Analyst, Data Engineer · Difficulty: Intermediate · Dialect: Dialect-specific
Answer: Regex is useful for validation or extraction when simpler predicates are insufficient. Syntax and performance are engine-specific; do not use regex to compensate for a missing normalized field when the transformation belongs upstream.
Common mistake: Writing an unanchored pattern that matches more values than intended.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
64. How do you query JSON fields?
Roles: Data Engineer, Data Analyst · Difficulty: Intermediate · Dialect: Dialect-specific
Answer: Use the engine's JSON path or extraction functions, cast values deliberately, and distinguish a missing path from a JSON null and a SQL NULL. Promote heavily used attributes into typed columns when governance or performance requires it.
Common mistake: Comparing an extracted JSON string with a numeric value without casting.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
65. How do you query arrays or repeated records in GoogleSQL?
Roles: Data Analyst, Data Engineer · Difficulty: Intermediate · Dialect: GoogleSQL
Answer: Use UNNEST to turn array elements into rows, generally with an explicit alias; use LEFT JOIN UNNEST when parent rows with empty arrays must remain. Preserve or reconstruct element order with WITH OFFSET when needed.
Common mistake: Unnesting multiple arrays independently and creating an unintended Cartesian product.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
66. How should blanks, whitespace, and NULL be normalized?
Roles: All data roles · Difficulty: Beginner · Dialect: Portable SQL
Answer: Apply TRIM, convert intentional empty strings with NULLIF, and preserve a distinction when blank is a valid observed value. Define normalization once upstream when many analyses rely on it.
Common mistake: Treating every empty-looking value as semantically missing.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
67. How do you cast dirty values safely?
Roles: Data Engineer, Data Analyst · Difficulty: Intermediate · Dialect: Dialect-specific
Answer: Use validation plus CAST, or a dialect's safe or try-cast function when bad inputs should become NULL rather than abort the query. Count and investigate failed casts so data loss is visible.
Common mistake: Silently dropping rows whose values fail conversion.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
Product and business analytics
68. How do you calculate daily active users from an event table?
Roles: Data Analyst, Data Scientist · Difficulty: Beginner · Dialect: Portable SQL
Answer: Define an active event, convert timestamps to the product timezone, deduplicate user-day pairs if necessary, then count distinct user IDs by date. Validate bots, anonymous IDs, and identity merges before treating the metric as canonical.
Common mistake: Counting events instead of unique qualifying users.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
69. How do you calculate day-1 and day-7 retention?
Roles: Data Analyst, Data Scientist · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Assign each user a cohort start date, join later qualifying activity at exact day offsets or defined windows, and divide retained users by eligible cohort users. State timezone, eligibility, and whether return activity must occur exactly on the day.
Common mistake: Dividing by active users on the return day rather than the original cohort.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
70. How do you build a multi-step funnel?
Roles: Data Analyst, Data Scientist · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Establish one row per entity, derive the first valid timestamp for each step, enforce required ordering, then condition each conversion denominator on reaching the prior step. Define whether repeated or out-of-order events count.
Common mistake: Counting independent step events without enforcing sequence.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
71. How should a conversion rate be defined?
Roles: Data Analyst, Data Scientist · Difficulty: Beginner · Dialect: Portable SQL
Answer: Name the unit, qualifying numerator event, eligible denominator, attribution window, and exclusion rules. Build both numerator and denominator at one row per unit before aggregating.
Common mistake: Mixing event-level numerators with user-level denominators.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
72. How do you create a cohort-retention matrix?
Roles: Data Analyst, Data Scientist · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Assign each entity a cohort period, calculate the period offset of later activity, count distinct retained entities by cohort and offset, then divide by cohort size. Generate missing offsets if the visualization requires explicit zeros.
Common mistake: Allowing users to enter multiple acquisition cohorts.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
73. How do you find each customer's first purchase and its attributes?
Roles: Data Analyst, Data Scientist · Difficulty: Beginner · Dialect: Portable SQL
Answer: Rank purchases within customer by timestamp plus a stable tie-breaker and keep row 1, or find the minimum key then join back safely. Clarify whether simultaneous first orders should return one or all rows.
Common mistake: Selecting MIN(date) alongside unrelated non-aggregated attributes.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
74. How do you calculate repeat-purchase rate?
Roles: Data Analyst, Data Scientist · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Define the observation window, count qualifying orders per customer, and divide customers with at least two purchases by eligible purchasing customers. Treat customers acquired near the period end carefully because they have less opportunity to repeat.
Common mistake: Comparing cohorts with unequal follow-up time.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
75. How do you calculate month-over-month growth including empty months?
Roles: Data Analyst, Data Scientist · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Aggregate the metric by month, left join to a calendar spine, use LAG on the complete series, then calculate change with a zero-denominator policy. Distinguish missing measurement from a measured zero.
Common mistake: Skipping empty months and comparing with the wrong prior period.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
76. How do you construct one row per randomized experiment unit?
Roles: Data Scientist, Data Analyst · Difficulty: Advanced · Dialect: Portable SQL
Answer: Start from the assignment table at the randomization grain, deduplicate assignment according to the experiment contract, then aggregate outcomes within the defined window before joining. Never let repeated events multiply assignment rows.
Common mistake: Analyzing event rows as independent observations when users were randomized.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
77. How do you compare pre/post behavior for treated and untreated groups?
Roles: Data Scientist · Difficulty: Advanced · Dialect: Portable SQL
Answer: Build one row per unit-period, calculate the change from pre to post for each group, then compare those changes. Check parallel-trend plausibility and avoid describing a raw pre/post difference as causal.
Common mistake: Ignoring baseline differences or time trends shared by both groups.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
78. How do you calculate experiment conversion rates correctly?
Roles: Data Scientist, Data Analyst · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Join assignment to the first qualifying conversion within the analysis window, create a binary outcome per randomized unit, and aggregate by assigned variant. Keep assignment—not exposure or event count—as the denominator unless the estimand says otherwise.
Common mistake: Dropping assigned users with no outcome and inflating conversion.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
79. How do you build point-in-time-correct features?
Roles: Data Scientist, Data Engineer · Difficulty: Advanced · Dialect: Portable SQL
Answer: For every prediction timestamp, use only records whose event or availability time was known by that moment. Use bounded as-of joins and reproduce historical source versions so backfills cannot leak future information.
Common mistake: Joining the latest customer state to historical labels.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
80. How do you investigate an apparent metric anomaly with SQL?
Roles: Data Analyst, Data Scientist · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Validate data freshness and definitions first, decompose the metric into numerator and denominator, segment by likely drivers, and compare with independent sources or invariant checks. Separate an instrumentation break from a real behavior change before explaining causes.
Common mistake: Jumping to a product narrative before checking the pipeline.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
Data modeling and warehousing
81. What is database normalization?
Roles: Data Engineer, Analytics Engineer · Difficulty: Beginner · Dialect: Portable SQL
Answer: Normalization decomposes data to reduce redundancy and update anomalies using well-defined dependencies. It improves integrity for transactional systems, but analytical workloads often add controlled denormalization for simpler and faster reads.
Common mistake: Treating higher normal form as automatically better for every workload.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
82. When should a model be denormalized?
Roles: Data Engineer, Analytics Engineer · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Denormalize when read patterns, latency, or usability justify storing related or derived data together and the maintenance contract is explicit. Measure the workload and preserve a governed source of truth.
Common mistake: Duplicating fields without defining how updates remain consistent.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
83. How do star and snowflake schemas differ?
Roles: Data Engineer, Analytics Engineer · Difficulty: Intermediate · Dialect: Portable SQL
Answer: A star schema connects facts directly to relatively denormalized dimensions; a snowflake normalizes dimensions into additional tables. Stars often simplify analytics, while snowflakes can reduce redundancy but add joins and semantic complexity.
Common mistake: Choosing by diagram preference instead of query and governance needs.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
84. Why must a fact table declare its grain?
Roles: Data Engineer, Analytics Engineer · Difficulty: Intermediate · Dialect: Portable SQL
Answer: The grain states exactly what one fact row represents and determines valid dimensions and additive measures. Declare it before selecting columns so facts with different levels of detail do not create double counting.
Common mistake: Mixing order and order-line facts in the same additive table.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
85. What is a surrogate key?
Roles: Data Engineer, Analytics Engineer · Difficulty: Intermediate · Dialect: Portable SQL
Answer: A surrogate key is a warehouse-generated identifier independent of the source business key. It is useful for integrating sources and tracking dimension versions, but the business key still needs documented uniqueness and matching rules.
Common mistake: Assuming a surrogate key solves entity resolution.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
86. How does a type-2 slowly changing dimension work?
Roles: Data Engineer, Analytics Engineer · Difficulty: Advanced · Dialect: Portable SQL
Answer: Each version has an effective interval and usually a surrogate key; a change closes the prior version and inserts a new one. Facts join to the version effective at the fact time to preserve historical truth.
Common mistake: Creating overlapping effective intervals or joining facts to the current row.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
87. How do transaction, periodic snapshot, and accumulating snapshot facts differ?
Roles: Data Engineer, Analytics Engineer · Difficulty: Advanced · Dialect: Portable SQL
Answer: Transaction facts record events, periodic snapshots record state at regular intervals, and accumulating snapshots update milestones for a process lifecycle. Choose the pattern that matches the business question and update behavior.
Common mistake: Summing snapshot balances across dates as though they were transactions.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
88. What makes an incremental SQL transformation idempotent?
Roles: Data Engineer, Analytics Engineer · Difficulty: Advanced · Dialect: Portable SQL
Answer: Rerunning the same inputs produces the same target state. Use stable business keys, deterministic deduplication, explicit affected windows, and merge or partition-replacement semantics that do not append duplicates.
Common mistake: Using ingestion time alone and duplicating data on retries.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
89. How should late-arriving facts be handled?
Roles: Data Engineer, Analytics Engineer · Difficulty: Advanced · Dialect: Portable SQL
Answer: Track event time and arrival time separately, define an accepted lateness window, and recompute affected partitions or aggregates through the same deterministic logic. Monitor corrections beyond the window instead of silently ignoring them.
Common mistake: Updating only today's partition when old events can still arrive.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
90. What SQL data-quality tests belong in a pipeline?
Roles: Data Engineer, Analytics Engineer · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Test business-key uniqueness, non-null requirements, accepted values, referential integrity, freshness, volume, and reconciliation invariants. Set thresholds and ownership so a failed test has a defined response.
Common mistake: Running tests without deciding whether they warn, quarantine, or block publication.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
Query plans and performance
91. How do indexes improve query performance?
Roles: Data Engineer · Difficulty: Intermediate · Dialect: Database-specific
Answer: Indexes provide alternative access paths that can reduce scanned rows or support ordering, at the cost of storage and write maintenance. Value depends on selectivity, query shape, data distribution, and the engine.
Common mistake: Recommending an index without examining the workload or plan.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
92. How do composite and covering indexes differ?
Roles: Data Engineer · Difficulty: Advanced · Dialect: Database-specific
Answer: A composite index keys on several columns and is useful when predicates match its supported leading order. A covering index contains every column needed by a query, potentially avoiding base-table lookups; exact behavior is engine-specific.
Common mistake: Ignoring column order in a composite index.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
93. How do you read an execution plan?
Roles: Data Engineer · Difficulty: Advanced · Dialect: Database-specific
Answer: Start with estimated and actual row counts, access paths, join algorithms and order, filters, sorts, spills, and the most expensive or misestimated operators. Compare the plan with table statistics and the requested result grain before changing indexes.
Common mistake: Optimizing only the visually highest cost percentage without validating runtime evidence.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
94. What is partition pruning?
Roles: Data Engineer, Analytics Engineer · Difficulty: Intermediate · Dialect: Database-specific
Answer: Partition pruning lets the engine skip partitions that cannot satisfy a predicate. Filter directly on the partition key with compatible boundaries and verify the plan or bytes scanned; wrapping the key in a function can prevent pruning.
Common mistake: Partitioning by a column that common queries do not filter.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
95. How do you optimize a join between large tables?
Roles: Data Engineer · Difficulty: Advanced · Dialect: Portable SQL
Answer: Reduce each input to needed columns and rows, ensure compatible keys and statistics, control many-to-many fan-out, and inspect the plan for join strategy, shuffles, spills, or skew. Pre-aggregation can help when it preserves the requested semantics.
Common mistake: Adding DISTINCT after the join instead of controlling cardinality.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
96. What makes a predicate non-sargable?
Roles: Data Engineer · Difficulty: Advanced · Dialect: Database-specific
Answer: A predicate is non-sargable when the engine cannot efficiently use an index or pruning strategy, often because the filtered column is wrapped in a function or implicitly converted. Rewrite it as a compatible range when semantics permit.
Common mistake: Changing a predicate for speed without preserving timezone or boundary semantics.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
97. What is parameter sniffing in SQL Server?
Roles: Data Engineer · Difficulty: Advanced · Dialect: SQL Server
Answer: SQL Server may compile and cache a plan using the parameter values seen at compilation; a plan good for one distribution can be poor for another. Diagnose with actual plans and runtime data before considering recompilation, hints, or query redesign.
Common mistake: Calling every inconsistent runtime issue parameter sniffing.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
Transactions and reliability
98. What do the ACID properties mean?
Roles: Data Engineer · Difficulty: Intermediate · Dialect: Portable SQL
Answer: Atomicity makes a transaction all-or-nothing; consistency preserves declared invariants; isolation controls interaction among concurrent transactions; durability preserves committed results after failure. Guarantees depend on the database configuration and operation.
Common mistake: Using consistency to mean replica consistency without clarifying context.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
99. What anomalies do isolation levels address?
Roles: Data Engineer · Difficulty: Advanced · Dialect: Database-specific
Answer: Concurrency anomalies include dirty reads, non-repeatable reads, phantoms, lost updates, and write skew. Isolation-level names do not imply identical implementation across engines, so reason about the anomaly the application must prevent.
Common mistake: Assuming repeatable read or serializable behaves identically everywhere.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
100. What causes deadlocks, and how should applications handle them?
Roles: Data Engineer · Difficulty: Advanced · Dialect: Database-specific
Answer: A deadlock occurs when transactions wait cyclically for resources held by one another. Reduce risk with consistent access order, short transactions, appropriate indexes, and small lock footprints; still detect the database error and retry the aborted transaction safely.
Common mistake: Trying to eliminate every deadlock instead of implementing idempotent retry handling.
What the interviewer is assessing: Correct reasoning, explicit assumptions, and validation of the result.
How to prepare for a SQL interview
Seven-day plan
| Day | Focus |
|---|---|
| 1 | Filtering, NULLs, CASE, and aggregation |
| 2 | Joins, grain, duplicates, and reconciliation |
| 3 | CTEs, subqueries, and set operations |
| 4 | Ranking, offset, and aggregate window functions |
| 5 | Dates, cohorts, funnels, retention, and sessionization |
| 6 | Role-specific modeling or performance topics |
| 7 | Timed mixed set, verbal explanation, and error review |
What makes a strong SQL interview answer?
A strong answer defines one output row, names the keys and assumptions, writes readable SQL, handles NULLs and ties, and proposes a validation check. Explain the straightforward correct solution before discussing optimization, and distinguish portable SQL from engine-specific behavior.
Track mistakes by category: misunderstood question, wrong grain, join inflation, NULL behavior, dialect syntax, edge case, or performance assumption. Review that log instead of only collecting new questions.
Practice in the SQL Pad · Continue with the SQL course · View live class schedule
