SQL Deep Dive interview questions

25 questions from the SQL Deep Dive course, each with the correct answer and an explanation. Try to answer before you open the answer.

How SQL Thinks

1. What order do rows come back in without ORDER BY?

  1. Primary key order
  2. Insertion order
  3. No guaranteed order
  4. Alphabetical order
Show answer

Answer: No guaranteed order

Always add ORDER BY when order matters.

Lesson: Thinking in Sets

2. Where do you filter on an aggregate such as count(*) > 2?

  1. HAVING
  2. WHERE
  3. ON
  4. ORDER BY
Show answer

Answer: HAVING

HAVING runs after GROUP BY.

Lesson: Logical Order of Evaluation

3. What does NULL = NULL evaluate to?

  1. An error
  2. TRUE
  3. FALSE
  4. UNKNOWN (NULL)
Show answer

Answer: UNKNOWN (NULL)

Use IS NULL or IS NOT DISTINCT FROM.

Lesson: NULL and Three-Valued Logic

Joins Without Surprises

4. What happens when you filter a LEFT JOIN's right table in WHERE?

  1. Nothing changes
  2. Unmatched left rows are removed, like an inner join
  3. All rows are duplicated
  4. The query fails
Show answer

Answer: Unmatched left rows are removed, like an inner join

NULL fails the WHERE condition.

Lesson: Inner and Outer Joins

5. Why can NOT IN (subquery) return no rows at all?

  1. NOT IN is not supported
  2. If the subquery returns a NULL, every NOT IN comparison is unknown
  3. It only works on numbers
  4. It requires an index
Show answer

Answer: If the subquery returns a NULL, every NOT IN comparison is unknown

Use NOT EXISTS instead.

Lesson: Semi-Joins and Anti-Joins

6. How do you fix revenue doubled by joining order items?

  1. Switch to a LEFT JOIN
  2. Use DISTINCT on the revenue sum
  3. Add ORDER BY
  4. Aggregate at the order grain before joining, or avoid the unneeded join
Show answer

Answer: Aggregate at the order grain before joining, or avoid the unneeded join

Match the grain to the metric.

Lesson: Fan-Out and Double Counting

Aggregation and Reporting

7. What does count(*) FILTER (WHERE hired >= '2022-01-01') compute?

  1. The number of rows in each group hired since 2022
  2. All rows ignoring the filter
  3. The rows removed by WHERE
  4. A boolean
Show answer

Answer: The number of rows in each group hired since 2022

Conditional aggregation.

Lesson: GROUP BY, HAVING and FILTER

8. What does GROUPING(col) return for a subtotal row where col is rolled up?

  1. NULL
  2. 0
  3. 1
  4. The column value
Show answer

Answer: 1

It identifies aggregated levels.

Lesson: Subtotals With ROLLUP and GROUPING SETS

9. Why use >= start and < next_start for date ranges?

  1. BETWEEN is not supported
  2. They are faster to type
  3. Half-open ranges avoid gaps and double counting at boundaries
  4. They ignore time zones
Show answer

Answer: Half-open ranges avoid gaps and double counting at boundaries

Works for dates and timestamps.

Lesson: Pivoting With Conditional Aggregation

Window Functions

10. Which function gives tied rows the same number and then skips numbers?

  1. RANK
  2. ROW_NUMBER
  3. DENSE_RANK
  4. NTILE
Show answer

Answer: RANK

1, 2, 2, 4.

Lesson: Ranking and Top-N per Group

12. What does sum(salary) OVER (PARTITION BY dept_id) return on each row?

  1. A running total
  2. The total salary of that row's department
  3. The overall total
  4. The row's own salary
Show answer

Answer: The total salary of that row's department

No ORDER BY means the whole partition.

Lesson: Shares, Comparisons and Bands

CTEs, LATERAL and Set Operations

13. What are the two parts of a recursive CTE?

  1. A SELECT and an ORDER BY
  2. An anchor query and a recursive query joined to the previous results
  3. Two tables of equal size
  4. A trigger and a function
Show answer

Answer: An anchor query and a recursive query joined to the previous results

Combined with UNION ALL.

Lesson: CTEs and Recursive Queries

