Top 100 SQL Interview Questions

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.

Fourteen query examples below include sample tables, runnable PostgreSQL, and expected results. Start with second-highest salary, customers with no orders, top N per group, or retention. For guided exercises, practice in SQL Studio.

Which SQL topics should each data role prepare?

100 SQL questions by topic

1. SQL fundamentals and NULL behavior (10)
2. Aggregation and conditional logic (10)
3. Joins and relationship grain (12)
4. Subqueries, CTEs, and set operations (10)
5. Window functions (15)
6. Dates, text, and semi-structured data (10)
7. Product and business analytics (13)
8. Data modeling and warehousing (10)
9. Query plans and performance (7)
10. 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.

2. What is the difference between WHERE and HAVING?

Roles: All data roles · Difficulty: Beginner · Dialect: PostgreSQL

Answer: WHERE filters individual rows; HAVING filters groups after aggregation. Here, exclude cancelled orders first, then keep customers whose paid orders total at least 100.

Input: orders

The query includes its sample data, so you can run it as written in PostgreSQL.

WITH orders (customer_id, status, amount) AS (
  VALUES
    (1::integer, 'paid'::text, 60::numeric),
    (1::integer, 'paid'::text, 50::numeric),
    (1::integer, 'cancelled'::text, 90::numeric),
    (2::integer, 'paid'::text, 80::numeric)
)
SELECT customer_id, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) >= 100
ORDER BY customer_id;

Expected result

Watch for: Filtering amount >= 100 in WHERE would remove both paid orders for customer 1. The threshold applies to the customer total, not to each purchase.

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 !=.

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.

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.

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.

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.

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.

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.

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.

Aggregation and conditional logic

11. What is the difference between COUNT(*) and COUNT(column)?

Roles: All data roles · Difficulty: Beginner · Dialect: PostgreSQL

Answer: COUNT(*) counts rows. COUNT(column) counts non-NULL values, and COUNT(DISTINCT column) counts distinct non-NULL values. A repeated value still contributes to COUNT(column).

Input: measurements

The query includes its sample data, so you can run it as written in PostgreSQL.

WITH measurements (value) AS (
  VALUES
    (10::integer),
    (10::integer),
    (NULL::integer)
)
SELECT
  COUNT(*) AS row_count,
  COUNT(value) AS recorded_count,
  COUNT(DISTINCT value) AS distinct_count
FROM measurements;

Expected result

Watch for: Zero is a recorded value, not a missing value. An empty input returns 0 for all three counts.

12. What is conditional aggregation?

Roles: All data roles · Difficulty: Beginner · Dialect: PostgreSQL

Answer: Put a CASE expression inside an aggregate to calculate several metrics at the same grain. This query returns one row per customer, with a paid-order count and paid revenue.

Input: orders

The query includes its sample data, so you can run it as written in PostgreSQL.

WITH orders (customer_id, status, amount) AS (
  VALUES
    (1::integer, 'paid'::text, 40::numeric),
    (1::integer, 'paid'::text, 60::numeric),
    (1::integer, 'cancelled'::text, 70::numeric),
    (2::integer, 'cancelled'::text, 30::numeric)
)
SELECT
  customer_id,
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
  SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_revenue
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Expected result

Watch for: COUNT(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) is wrong here: COUNT also counts the non-NULL zeroes. This example assumes every paid order has a recorded amount.

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.

14. How do you find the second-highest distinct salary?

Roles: All data roles · Difficulty: Beginner · Dialect: PostgreSQL

Answer: To return the second-highest distinct salary as a single value, find the maximum salary below the overall maximum. Repeated top salaries should not occupy both first and second place.

Input: employees

The query includes its sample data, so you can run it as written in PostgreSQL.

WITH employees (employee_id, salary) AS (
  VALUES
    (1::integer, 120::integer),
    (2::integer, 120::integer),
    (3::integer, 100::integer),
    (4::integer, 90::integer),
    (5::integer, NULL::integer)
)
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Expected result

Watch for: This returns one NULL when fewer than two distinct non-NULL salaries exist. If the interviewer asks for every employee earning the second-highest salary, rank salaries with DENSE_RANK and return the employee rows instead.

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.

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.

17. How do you identify duplicate business keys?

