Ranking Within Groups

Waveform wants a chart: the most-played tracks in each genre. The One Row Per Group lesson would collapse each genre to one row, and groupBy("genre").agg(F.max("plays")) tells you the top play count and nothing else, not even which track it was. The chart needs the opposite: every track kept, each one labelled with its place in its genre. That is a window function: a calculation across a group of rows that keeps every row.

Watch it happen

Seven tracks, two genres:

from pyspark.sql import functions as F
from pyspark.sql.window import Window

w = Window.partitionBy("genre").orderBy(F.col("plays").desc())

result = plays.withColumn("rn", F.row_number().over(w))

Seven rows in, seven rows out. partitionBy("genre") says which rows belong together: each genre is a window partition, the same word as the data partitions of the Where Your Code Runs lesson for a different thing, which is why we always say which one. orderBy says what order they stand in within it. row_number() hands out 1, 2, 3 down that order, and .over(w) is what makes it a window function instead of a plain column. Nothing was collapsed; every track still has its title.

Three ways to number a tie

Afterglow and Circuit both have 750 plays. row_number gave them 2 and 3, and which got which is not defined: nothing in the data says Afterglow comes first. Sail, the engine here, happened to give Afterglow the 2 both times we ran it; a cluster, which reads the rows in whatever order they arrive, need not agree with itself. Two other functions treat the tie honestly:

result = plays.select(
    "genre", "title", "plays",
    F.row_number().over(w).alias("rn"),
    F.rank().over(w).alias("rank"),
    F.dense_rank().over(w).alias("dense_rank"),
)

rank is competition ranking: the tied pair share 2, and because two tracks hold second place the next one is fourth, the way a podium works. dense_rank also shares the 2 but then carries on to 3, no gap. Ask which is right and the answer is a question back: should a tie push the next row down, or not?

Make the order total

If you need row_number to be reproducible, and a grader does, give the order a tiebreaker, so no two rows ever compare equal:

w = Window.partitionBy("genre").orderBy(F.col("plays").desc(), F.col("title").asc())

Now Afterglow is 2 and Circuit is 3 everywhere, because A comes before C. With a total order all three functions agree; the differences above only exist while there are ties.

Top N per group, and one row per key

Number the rows, then keep the numbers you want:

result = (
    plays.withColumn("rn", F.row_number().over(w))
    .filter(F.col("rn") <= 2)
    .select("genre", "title", "plays")
)

Set the filter to == 1 and this is the pattern the One Table From Many lesson promised: one specific row per key. The latest order per customer is partitionBy("customer_id"), ordered by date descending, rn == 1; the cheapest item per category is the same shape with a different order. dropDuplicates keeps a row of the engine's choosing; row_number keeps the row you chose.

Where the rows have to be

A window partition is a promise that every row of a genre is looked at together, so on a cluster partitionBy is a shuffle, the same movement the Where Your Code Runs lesson showed for groupBy. Call .explain() on the chart and the plan here is a SortExec feeding a BoundedWindowAggExec on one data partition; with a hundred, moving the rows is the cost.

Five problems, in the order they build:

1. Best Seller Per Store — one row per group, with a tiebreaker.
2. Dense Standings — a leaderboard with no gaps.
3. Chart-Toppers — top two per group.
4. Top Two, Ties Included — competition ranking.
5. Latest Order Per Customer — one row per key, then a join.