FAANG SQL Interview Questions & Answers

1. Introduction

FAANG-level SQL interviews test your ability to write complex queries, design schemas, reason about performance, and explain database internals. This topic consolidates the most frequently asked questions across all chapters, with model answers that demonstrate depth and clarity. Questions span four categories: Conceptual (ACID, isolation), Query Writing (joins, windows, CTEs), Design (schema, indexing), and Performance (EXPLAIN, optimization).

2. Why It Matters

Database interviews at top companies assess not just syntax knowledge but system-level thinking. A candidate who can explain why a Hash Join outperforms a Nested Loop for large datasets, or design a schema that handles 100M users, demonstrates production-ready skills. Preparing structured answers with examples shows depth beyond memorization.

3. Real-World Analogy

Interview preparation is like rehearsing for a play — you don't just memorize lines (SQL syntax), you understand your character's motivation (why the database works that way), practice scene transitions (connecting concepts), and prepare for improv (handling follow-up questions).

4. How It Works

Interviewers typically evaluate: (1) Problem understanding — do you ask clarifying questions? (2) Query correctness — does the SQL produce the right results? (3) Performance awareness — do you consider indexes, plan choices, and scalability? (4) Communication — can you explain trade-offs clearly? Structure your answers using the STAR method: Situation, Task, Action, Result.

5. Internal Architecture

Questions typically follow a depth-first pattern: the interviewer starts with a simple query, then probes deeper. Example flow: "Write a query to find top 3 salaries per department" → "Now add a running total" → "How would this perform on 100M rows?" → "Design indexes for this query" → "What isolation level would you use?"

6. Visual Explanation

The diagram maps the four question categories with example topics from each chapter, showing how interview questions connect across the SQL knowledge spectrum.

7. Practical Example

Q: Find the second highest salary in each department.

Q: Explain the difference between RANK, DENSE_RANK, and ROW_NUMBER.

Q: Design a schema for a ride-sharing app (Uber-like).

8. Common Mistakes

• Jumping straight to code: Always clarify requirements first — table sizes, data distribution, query frequency, and consistency needs.
• Ignoring edge cases: NULL values, duplicate rows, empty tables, and timezone issues commonly trip up candidates.
• Not mentioning indexes: Every query you write should include what indexes you'd create for production performance.
• Over-engineering: Don't propose sharding for a 10K-row table. Start simple and scale up when asked about larger volumes.

9. Quick Quiz

10. Scenario-Based Challenge

Prepare a 45-minute mock interview covering: (1) Write a CTE to find customers who ordered every month in the last year (relational division). (2) Design an index strategy for a table with 1B rows queried by date range and customer_id. (3) Explain what happens when two transactions simultaneously update the same counter. (4) Debug a query that runs in 100ms on dev but 30s in production.

11. Debugging Exercise

12. Interview Questions

• Q: Design a database for a social media platform with posts, comments, likes, and follows. How would you handle feed generation?
  A: Use a fan-out-on-write pattern — when a user posts, insert into a feeds table for all followers. For large follower counts, use a hybrid approach: push to small follower feeds, pull for celebrity accounts. Partition feeds by user_id and use materialized views for trending content.
• Q: A query uses an index on dev (10K rows) but does a Seq Scan on production (10M rows). Why?
  A: Stale statistics — the planner thinks the table is small. Run ANALYZE. Or: the query selects >20% of rows, making Seq Scan cheaper than Index Scan due to random I/O cost.
• Q: Explain MVCC to a junior developer.
  A: When you UPDATE a row, PostgreSQL doesn't overwrite it. It creates a new version and marks the old one. Other transactions still reading see the old version. This means readers never block writers and writers never block readers.

13. Production Considerations

• Practice environment: Set up a local PostgreSQL instance with pgbench-generated data (millions of rows) to test query performance realistically.
• EXPLAIN ANALYZE everything: In interviews, always mention checking the execution plan. This shows production thinking.
• Know your PostgreSQL version: Features like generated columns (PG 12), logical replication (PG 10), and parallel queries (PG 9.6+) are version-specific. Mention this in interviews.
• System design overlap: Many SQL interview questions connect to system design — caching strategies, read replicas, event sourcing. Be ready to discuss the broader architecture.