Roles: All data roles · Difficulty: Beginner · Dialect: PostgreSQL

Answer: Group by the business key and keep groups with more than one row. Decide whether missing keys should be reported separately; this example excludes them from duplicate-key counts.

Input: customers

The query includes its sample data, so you can run it as written in PostgreSQL.

WITH customers (customer_id, email) AS (
  VALUES
    (1::integer, 'ari@example.com'::text),
    (2::integer, 'ari@example.com'::text),
    (3::integer, 'bo@example.com'::text),
    (4::integer, NULL::text),
    (5::integer, NULL::text)
)
SELECT email, COUNT(*) AS copies
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;

Expected result

Watch for: This compares stored email strings exactly. Normalize whitespace and case only if the business defines those variants as the same key; do not silently collapse distinct identifiers.

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.

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.

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.

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.

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.

23. Why can a many-to-many join inflate revenue?

Roles: All data roles · Difficulty: Intermediate · Dialect: PostgreSQL

Answer: Joining two child tables through an order can multiply rows. Order 1 has two items and two payments, so a direct join creates four rows. Aggregate each child table to one row per order before joining.

Input: order_items

Input: payments

The query includes its sample data, so you can run it as written in PostgreSQL.

WITH order_items (order_id, item_id, amount) AS (
  VALUES
    (1::integer, 11::integer, 30::numeric),
    (1::integer, 12::integer, 20::numeric)
),
payments (order_id, payment_id, amount) AS (
  VALUES
    (1::integer, 21::integer, 25::numeric),
    (1::integer, 22::integer, 25::numeric)
),
item_totals AS (
  SELECT order_id, SUM(amount) AS item_total
  FROM order_items
  GROUP BY order_id
),
payment_totals AS (
  SELECT order_id, SUM(amount) AS paid_total
  FROM payments
  GROUP BY order_id
)
SELECT i.order_id, i.item_total, p.paid_total
FROM item_totals AS i
LEFT JOIN payment_totals AS p
  ON p.order_id = i.order_id
ORDER BY i.order_id;

Expected result

Watch for: A direct join would report 100 for each total. SUM(DISTINCT amount) is not a repair: it would collapse the two legitimate payments of 25 into one. An order without a payment has NULL paid_total in this query.

24. How do you find rows in one table with no match in another?

Roles: All data roles · Difficulty: Beginner · Dialect: PostgreSQL

Answer: Use NOT EXISTS to keep customers for whom no matching order exists. It checks whether a match is present, so duplicate orders do not duplicate the customer output.

Input: customers

Input: orders

The query includes its sample data, so you can run it as written in PostgreSQL.

WITH customers (customer_id) AS (
  VALUES
    (1::integer),
    (2::integer),
    (3::integer)
),
orders (order_id, customer_id) AS (
  VALUES
    (101::integer, 1::integer),
    (102::integer, 1::integer),
    (103::integer, NULL::integer)
)
SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

Expected result

Watch for: NOT IN (SELECT customer_id FROM orders) would return no customers for this input because the subquery contains NULL. The customer key in the outer table is assumed non-NULL.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

44. How do ROW_NUMBER, RANK, and DENSE_RANK differ?

Roles: All data roles · Difficulty: Beginner · Dialect: PostgreSQL

Answer: ROW_NUMBER assigns a different number to every row. RANK gives ties the same rank and leaves gaps; DENSE_RANK gives ties the same rank without gaps. Use a unique tie-breaker for ROW_NUMBER, but leave it out of the other two windows when equal scores should tie.

Input: scores

The query includes its sample data, so you can run it as written in PostgreSQL.

WITH scores (player_id, score) AS (
  VALUES
    (1::integer, 100::integer),
    (2::integer, 100::integer),
    (3::integer, 90::integer)
)
SELECT
  player_id,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC, player_id) AS row_num,
  RANK() OVER (ORDER BY score DESC) AS score_rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_score_rank
FROM scores
ORDER BY score DESC, player_id;

Expected result

Watch for: Adding player_id to the RANK ordering would break the score ties. The outer ORDER BY controls display order; window ordering alone does not guarantee it.

45. How do you return the top N rows within each group?

Roles: All data roles · Difficulty: Intermediate · Dialect: PostgreSQL

