A data engineer I know got asked this in a 2025 Google screen: write a query that returns the second-highest salary from an employees table. He blanked. Not because he didn’t know SQL, but because he’d been writing CTEs and window functions for 3 years and hadn’t thought about that kind of puzzle problem since his first job. He came back from the interview and said, “I forgot that interviews test a different skill than production work.”
That gap is real. Production SQL is about maintainability, query plans, and working inside a schema someone else designed. Interview SQL tests whether you can construct a correct query from scratch, explain why it works, and optimize it on a whiteboard. The overlap is significant, but preparing only from production experience leaves gaps.
The questions that appear in almost every SQL screen
These aren’t predictions, they’re pattern observations from engineers who’ve gone through screens at companies ranging from early-stage startups to major tech companies in the last 18 months:
JOINs. INNER, LEFT, RIGHT, FULL OUTER. The common trap question: what’s the difference between a LEFT JOIN and a LEFT OUTER JOIN? (Nothing. They’re synonyms. Interviewers ask this to see if you get flustered by trick questions.) The more substantive version: when would you choose a full outer join over running two separate left joins and unioning the results? Query planner behavior and NULLs are the right areas to discuss.
WHERE vs. HAVING. WHERE filters rows before aggregation. HAVING filters groups after aggregation. You cannot use a WHERE clause to filter on an aggregate function result. This comes up in maybe 60% of SQL screens according to engineers I’ve spoken with, and a surprising number of people still stumble on it.
NULL behavior. NULL in SQL is not zero, not empty string, not false. NULL is unknown. Any comparison with NULL using = returns NULL, not TRUE or FALSE. You need IS NULL or IS NOT NULL. Arithmetic with NULL produces NULL. This trips up people who learned SQL from web tutorials that gloss over it.
The second-highest salary problem. Multiple valid approaches: subquery with MAX where salary < (SELECT MAX(salary)), OFFSET/FETCH, or DENSE_RANK() window function. The window function approach is cleanest for anything beyond second-highest, and demonstrating that you know when to use window functions vs. subqueries is a leveling signal.
Window functions: the separator between junior and mid-level
If you’re interviewing for anything above entry-level, you need to know window functions. ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and aggregate functions with OVER clauses. The 2024 Stack Overflow Developer Survey found PostgreSQL as the most used database professionally (49% of respondents), and Postgres has excellent window function support, so questions are often framed around Postgres syntax.
A commonly asked question: given a table of sales transactions with a timestamp, write a query that shows each transaction alongside the previous transaction amount for the same customer. That’s LAG(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date). The PARTITION BY is where most people get it wrong, either omitting it or using the wrong column.
CTEs (common table expressions) often come up alongside window functions. The question pattern: “Can you rewrite this correlated subquery as a CTE?” The answer is almost always yes, and the CTE version is almost always more readable. Whether it’s faster depends on the query planner, and a good answer acknowledges that uncertainty rather than claiming CTEs are always more performant.
Query optimization questions
At senior level, expect to discuss indexes, execution plans, and query rewrites. The classic bad query pattern interviewers present: a correlated subquery inside a WHERE clause that executes once per row. Rewriting it as a JOIN or a subquery in the FROM clause is the expected answer.
Index questions: B-tree vs. hash indexes (B-tree for range queries, hash for equality only in most engines), covering indexes, partial indexes, and the cost of indexes on write-heavy tables. When an interviewer asks “why is this query slow?” the correct first move is to look at the execution plan (EXPLAIN ANALYZE in Postgres), not to guess.
The BLS Occupational Handbook for Database Administrators lists query tuning and index design among the core competencies for the role. The skills map directly to what interview questions test.
Schema design questions
For data engineer and backend-heavy roles, expect one schema design question. Usually framed as: “Design a database schema for an e-commerce order system” or “How would you model a hierarchical category structure in a relational database?” (The answer for hierarchies involves either adjacency lists, nested sets, or closure tables, each with different read/write tradeoff profiles.)
Normalization comes up here too. Third normal form is the standard target for transactional schemas. Denormalization is appropriate for analytical workloads where read speed matters more than write efficiency. Being able to name which normal form a given schema violates, and articulate why that might be acceptable, is a stronger answer than just saying “normalize it.”
How to practice
LeetCode’s database section and StrataScratch both have good interview-format SQL problems. Practice writing queries without an IDE autocomplete. Reading your own queries aloud and explaining what each clause does forces the kind of clarity interviewers are testing.
The second-highest salary problem my acquaintance blanked on is genuinely easy with 20 minutes of practice. Most interview SQL isn’t hard. The preparation gap is usually a question of format familiarity, not knowledge.