Window functions
On This Lesson 4 topics
This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.
In this lesson
compute per-row values over a group of rows without collapsing them.
Planned topics
GROUP BYreturns one row per group; a window function keeps every row and adds a computed column.OVER (PARTITION BY ... ORDER BY ...)defines the window.- Ranking:
ROW_NUMBER(unique),RANK(ties share, gaps follow),DENSE_RANK(no gaps). LAG/LEADread the previous/next row;sum(...) OVER (ORDER BY ...)gives a running total.- You cannot filter on a window result in
WHERE; wrap it in a CTE or subquery.
Draft outline details
Demonstration
SELECT day, revenue,
sum(revenue) OVER (ORDER BY day) AS running_total,
revenue - lag(revenue) OVER (ORDER BY day) AS change
FROM daily_sales;Check your understanding
Which keeps all rows: GROUP BY or a window function?
Show answer
The window function.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.