Window Functions in Spark SQL

Window functions work as in PostgreSQL 1,289 and DuckDB 61,228 (LAMP Stack Development covers the basics, Extended Window Frames the frames), including named WINDOW clauses and, since Spark 4.2 129 , QUALIFY. The listing finds each 2026 month's leading genre with its change on the month and a year-to-date total.

Monthly leaders with LAG, a running total and QUALIFY

spark.sql("""
WITH monthly AS (
  SELECT date_trunc('MONTH', l.order_ts) AS month, b.genre,
         sum(l.qty * l.unit_price) AS revenue
  FROM lines l JOIN books b ON l.book_id = b.id
  WHERE l.status = 'delivered'
  GROUP BY 1, 2)
SELECT date_format(month, 'yyyy-MM') AS ym, genre, revenue,
       round(100 * (revenue / lag(revenue) OVER by_genre - 1), 1) AS vs_prev_pct,
       sum(revenue) OVER (PARTITION BY genre, year(month) ORDER BY month) AS genre_ytd
FROM monthly
WINDOW by_genre AS (PARTITION BY genre ORDER BY month)
QUALIFY rank() OVER (PARTITION BY month ORDER BY revenue DESC) = 1
    AND month >= DATE'2026-01-01'
ORDER BY ym""").show()

Output

+-------+----------+---------+-----------+----------+
|     ym|     genre|  revenue|vs_prev_pct| genre_ytd|
+-------+----------+---------+-----------+----------+
|2026-01|Technology|475659.00|      -10.4| 475659.00|
|2026-02|Technology|427311.00|      -10.2| 902970.00|
|2026-03|Technology|476686.00|       11.6|1379656.00|
|2026-04|Technology|456343.50|       -4.3|1835999.50|
|2026-05|Technology|468470.00|        2.7|2304469.50|
|2026-06|Technology|374736.50|      -20.0|2679206.00|
+-------+----------+---------+-----------+----------+

The date filter sits in QUALIFY, after the windows, so January's lag can still see December 2025. Spark shuffles rows by the PARTITION BY keys and sorts each partition, so those keys behave like join keys, skew included. A window with no PARTITION BY moves every row into one task, and Spark logs No Partition Defined for Window operation! Moving all data to a single partition. Aggregate first, as monthly does: the window then sorts 96 rows, not 1.38 million.

MENU