Skip to content
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 this module

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

  • Write queries that filter, sort and aggregate (SELECT, WHERE, ORDER BY, GROUP BY, HAVING) while handling NULL values and dates correctly.
  • Turn a product question into a verifiable query, using CTEs and simple window functions (ROW_NUMBER, LAG, SUM OVER).

Related courses