25. Ranking: `row_number`, `rank`, `dense_rank`, `ntile`

Ranking functions number rows within each partition, in the window's order. All four need an orderBy in the spec.

w = Window.partitionBy("category").orderBy(F.col("unit_price").desc())

orders.dropDuplicates().select(
    "category", "product", "unit_price",
    F.row_number().over(w).alias("row_number"),
    F.rank().over(w).alias("rank"),
    F.dense_rank().over(w).alias("dense_rank"),
).show(10)

How they differ on ties

Say two products tie on price:

• row_number — always distinct: 1, 2, 3, 4… Ties are broken arbitrarily. Use it when you need exactly one row per position (top-N, dedup).
• rank — ties share a rank, then it skips: 1, 1, 3… Like race placings.
• dense_rank — ties share a rank, no gaps: 1, 1, 2… Use it for "distinct price levels".

Choosing the wrong one is a classic bug — row_number when you meant dense_rank gives two "3rd place" rows different numbers.

ntile — split into buckets

ntile(n) divides each partition into n roughly equal buckets — handy for quartiles, deciles, percentile bands:

w = Window.orderBy(F.col("unit_price").desc())
orders.dropDuplicates().select(
    "product", "unit_price", F.ntile(4).over(w).alias("price_quartile")
).show(8)

Your turn

Rank products within each category by unit_price, highest first, using row_number (alias rn). Return category, product, unit_price, rn for deduplicated orders. Assign to result.

w = Window.partitionBy("category").orderBy(F.col("unit_price").desc())
result = orders  # <- dropDuplicates, add row_number().over(w) AS rn, select the 4 columns
result.show()

w = Window.partitionBy("category").orderBy(F.col("unit_price").desc())
result = orders.dropDuplicates().select(
    "category", "product", "unit_price",
    F.row_number().over(w).alias("rn"),
)