Part 199: 50 Staff & Principal SQL Interview Questions & Scenarios

Top system design questions, architecture trade-offs, and query problems asked at top tech companies.

1. System Design & Concurrency Scenarios

1. How do you architect a flash-sale ticketing inventory system in SQL without overselling? - Answer: Use SELECT ... FOR UPDATE or optimistic concurrency with version checking (WHERE id = :id AND stock >= :qty).
2. Explain the difference between Optimistic and Pessimistic Concurrency Control.
3. What causes Transaction ID (TXID) wraparound in PostgreSQL and how do you recover?
4. Compare Keyset (Cursor) pagination vs Offset pagination for high-volume feeds.
5. How does an Index-Only Scan work and how do INCLUDE columns make it possible?
6. Why can NOT IN return 0 rows when a subquery contains even a single NULL?
7. Explain the physical difference between a B-Tree and an LSM-Tree.
8. When would you choose Hash Partitioning over Range Partitioning?
9. How do you eliminate parameter sniffing in SQL Server without turning off caching?
10. Explain how PostgreSQL MVCC handles row updates vs MySQL InnoDB Undo logs.

2. Query Architecture & Data Modeling

11. Design a high-throughput Ledger table enforcing zero-negative balance with pure constraints.
12. Compare Star Schema vs Snowflake Schema vs Data Vault 2.0.
13. How do you implement an SCD Type 2 dimension using modern SQL MERGE?
14. How do you safely rename a column on a table with 500 million rows without downtime?
15. Explain the difference between Nested Loop, Hash, and Sort-Merge joins.
16. What is an Index Seek vs Index Scan vs Bitmap Index Scan?
17. How does Snowflake separate compute from storage and why does it matter?
18. What is the Transactional Outbox pattern and why does it prevent dual-write bugs?
19. How does ClickHouse achieve sub-second aggregations on billions of rows?
20. What is DuckDB and what role does it play in modern data engineering stacks?

Key Takeaways

• Senior interviews test architectural trade-offs (storage vs speed, consistency vs availability) rather than basic syntax.

In Part 200, we conclude our masterclass with The Ultimate SQL Encyclopedia: 200-Topic Master Reference!