Answer: For exactly two products per category, number products within each category and filter in an outer query. Use product_id to choose consistently between equal revenue values.

Input: products

The query includes its sample data, so you can run it as written in PostgreSQL.

WITH products (category, product_id, revenue) AS (
  VALUES
    ('books'::text, 1::integer, 100::numeric),
    ('books'::text, 2::integer, 80::numeric),
    ('books'::text, 3::integer, 80::numeric),
    ('games'::text, 4::integer, 60::numeric),
    ('games'::text, 5::integer, 40::numeric)
),
ranked AS (
  SELECT category, product_id, revenue,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY revenue DESC, product_id
    ) AS row_num
  FROM products
)
SELECT category, product_id, revenue
FROM ranked
WHERE row_num <= 2
ORDER BY category, row_num;

Expected result

Watch for: For the top two distinct revenue levels including ties, use DENSE_RANK ordered only by revenue. That version would also include book 3. A category with fewer than two products returns all its products.

46. How do you calculate a running total?

Roles: All data roles · Difficulty: Intermediate · Dialect: PostgreSQL

Answer: Order the rows and sum from the first row through the current row. An explicit ROWS frame plus a unique order key gives a row-by-row total, even when multiple purchases happen on the same day.

Input: orders

The query includes its sample data, so you can run it as written in PostgreSQL.

