Interview Prep
SQL interview questions and how to answer them
SQL interviews test whether you can write correct queries under time pressure and explain what they're doing. Window functions and query performance come up more than most people expect. These are the questions you'll face.
PrepVault surfaces the questions you need and keeps your prep organized by role. Free to start.
SQL interview questions and how to answer them
How do you calculate a 7-day moving average using a window function?
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for 7-row window. ROWS uses physical position, not calendar distance. RANGE with interval handles calendar gaps correctly in PostgreSQL. date spine approach ensures no missing day issues
How do you find rows in one table that have no corresponding row in another table?
LEFT JOIN with IS NULL on right side finds unmatched left rows. NOT EXISTS is semantically clear and safe with NULLs. NOT IN fails silently when subquery returns any NULL. both LEFT JOIN and NOT EXISTS are safe choices
What is a database index and how does it affect query performance?
B-tree structure enables O(log n) lookup. speeds reads, slows writes. foreign keys and WHERE columns are index candidates. composite index column order matters
What does EXPLAIN or EXPLAIN ANALYZE output tell you about a query?
EXPLAIN shows the planned execution path. EXPLAIN ANALYZE runs the query and shows actual vs estimated rows. Row count estimate errors explain bad plan choices. Sequential scans on large tables are the first thing to investigate
How would you implement a distributed saga pattern to maintain data consistency across multiple microservice databases without a distributed transaction?
local transactions + compensating transactions. choreography vs orchestration. idempotency required for at-least-once delivery. outbox pattern for durable event publishing. eventual consistency, not ACID atomicity. compensations are forward corrections not rollbacks
How do you pivot rows into columns using SQL aggregation?
conditional aggregation: SUM(CASE WHEN category = X THEN value END). one CASE expression per pivot column. pivot columns must be known at write time. dynamic pivot requires dynamic SQL string building
How do GIN indexes support JSONB queries in PostgreSQL?
JSONB containment. key existence. jsonb_ops. jsonb_path_ops
How would you design a query to find the longest path in a directed acyclic graph stored as an edge list in SQL?
anchor on zero-in-degree nodes. recursive member extends paths. path array for cycle safety and reconstruction. MAX aggregation after CTE. scalability limits of SQL graph traversal
How would you diagnose and fix a slow SQL query in a production database?
EXPLAIN ANALYZE shows actual vs estimated row counts. look for sequential scans on large tables. function on indexed column disables the index. measure before and after on production-size data
What is ROLLUP and when would you use it?
ROLLUP generates subtotals and grand totals automatically. produces (a,b), (a), () grouping levels in hierarchy. NULLs in rolled-up columns represent the subtotal level. GROUPING() distinguishes rollup NULLs from real NULLs
Common mistakes
- Wrong join type and not noticing
- Skipping window functions when they fit
- Queries that work but do not scale
What interviewers weigh
- Correctness on joins and aggregation
- Whether you can explain query performance
- Comfort with window functions