SQL Screen Drills (Timed Sets)

This lesson on SQL Screen Drills (Timed Sets) is hands-on and example-driven. You will master high-stakes technical screen patterns by applying pattern-first SQL architectures under strict time limits. You will rapidly detect and solve gaps-and-islands, deduplication windows, and recursive structures while diagnosing query execution bottlenecks using EXPLAIN.

What You'll Be Able To Do

• Identify algorithmic SQL patterns such as gaps-and-islands and dedupe windows within 60 seconds of prompt review.
• Formulate recursive CTEs to traverse hierarchical structures and generate dynamic sequences under timed conditions.
• Implement row-number difference techniques to isolate contiguous temporal islands.
• Inspect query plans using EXPLAIN to spot full table scans, sorting bottlenecks, and costly hash joins.
• Articulate solution design and trade-offs out loud to satisfy the mock interview defense rubric.

Detailed Concept Walkthrough

1. Pattern-First Solving and Strategy

Technical SQL screens evaluate pattern recognition speed rather than raw typing speed. Identifying the core archetype upfront dictates your query architecture before writing code.

• Mental Model: Deconstruct the prompt into canonical problem archetypes: contiguous state tracking (gaps/islands), hierarchy traversal (recursion), or ranking/deduplication (window frames).
• Execution Flow: Spend the first 90 seconds narrating assumptions, verifying edge cases (NULLs, duplicate timestamps), and stating the chosen pattern out loud to align with the interviewer.
• Best Practice: Build queries modularly using Common Table Expressions (CTEs), verifying intermediate outputs mentally or through dry runs before assembling final aggregation logic.

-- Template: Modular CTE structure for timed screens
WITH ranked_records AS (
  SELECT
    user_id,
    event_time,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) AS seq
  FROM user_events
)
SELECT user_id, seq
FROM ranked_records
WHERE seq = 1;

Key Takeaway: Classifying the problem archetype in the first minute prevents destructive mid-interview rewrites.

2. Gaps and Islands Detection

Gaps-and-islands problems identify consecutive sequences of identical states or chronological intervals using offset differences.

• Mechanism: Calculate the difference between a global row index and a partitioned row index, or subtract an incremental row count from a date sequence. The resulting constant offset uniquely tags contiguous 'islands'.
• Under the Hood: Window functions assign ordinal positions via an in-memory sort buffer without collapsing the underlying rows prior to downstream grouping.
• Best Practice: Use DATE_SUB(event_date, INTERVAL ROW_NUMBER() ... DAY) or ROW_NUMBER() OVER(...) - ROW_NUMBER() OVER(...) to generate deterministic group identifiers.

-- Identify consecutive active streak islands per user
WITH numbered_events AS (
  SELECT
    user_id,
    activity_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) AS rn
  FROM daily_logins
),
island_groups AS (
  SELECT
    user_id,
    activity_date,
    DATE_SUB(activity_date, INTERVAL rn DAY) AS island_id
  FROM numbered_events
)
SELECT user_id, MIN(activity_date) AS streak_start, MAX(activity_date) AS streak_end, COUNT(*) AS streak_length
FROM island_groups
GROUP BY user_id, island_id;

Key Takeaway: A constant mathematical delta between an ordinal sequence and a monotonic metric defines an island group.

3. Deduplication Windows

Windowed ranking functions enforce deterministic row selection across duplicate dimensions without dropping relational attributes.

• Mechanism: Apply ROW_NUMBER() or DENSE_RANK() partitioned by primary entity keys and ordered by recency or priority criteria.
• Under the Hood: Unlike GROUP BY which discards non-aggregated attributes, window partitioning retains full tuple contexts, streaming output via row filters.
• Syntax Rule: Filter on the derived ranking rank in an outer query wrapper since window functions are evaluated in the SELECT phase, after WHERE and HAVING.

-- Deduplicate transaction streams to keep the latest status per transaction
WITH ranked_transactions AS (
  SELECT
    txn_id,
    user_id,
    amount,
    status,
    updated_at,
    ROW_NUMBER() OVER (
      PARTITION BY txn_id 
      ORDER BY updated_at DESC, id DESC
    ) AS dedupe_rank
  FROM raw_transactions
)
SELECT txn_id, user_id, amount, status, updated_at
FROM ranked_transactions
WHERE dedupe_rank = 1;

Key Takeaway: Enforce strict tie-breakers in window ORDER BY clauses to guarantee deterministic deduplication output.

4. Recursive CTE Traversal

