SQL interview questions that actually get asked in 2025

I watched a senior engineer with 8 years of experience get filtered out at a Series B startup in 2023 because he couldn't explain why a query with a perfectly valid index was doing a full table scan. He knew the syntax cold. He'd memorized CTEs, window functions, all of it. But he had no idea how the query planner actually makes decisions. That's the gap nobody talks about.

Most SQL prep content is stuck in 2015. Here's what's actually on the whiteboard right now.

The execution plan question is the new "reverse a linked list"

Almost every senior+ SQL question I've seen in the last 18 months starts with EXPLAIN ANALYZE or its equivalent. Not "write a query" — "here's a query, here's the plan, why is it slow."

The trap everyone falls into: they see Seq Scan and immediately say "add an index." Wrong. Sometimes the planner is right. If you're selecting 40% of a table, a sequential scan beats an index scan because random I/O is expensive and Postgres knows it. The planner uses random_page_cost vs seq_page_cost to make that call. Default values are 4.0 and 1.0. On SSDs, you should be setting random_page_cost = 1.1 and most people never do this.

Real question I've seen: "This query has an index on created_at but it's not using it. Why?" Answer: the column has low cardinality, or statistics are stale, or the planner estimated row counts wrong because ANALYZE hasn't run recently. Know how to run SELECT * FROM pg_stats WHERE tablename = 'orders' and read the histogram buckets.

Window functions: everyone knows the syntax, nobody knows the frame

Every candidate in 2025 can write ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at). That's table stakes. What breaks people is the frame clause.

vs

ROWS is physical. RANGE is logical. With duplicate created_at values, these return different results. RANGE includes all rows with the same order key as the current row in the "current" frame boundary. This trips up people writing financial aggregations and they produce subtly wrong numbers that pass QA.

The N+1 question disguised as a schema design question

In 2022, at a company running roughly 2M queries/day on RDS, a bad ORM query pattern that nobody caught in review cost about $12k/month in extra compute before someone finally ran pg_stat_statements and found 600 variations of the same query hitting the DB with single-row lookups. The interview version of this: "Given this schema, your ORM is generating this SQL — what's wrong and how do you fix it at the query level, not the application level." The answer they want involves JOIN strategy and understanding how your ORM's include vs preload vs eager_load translates to SQL.

Index types beyond B-tree

Partial indexes are underused and often the right answer. CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL — smaller index, faster scans for the 99% case. GIN indexes for JSONB and full-text search. The question: "you have a tags jsonb column and you're querying with @> — what index?" B-tree won't help you. GIN will.

What I'd do differently

Stop memorizing query patterns and start reading the Postgres source for the planner — specifically src/backend/optimizer/path/costsize.c. Run EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) on every slow query you've ever written and actually read the buffer hit ratio. The ratio of shared_hit to shared_read tells you more about your query performance than the query itself. Build the mental model of how the planner thinks and the interview questions answer themselves.