Interview
SQL Scenarios: Real-World SQL & LeetCode Patterns
Real-world SQL scenarios and the LeetCode patterns behind them, with the reasoning shown.
- Handwritten-style
- Diagrams and tables
- Worked examples
Who this notebook is for
Candidates who already know basic SQL and now face scenario questions: streaks, funnels, cohorts, duplicates and slowly changing data.
What you will be able to do
- Solve gaps and islands and consecutive-event problems with window functions
- Build sessions, funnels and retention cohorts from raw event rows
- Deduplicate and merge changing data with MERGE and CDC patterns
- Tune a slow query and explain the plan the way an interviewer wants to hear it
What's inside
Pick a chapter and the notebook opens right there.
- Advanced Window Functions & Analytical RankingOVER frame specifications (ROWS vs RANGE BETWEEN), ROW_NUMBER vs RANK vs DENSE_RANK ties, LEAD/LAG period-over- period growth, running totals with UNBOUNDED PRECEDING, FIRST_VALUE/LAST_VALUE IGNORE NULLS, and Top-N per group ranking CTEsStart here
- Gaps & Islands, Consecutive Events & StreaksActive user login streaks, the difference-of- row-numbers (RN - Date) mathematical trick, conditional sum / running flag island detection, missing sequence gap detection with GENERATE_SERIES, consecutive available slot finding, and interval range mergingStart here
- Sessionization, Funnels & User Journey AnalyticsClickstream sessionization with 30- minute inactivity thresholds, unique session ID generation via LAG + SUM OVER, multi-step e-commerce conversion drop-off funnels, First-Touch vs Last-Touch marketing attribution, and LEAD event sequence pathingStart here
- Retention, Churn, Cohorts & Lifecycle AnalyticsMonthly cohort retention matrix grid (Month 0, Month 1, Month 2...), rolling retention vs strict anniversary retention, customer churn definition via dormant inactivity windows, cumulative customer lifetime value (LTV), and user lifecycle state transitions (New, Active, Churned, Resurrected)Start here
- Complex Transformations, Deduplication & HierarchyBig Data deduplication with QUALIFY vs ROW_NUMBER(), Slowly Changing Dimensions (SCD Type 2) valid-to intervals, Recursive CTEs for organizational hierarchy and graph DAG traversal, Pivot/Unpivot cross- tabulations, JSON/Array unnesting, and Anti-join NULL trapsStart here
- Query Performance Tuning, EXPLAIN & FAANG DrillsLogical SQL execution order (FROM to LIMIT), EXPLAIN ANALYZE deep dive (Seq Scan, Index Scan, Bitmap Heap Scan), join algorithms (Nested Loop, Hash Join, Merge Join memory spills), columnar warehouse partition pruning, and LeetCode Hard staff interview scenariosStart here
- Non-Equi Joins, Range Overlaps & Cartesian ExplosionTemporal validity interval joins, dynamic tax bracket / commission tier matching, room booking conflict detection with OVERLAPS, spatial proximity joins, asof joins for financial tick data, and Cartesian product explosion containmentStart here
- GROUPING SETS, ROLLUP, CUBE & Multi-Dimensional AnalyticsEliminating repetitive UNION ALL aggregations, hierarchical subtotals with ROLLUP, full N-dimensional combinations with CUBE, GROUPING() / GROUPING_ID() indicator flags, and building executive financial P&L matricesStart here
- Recursive CTEs, Graph Traversal & Hierarchical BOMAnchor & recursive union mechanics, organizational org charts with path tracking, manufacturing Bill of Materials (BOM) multi- level explosion, cycle detection with array path history, and graph connected componentsStart here
- Semi-Structured Data: JSON Parsing, Arrays & Lateral FlatteningIngesting raw JSON payloads, PostGIS/Snowflake JSON extraction operators ( payloads, PostGIS/Snowflake JSON extraction operators ( -> ->,, ->> ->>,, : ), CROSS JOIN ), CROSS JOIN LATERAL / FLATTEN / UNNEST arrays, schema-on-read transformation, and polymorphic key pivotingStart here
- Time-Series Gap Filling, Rolling Windows & Outlier Z-ScoresContinuous calendar generation with GENERATE_SERIES, dense imputation of missing metric intervals with LOCF (Last Observation Carried Forward), rolling 7-day volatility, and statistical Z-score anomaly detectionStart here
- Data Quality, Reconciliation, Anomaly & Schema Drift DetectionSource-to-target reconciliation hash sums, automated data contract validations (nulls, ranges, regex constraints), schema drift detection via information_schema, and statistical distribution drift testingStart here
- Incremental Processing, Change Data Capture (CDC) & MERGEDebezium CDC change log replay, idempotent MERGE (UPSERT) operations, handling out-of-order event arrivals via watermarking, tombstone soft deletes, and building robust Type 2 SCD pipelines in SQLStart here
- Data Warehouse Architecture Tuning (Snowflake, BigQuery, Redshift)BigQuery dynamic slot execution & capacitor columnar partitioning vs clustering, Snowflake micro-partition pruning & warehouse spilling triage, Redshift distribution styles, and Databricks Liquid ClusteringStart here
- Staff Data Engineer LeetCode Hard & FAANG System DrillsUber/Lyft H3 hexagonal surge pricing allocation, Netflix binge sessionizer & pairwise movie co-occurrence matrix, Stripe recursive fraud ring detection, Amazon dynamic safety stock ROP, LinkedIn social graph PYMK, and live debugging stalled jobsStart here
- Modern Staff Data Engineering SQL ScenariosLate-arriving fact reconciliation, deadlock prevention, MAD statistical anomaly & hybrid vector RRFStart here
- Gap-Fill SupplementRFM segmentation, A/B test z-score, Pareto/ABC analysis, percentiles & Kaplan-Meier survivalStart here
Practice this
Put it to work on LakeBench.
Read the guide first
Short explainers that pair with this notebook.
Common questions
- Is this for beginners?
- No. It assumes you can already write joins and aggregates. If you cannot, read the SQL notebook first and come back for the scenarios.
- Do the scenarios come with explanations?
- Yes. Each scenario shows the reasoning step by step, not only the final query, so you can adapt the pattern to a new question.
- Can I read a sample before I commit?
- Yes. The contents pages and the first pages of every notebook can be read online, so you can judge the style before going further.