Recursive CTEs iteratively process hierarchical tree structures, graph dependencies, and sequential date gaps without external iteration loops.

• Mechanism: Define an anchor query that seeds the base dataset, combined via UNION ALL with a recursive query that references the CTE itself until an exit condition terminates.
• Execution Flow: The database engine maintains a working table, repeatedly executing the recursive member against the latest iteration's result until an empty working set is returned.
• Best Practice: Always specify an explicit recursion termination condition in the WHERE clause to avoid infinite loops and query timeouts during interview execution.

-- Traverse management hierarchy to resolve reporting depth
WITH RECURSIVE org_chart AS (
  -- Anchor member: top-level executives
  SELECT employee_id, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  -- Recursive member: downstream reports
  SELECT e.employee_id, e.manager_id, o.depth + 1
  FROM employees e
  INNER JOIN org_chart o ON e.manager_id = o.employee_id
  WHERE o.depth < 20 -- Safety termination guard
)
SELECT employee_id, manager_id, depth FROM org_chart;

Key Takeaway: Recursive queries require an explicit anchor, an iterative join step, and a strict termination condition.

5. EXPLAIN Plan Diagnosis

Query plan analysis verifies cost estimation, scan patterns, and index utilization to defend performance trade-offs during screens.

• Mechanism: Prepend EXPLAIN or EXPLAIN ANALYZE to inspect operational steps including sequential scans, index scans, nested loops, and hash aggregations.
• Under the Hood: Query optimizers use catalog statistics to generate candidate execution trees and select the lowest cost-based operator pipeline.
• Best Practice: Look out for explicit sorting steps (Sort), sequential table scans (Seq Scan on large tables), and spilling hash aggregates to disk buffers.

-- Inspect execution mechanics and join algorithms
EXPLAIN ANALYZE
SELECT u.user_id, COUNT(e.event_id)
FROM users u
LEFT JOIN events e ON u.user_id = e.user_id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.user_id;

Key Takeaway: Use EXPLAIN to validate index scans and explain algorithmic complexity trade-offs to the interviewer.

Topics Covered in SQL Screen Drills (Timed Sets)

1. Pattern-First Solving (0:00 - 1:30) — Establishes rapid archetype identification and structured CTE design under timed interview constraints.
2. Gaps and Islands Architecture (1:30 - 3:15) — Demonstrates the row-number difference technique to compute contiguous historical streaks.
3. Dedupe Windows Setup (3:15 - 4:45) — Applies deterministic window ranking functions to isolate primary entity states without losing attributes.
4. Recursive CTE Construction (4:45 - 6:30) — Walks through building anchor members and recursive joins for tree-structured hierarchies.
5. EXPLAIN Plan Diagnosis (6:30 - 8:00) — Reviews execution plans to identify query bottlenecks and articulate performance trade-offs.

DS Interview Prep Cheat Sheet

• ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) — Assigns unique ordinal integers to rows within a partition

  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)
  

• DATE_SUB(date, INTERVAL rn DAY) — Generates constant island identifier across contiguous dates

  DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (ORDER BY login_date) DAY)
  

• WITH RECURSIVE cte_name AS (...) UNION ALL (...) — Iteratively expands hierarchical nodes or sequences

  WITH RECURSIVE t AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM t WHERE n<10)
  

• EXPLAIN ANALYZE — Executes query and prints actual execution node statistics

  EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'SHIPPED';
  

• DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...) — Ranks rows with ties receiving identical, non-skipping numbers

  DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC)
  

Comparison Table

Common Pitfalls

• Mistake: Filtering window ranks directly inside the WHERE clause of the definition query. Avoid: Wrapping window expressions inside a CTE or subquery before applying rank predicates.
• Mistake: Omitting secondary sort criteria for deterministic window ordering. Avoid: Including primary key columns in the window ORDER BY clause to break ranking ties.
• Mistake: Missing termination clauses in recursive CTE queries. Avoid: Writing strict depth boundaries or parent checks in the recursive member WHERE clause.

FAQs

• When should I use ROW_NUMBER() versus DENSE_RANK() for deduplication? Use ROW_NUMBER() when you must guarantee exactly one unique row per partition. Use DENSE_RANK() when ties must be preserved collectively.
• How does DATE_SUB identify continuous streaks in gaps-and-islands? As both consecutive dates and row numbers increment by one daily, subtracting the row number from the date produces an invariant reference date for contiguous records.
• What should I look for first in an EXPLAIN output during an interview? Check for unexpected sequential scans on large tables and expensive Sort operations that could be mitigated via index coverage.