Window functions (Rank, DenseRank, Lag over price)

# ======================================================================
# A `Window` keeps every row but computes across a sliding 'window' defined by
# `order_by` and `partition_by`. `Rank` leaves gaps after ties, `DenseRank` does
# not, and `Lag` reads the previous row's value — none of this collapses the
# result set the way an aggregate would.
# ======================================================================

# ----------------------------------------------------------------------
# books ranked by price
# ----------------------------------------------------------------------

# Django:
Book.objects.annotate(
    price_rank=Window(Rank(), order_by=F("price").desc()),
    dense=Window(DenseRank(), order_by=F("price").desc()),
    prev=Window(Lag("price"), order_by=F("price").desc()),
).order_by("-price")

# SQL:
#   SELECT "examples_book"."id", "examples_book"."title", "examples_book"."author_id", "examples_book"."price", "examples_book"."pages", "examples_book"."published", "examples_book"."is_active", "examples_book"."tags", "examples_book"."data", "examples_book"."search", "examples_book"."title_upper", RANK() OVER (ORDER BY "examples_book"."price" DESC) AS "price_rank", DENSE_RANK() OVER (ORDER BY "examples_book"."price" DESC) AS "dense", LAG("examples_book"."price", 1) OVER (ORDER BY "examples_book"."price" DESC) AS "prev"
#   FROM "examples_book"
#   ORDER BY "examples_book"."price" DESC

# Result:
#   rank 1 (dense 1): The Lord of the Rings $29.00  prev=$None
#   rank 2 (dense 2): Harry Potter $19.99  prev=$29.00
#   rank 3 (dense 3): The Hobbit $14.50  prev=$19.99
#   rank 4 (dense 4): Postgres for Authors $0.00  prev=$14.50