Module
Module 5 of 6Lesson 1 of 2~18 min

Break queries down with WITH, compare with window functions

Month-over-month change, each customer's latest state, share of total: tracking questions call for more structured queries. You learn to break a query into readable steps and to compare rows with one another using window functions. You spot a disengaging customer sooner and share a query others can review.

Take this lesson: your first course is freeSee the full syllabus

Lesson objective

By the end of this lesson, you will be able to break a query into named steps with WITH (CTEs) and use three window functions (ROW_NUMBER to keep the latest row of each group, LAG to compute a change, SUM OVER for a running total or a share of the total).

Topics covered

  • CTE
  • WITH clause
  • window functions
  • ROW_NUMBER
  • LAG
  • monthly tracking

Where it fits

Answer product questions

How do I go from a vague question ("are our customers active?") to a query whose result I can defend?

Lessons in this module

  1. Break queries down with WITH, compare with window functions (this lesson)
  2. From a product question to a query you can defend

What you will learn in the course

This lesson is part of the course Model your product’s data and query it with SQL

  • Read a data model (tables, primary and foreign keys, cardinalities, entity-relationship diagram) and connect it to the product's screens and rules.
  • Model the data of a feature (entities, attributes, relationships, keys, cardinalities) and get the model validated by the engineering team.
  • Compare a relational database and a document (NoSQL) database for a product need and justify the choice based on expected queries, consistency and change.
  • Write queries that filter, sort and aggregate (SELECT, WHERE, ORDER BY, GROUP BY, HAVING) while handling NULL values and dates correctly.
  • Combine several tables with INNER and LEFT joins, and check that the result neither loses nor duplicates any row.
  • Turn a product question into a verifiable query, using CTEs and simple window functions (ROW_NUMBER, LAG, SUM OVER).
  • Query data read-only while protecting personal data, and have an AI draft SQL while checking every query before using its result.

Related courses