Stage 2 · Lesson 1 of 9

Analytical SQL: CTEs and aggregation

Most in data engineering is analytical. It reads large amounts of data, joins several tables and adds things up, and it should read as clear steps. (CTEs) help with this.

A CTE starts with WITH and lets you build a query as named steps, one after another. are written the same way. Get comfortable with GROUP BY and HAVING, CASE expressions and conditional totals such as SUM(CASE WHEN ...).

Also practise cutting dates down to a day or month, and handling missing values () safely. If joins, keys and basic queries are new to you, work through the SQL and databases roadmap first, because this topic assumes them. Always check row counts after each join.

If you join on a key that is not unique, the join quietly repeats rows. This is called .

Common trapfixing duplicate rows with DISTINCT instead of finding the join that created them.

A question to check yourself, an AI practice session and 2 cheat sheet entries come with the full roadmap. Unlock

Resources for this stage

Start with

CourseFree
SQL Tutorial for Data Analysis (formerly Mode)
ThoughtSpot · Beginner
Its advanced section covers window functions, performance tuning and pivoting on realistic data

Practise

LeetCode SQL 50
Fifty graded problems; the window function and aggregation ones match what pipeline SQL needs
Select Star SQL
An interactive book on one real dataset that ends with window functions and self joins

Keep handy

Kimball Dimensional Modeling Techniques
The original definitions of grain, conformed dimensions and SCD types, straight from the source
roadmap.sh: SQL
Check which SQL topics you still lack before moving on

Also worth your time

Data Warehouse Design: Schemas, Steps, and Best Practices (opens in a new tab)Read
Star and snowflake schemas, facts and dimensions, and the steps of a warehouse design in one read