14. How do you keep outer rows with no LATERAL results?

  1. Use UNION
  2. CROSS JOIN LATERAL
  3. Add DISTINCT
  4. LEFT JOIN LATERAL (...) ON true
Show answer

Answer: LEFT JOIN LATERAL (...) ON true

Like any left join.

Lesson: LATERAL Joins

15. Which operation returns rows in the first query but not the second?

  1. EXCEPT
  2. INTERSECT
  3. UNION
  4. UNION ALL
Show answer

Answer: EXCEPT

Set difference.

Lesson: UNION, INTERSECT and EXCEPT

Analytical Patterns

16. How do you show days with no orders as zero?

  1. Use INNER JOIN
  2. GROUP BY ordered_on only
  3. LEFT JOIN orders to a generated list of days and COALESCE the sum to 0
  4. Add ORDER BY day
Show answer

Answer: LEFT JOIN orders to a generated list of days and COALESCE the sum to 0

Start from the complete calendar.

Lesson: Filling Gaps in Time Series

17. In gaps-and-islands, what stays constant within a run of consecutive days?

  1. The date
  2. The row number
  3. The date minus its row number
  4. The user count
Show answer

Answer: The date minus its row number

That difference is the island key.

Lesson: Gaps and Islands

18. What prevents duplicates from reappearing after cleanup?

  1. A unique constraint or unique index on the key
  2. An ORDER BY clause
  3. A view
  4. Running the cleanup weekly only
Show answer

Answer: A unique constraint or unique index on the key

Let the database enforce it.

Lesson: Finding and Removing Duplicates

Changing Data Safely

19. Which PostgreSQL feature is simplest for a concurrent single-row upsert?

  1. CREATE VIEW
  2. A SELECT then INSERT in application code
  3. TRUNCATE
  4. INSERT ... ON CONFLICT DO UPDATE
Show answer

Answer: INSERT ... ON CONFLICT DO UPDATE

Atomic with respect to concurrent inserts.

Lesson: MERGE and Upserts

20. What does RETURNING do on an UPDATE?

  1. Rolls back the update
  2. Returns the modified rows, like a SELECT
  3. Returns only a count
  4. Locks the whole table
Show answer

Answer: Returns the modified rows, like a SELECT

Great for audits and verification.

Lesson: UPDATE ... FROM and DELETE ... USING

21. What does ROLLBACK do after an UPDATE inside BEGIN?

  1. Commits the changes
  2. Undoes all changes made since BEGIN
  3. Deletes the table
  4. Repeats the update
Show answer

Answer: Undoes all changes made since BEGIN

Nothing is permanent until COMMIT.

Lesson: A Safe Workflow for Data Changes

Writing Fast Queries

22. Why can WHERE date(created_at) = '2026-03-01' miss an index on created_at?

  1. PostgreSQL ignores indexes on timestamps
  2. Dates cannot be indexed
  3. The index is corrupted
  4. The function changes the column, so the index order on raw values cannot be used directly
Show answer

Answer: The function changes the column, so the index order on raw values cannot be used directly

Use a range or an expression index.

Lesson: Index-Friendly (Sargable) Predicates

23. An index on (user_id, kind) is least useful for which filter?

  1. WHERE kind = 'buy'
  2. WHERE user_id = 42
  3. WHERE user_id = 42 AND kind = 'buy'
  4. WHERE user_id = 42 ORDER BY kind
Show answer

Answer: WHERE kind = 'buy'

Leftmost prefix rule.

Lesson: Composite Index Column Order

24. What does EXPLAIN ANALYZE add compared with EXPLAIN?

  1. It creates missing indexes
  2. It rewrites the query
  3. It executes the query and reports actual row counts
  4. It only formats the SQL
Show answer

Answer: It executes the query and reports actual row counts

Estimated versus actual.

Lesson: Reading EXPLAIN ANALYZE

25. Which belongs on an SQL review checklist?

  1. Put LEFT JOIN filters in WHERE
  2. Always use SELECT *
  3. Use NOT EXISTS rather than NOT IN with nullable subqueries
  4. Rely on default row order
Show answer

Answer: Use NOT EXISTS rather than NOT IN with nullable subqueries

NULL-safe anti-joins.

Lesson: An SQL Review Checklist

← Back to the course