SQL Interview Mastery: 100 Essential Q&As
SQL Interview Mastery: 100 Essential Q&As
Structure Your Study Plan
Do not memorize answers. Group the 100 questions into five core categories: **Fundamentals**, **Joins & Set Operations**, **Aggregation & Window Functions**, **Data Modeling & Performance**, and **Scenario-Based Problem Solving**. Allocate 70% of prep time to writing queries against a sample dataset (e.g., PostgreSQL `dvdrental` or MySQL `employees`). Passive reading yields false confidence; active execution reveals syntax gaps.
Master the High-Frequency Patterns
Interviewers recycle the same logical patterns. Prioritize these question archetypes:
1. **Running Totals & Moving Averages:** `SUM() OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)`.
2. **Top-N Per Group:** `ROW_NUMBER() OVER (PARTITION BY category ORDER BY metric DESC)` filtered in a CTE or subquery where `rn = 1`.
3. **Gaps & Islands:** Identify consecutive sequences using `LAG()` difference or `ROW_NUMBER()` subtraction techniques.
4. **Self-Joins:** Compare rows within the same table (e.g., Employee vs Manager, sequential events).
5. **Pivot/Unpivot:** Conditional aggregation (`CASE WHEN` inside `SUM`) for crosstabs; `UNION ALL` or `CROSS APPLY`/`UNNEST` for reverse.
Optimize for the "Why," Not Just the "How"
For every query you write, prepare to explain the **execution plan**.
* **Indexing:** Know when a composite index `(user_id, created_at)` beats two single-column indexes. Explain covering indexes vs. key lookups.
* **Sargability:** Demonstrate why `WHERE DATE(created_at) = '2023-01-01'` prevents index seeks and how to rewrite it as `WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02'`.
* **Joins vs. Subqueries:** Articulate when `EXISTS` outperforms `IN` or `JOIN` (semi-joins, NULL handling).
* **Isolation Levels:** Define Read Committed, Repeatable Read, and Serializable. Explain Phantom Reads and Dirty Reads with concrete transaction timelines.
Practice the Whiteboard Workflow
1. **Clarify:** Ask for schema, data volume, and expected output format. Confirm edge cases (NULLs, duplicates, missing foreign keys).
2. **Draft:** Write pseudocode or a rough CTE structure. Use CTEs (`WITH` clauses) to modularize logic—it improves readability and debugging.
3. **Refactor:** Optimize. Replace correlated subqueries with joins or window functions. Remove `SELECT *`. Explicitly list columns.
4. **Test:** Walk through sample data manually. Check for division by zero, integer division truncation, and timezone ambiguities.
Final Review Checklist
Before the interview, ensure you can solve these cold in under 10 minutes each: Median per group (using `PERCENTILE_CONT` or `ROW_NUMBER` logic), Sessionization (gap > 30 mins), Funnel Analysis (multi-step conversion), and Slowly Changing Dimension (SCD Type 2) merge logic.
Build muscle memory, not memory palaces. Streamline your interview prep workflow with **Wild Cherry Capital's tools**.