Window functions

Rank and accumulate values without collapsing rows.

Beginner 15 min read Lesson 12 of 480 Challenge included

What you will be able to do

Use OVER with PARTITION BY
Rank rows inside each group
Filter a window result with a CTE

A window function sees a set of related rows but returns one value per row. GROUP BY collapses rows; a window keeps them.

windows.sqlSQL

SELECT course_slug, title, minutes,
       ROW_NUMBER() OVER (
         PARTITION BY course_slug ORDER BY minutes DESC
       ) AS rank_in_course,
       SUM(minutes) OVER (PARTITION BY course_slug) AS course_minutes,
       ROUND(AVG(minutes) OVER (PARTITION BY course_slug), 1) AS course_avg
FROM lessons
ORDER BY course_slug, rank_in_course;

Function	Returns
ROW_NUMBER()	Sequential position inside the window
RANK()	Position with ties sharing a rank
SUM() OVER	Running or grouped total
AVG() OVER	Average across the window

Your turn

Top two lessons per course

Hard

Return the two longest lessons in each course, with their rank inside the course.

SQL · starter

WITH ranked AS (
  SELECT course_slug, title, minutes,
         ROW_NUMBER() OVER (
           PARTITION BY course_slug ORDER BY minutes DESC
         ) AS rank_in_course
  FROM lessons
)

Check your understanding before moving on.

Knowledge checkQuestion 1

What does PARTITION BY do in an OVER clause?

Select an answer to continue

Study tools

Make it yours.

Keep a private note for this lesson, then turn the key ideas into flashcards you can revisit.

Sign in to save notes and flashcards.Both are private to your account and stay with your progress.

Finished reading?

Lock in what you just learned.

Mark this lesson complete to save your place and move one step closer to the certificate.

Next: Text and dates

Lesson 12 of 480 · 0% of course