SQL Interview Patterns: Advanced

The five tricks that gate every data engineering loop

The five tricks that gate every data engineering loop

Category

SQL

Difficulty

advanced

Duration

45 minutes

Challenges

0 hands-on challenges

Topics covered: Sessionization, Gaps and Islands, Deduplication via ROW_NUMBER, Recursive CTEs, Conditional Pivot

Lesson Sections

1. Sessionization

   The Pattern Now take the cumulative sum of that flag: The Query How to Spot It Follow-Ups

2. Gaps and Islands

   The Pattern The Query Gaps vs. Islands Both use the same subtraction trick. Islands group consecutive rows. Gaps find the spaces between them. How to Spot It Follow-Ups

3. Deduplication via ROW_NUMBER

   You have a customers table where each customer appears multiple times with different timestamps. Keep exactly one row per customer: the most recent record. The Pattern The Query ROW_NUMBER vs. RANK vs. DENSE_RANK How to Spot It Follow-Ups

4. Recursive CTEs

   The Pattern The Query Other Problems, Same Shape How to Spot It Follow-Ups

5. Conditional Pivot (concepts: sqlPivotCrosstab)

   You have a tall table of monthly metrics. One row per (product, month) pair. Turn it into a wide table with one column per month. Most SQL engines have no PIVOT keyword. Desired output: The Pattern SUM vs. MAX How to Spot It Follow-Ups