Reliable pipelines

Pro lesson Intermediate

Latest wins: window functions

Keep the newest version of each row when updates arrive more than once.

10 min8 hands-on steps

In this lesson you will

  • See why GROUP BY cannot pick the newest row
  • Number the rows inside each group with ROW_NUMBER
  • Keep the latest row per customer with QUALIFY and a tie-breaker

Words you'll meet

Window function
A function that looks at a group of rows but keeps every row, adding its answer as a new column.
Partition
One group of rows inside a window function, such as all the rows for one customer.
Deduplicate
Keep one row per key and drop the other copies.
Tie-breaker
An extra sort column that decides between rows that would otherwise tie.

The steps

Each step teaches one idea, then has you try it hands-on in your browser, and checks your work.

  1. 1Updates arrive more than once
  2. 2GROUP BY mixes rows
  3. 3Number the rows
  4. 4Start again for each customer
  5. 5Keep the newest
  6. 6QUALIFY, the shortcut
  7. 7Ties
  8. 8Challenge

This lesson is part of LearnDE Pro

$12 a month or $100 a year: every lesson, spaced-repetition flashcards, and Learn by teaching. Refund on request within 14 days.