SQL Practice Suite
Advanced SQL problems with worked solutions — going well beyond GROUP BY into the patterns that actually come up in production data engineering and analytics work.
Overview
A lot of SQL practice resources stop at aggregations. This suite covers the harder patterns — handling hierarchies, building slowly changing dimensions, writing queries that are both readable and performant at scale. Problems span ANSI SQL, T-SQL, Snowflake SQL, and Spark SQL. Every problem includes a worked solution and commentary on trade-offs between approaches.
Window Functions
RANK, DENSE_RANK, ROW_NUMBER, LAG/LEAD, running totals, NTILE — applied to real analytical problems.
Recursive CTEs
Org chart traversal, bill of materials, category trees — problems that require iterative or hierarchical logic.
SCD Patterns
Type 1, 2, and 3 slowly changing dimensions with merge logic, effective date ranges, and history tracking.
Query Optimisation
Execution plan analysis, index strategy, and partition pruning — writing queries that hold up at enterprise scale.
Roadmap
Core problem set with worked solutions and commentary on performance trade-offs.
Merge-based SCD implementations in T-SQL and Snowflake SQL with full worked examples.
Execution plan analysis and partition pruning problems with before/after performance comparisons.
FLATTEN, LATERAL, PIVOT, MATCH_RECOGNIZE, and Time Travel — patterns unique to Snowflake's SQL dialect.
Translating the SQL patterns into dbt models — showing the SQL → dbt mapping for each problem type.