WITH orders (order_id, order_day, amount) AS (
  VALUES
    (1::integer, '2026-01-01'::date, 40::numeric),
    (2::integer, '2026-01-01'::date, 20::numeric),
    (3::integer, '2026-01-02'::date, 30::numeric)
)
SELECT
  order_id,
  order_day,
  amount,
  SUM(amount) OVER (
    ORDER BY order_day, order_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders
ORDER BY order_day, order_id;

Expected result

Watch for: If the requested output is one row per day, aggregate by day before applying the window. Ordering only by the date leaves the order of same-day purchases unspecified.

47. How do you calculate a rolling seven-day average?

Roles: Data Analyst, Data Scientist · Difficulty: Intermediate · Dialect: PostgreSQL

Answer: A seven-row frame means seven calendar days only when every date is present exactly once. Create a date spine, fill confirmed no-sales days with zero, then compute the window. This example returns the full seven-day average ending January 7.

Input: sales

The query includes its sample data, so you can run it as written in PostgreSQL.

WITH sales (sale_day, amount) AS (
  VALUES
    ('2026-01-01'::date, 70::numeric),
    ('2026-01-03'::date, 140::numeric),
    ('2026-01-07'::date, 70::numeric)
),
calendar AS (
  SELECT day::date AS day
  FROM generate_series(
    DATE '2026-01-01', DATE '2026-01-07', INTERVAL '1 day'
  ) AS dates(day)
),
daily AS (
  SELECT c.day, COALESCE(SUM(s.amount), 0) AS revenue
  FROM calendar AS c
  LEFT JOIN sales AS s ON s.sale_day = c.day
  GROUP BY c.day
),
rolling AS (
  SELECT day,
    ROUND(AVG(revenue) OVER (
      ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) AS avg_7d
  FROM daily
)
SELECT day, avg_7d
FROM rolling
WHERE day = DATE '2026-01-07';

Expected result

Watch for: The total is 280 across seven days, not three sales rows. Filter the output date after the window calculation. In a longer report, include six days of lookback before its first date; do not convert missing ingestion into zero sales.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Product and business analytics

68. How do you calculate daily active users from an event table?

Roles: Data Analyst, Data Scientist · Difficulty: Beginner · Dialect: PostgreSQL

Answer: Count distinct identified users with a qualifying event on each reporting date. Here a search event defines activity, and event_day is already the date in the agreed product timezone.

Input: events

The query includes its sample data, so you can run it as written in PostgreSQL.

WITH events (event_day, user_id, event_type) AS (
  VALUES
    ('2026-01-01'::date, 1::integer, 'search'::text),
    ('2026-01-01'::date, 1::integer, 'search'::text),
    ('2026-01-01'::date, 2::integer, 'search'::text),
    ('2026-01-01'::date, 3::integer, 'login'::text),
    ('2026-01-02'::date, 1::integer, 'search'::text),
    ('2026-01-02'::date, NULL::integer, 'search'::text)
)
SELECT event_day, COUNT(DISTINCT user_id) AS active_users
FROM events
WHERE event_type = 'search'
GROUP BY event_day
ORDER BY event_day;

Expected result

Watch for: User 1 counts once per day regardless of event volume. COUNT(DISTINCT user_id) ignores NULL, so this definition excludes anonymous events. For timestamp data, derive the reporting date after timezone conversion.

69. How do you calculate day-1 and day-7 retention?

Roles: Data Analyst, Data Scientist · Difficulty: Intermediate · Dialect: PostgreSQL

Answer: For exact-day retention, start from the signup cohort and check whether each user returns exactly one or seven days later. Keep users with no activity in the denominator. Only include cohorts whose entire day-7 window has been observed.

Input: users

Input: activity

The query includes its sample data, so you can run it as written in PostgreSQL.

WITH users (user_id, signup_day) AS (
  VALUES
    (1::integer, '2026-01-01'::date),
    (2::integer, '2026-01-01'::date),
    (3::integer, '2026-01-01'::date)
),
activity (user_id, active_day) AS (
  VALUES
    (1::integer, '2026-01-02'::date),
    (1::integer, '2026-01-02'::date),
    (1::integer, '2026-01-08'::date),
    (2::integer, '2026-01-02'::date)
),
per_user AS (
  SELECT u.user_id, u.signup_day,
    MAX(CASE WHEN a.active_day = u.signup_day + 1 THEN 1 ELSE 0 END) AS d1,
    MAX(CASE WHEN a.active_day = u.signup_day + 7 THEN 1 ELSE 0 END) AS d7
  FROM users AS u
  LEFT JOIN activity AS a ON a.user_id = u.user_id
  WHERE u.signup_day + 7 <= DATE '2026-01-08'
  GROUP BY u.user_id, u.signup_day
)
SELECT
  signup_day,
  COUNT(*) AS cohort_users,
  ROUND(100.0 * SUM(d1) / COUNT(*), 2) AS day_1_pct,
  ROUND(100.0 * SUM(d7) / COUNT(*), 2) AS day_7_pct
FROM per_user
GROUP BY signup_day
ORDER BY signup_day;

Expected result

Watch for: Assume activity is complete through the end of January 8 and uses the signup timezone. MAX removes duplicate return events. This is exact-day retention, not a return at any time during the first seven days.

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.

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.

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.

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.

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.

75. How do you calculate month-over-month growth including empty months?

Roles: Data Analyst, Data Scientist · Difficulty: Intermediate · Dialect: PostgreSQL

Answer: Fill confirmed no-sales months before applying LAG. Otherwise March would compare with January instead of February. Return NULL when the previous month is absent or zero rather than reporting an undefined growth rate.

Input: sales

The query includes its sample data, so you can run it as written in PostgreSQL.

WITH sales (month, revenue) AS (
  VALUES
    ('2026-01-01'::date, 100::numeric),
    ('2026-03-01'::date, 150::numeric)
),
months AS (
  SELECT month::date AS month
  FROM generate_series(
    DATE '2026-01-01', DATE '2026-03-01', INTERVAL '1 month'
  ) AS dates(month)
),
monthly AS (
  SELECT m.month, COALESCE(SUM(s.revenue), 0) AS revenue
  FROM months AS m
  LEFT JOIN sales AS s ON s.month = m.month
  GROUP BY m.month
),
with_previous AS (
  SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS previous_revenue
  FROM monthly
)
SELECT month, revenue, previous_revenue,
  ROUND(100.0 * (revenue - previous_revenue)
    / NULLIF(previous_revenue, 0), 2) AS growth_pct
FROM with_previous
ORDER BY month;

Expected result

Watch for: February is a measured zero in this fixture, not an unknown value. The change from zero to 150 is meaningful in absolute terms, but percentage growth from zero is undefined.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

How to prepare for a SQL interview

Seven-day plan

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

SQL dialect references

• PostgreSQL SQL language
• PostgreSQL window functions
• MySQL 8.4 window functions
• MySQL 8.4 EXPLAIN
• GoogleSQL query syntax and QUALIFY
• BigQuery nested and repeated fields