Vertex

CTEs and window functions

CTEs make a long query readable; window functions compute across related rows without collapsing them. Running totals and rankings stop being hard.

Lesson 1.37m 52sIntermediate10.7k students

Chapters

7:52
  1. 0:00Intro
  2. 0:09View the table
  3. 0:28ORDER BY
  4. 1:18Window function with ROW_NUMBER
  5. 1:40OVER
  6. 2:36Breaking down the window function
  7. 3:28ROW_NUMBER vs RANK vs DENSE_RANK
  8. 5:13PARTITION BY
  9. 6:52Window function in a subquery

What this lesson covers

  • Break a complex query into named CTEs
  • Rank and number rows within partitions
  • Compute running totals and row-to-row differences

This lesson sits in SQL Essentials, part of PostgreSQL for Developers. It assumes what came before it and leads directly into the next lesson in the module.

In this lesson you will

  • Break a complex query into named CTEs
  • Rank and number rows within partitions
  • Compute running totals and row-to-row differences

Resources

Previous lessonAggregates and GROUP BY1.2 · 6mNext lessonNormalisation and when to break it2.1 · 3m 33s