Multiple Windows

Concepts covered: Multiple WINDOWs

Each OVER clause is a window of its own, and a query can hold as many as it needs, partitioned and sorted however each column requires. The engine computes them all over the same input rows, so one SELECT can report how a track moved against yesterday, what share of the day it took, and where it ranked that day, all without a join or a second read of the table. The 3 columns below come from 3 windows that disagree on nearly everything.

SELECT
  track,
  stream_date,
  streams,
  streams - LAG(streams) OVER (
    PARTITION BY track
    ORDER BY stream_date
  ) AS vs_prev_day,
  ROUND(100.0 * streams / SUM(streams) OVER (
    PARTITION BY stream_date
  ), 1) AS share_of_day,
  RANK() OVER (
    PARTITION BY stream_date
    ORDER BY streams DESC
  ) AS day_rank
FROM track_days
ORDER BY
  track,
  stream_date

vs_prev_day partitions by track and orders by date, so Orbit's 2024-03-02 row reads back to its own 800 and reports 200, while Wind's 2024-03-03 row reads 600 and reports 300. share_of_day partitions by date with no ORDER BY, so SUM covers the whole day: 1000 on 2024-03-01, 1600 on 2024-03-02, 1800 on 2024-03-03, and Orbit's 800 is 80.0 percent of the first. day_rank partitions by date and orders by streams DESC; on 2024-03-03 both tracks reach 900 and RANK gives both a 1. The engine computes all 3 from a single read of the 6 rows.

Which OVER clause gives each track_days row its share of that day's total streams?

• OVER (PARTITION BY track), the total of the track across all days
• OVER (PARTITION BY stream_date ORDER BY streams), a running total within the day
• OVER (PARTITION BY stream_date), the total of every track on that day
• OVER (), the total of every row in the table

Filtering on a window result

Window functions run in the SELECT step of the execution order, after WHERE has already chosen its rows, so a WHERE clause cannot mention day_rank: the column does not exist yet when WHERE runs. Wrap the windowed query in a CTE and filter its output in the outer query instead. The query below keeps the leader of each day, and because 2024-03-03 is a tie, that day returns 2 rows.

WITH ranked AS (
  SELECT
    track,
    stream_date,
    streams,
    RANK() OVER (
      PARTITION BY stream_date
      ORDER BY streams DESC
    ) AS day_rank,
    ROUND(100.0 * streams / SUM(streams) OVER (
      PARTITION BY stream_date
    ), 1) AS share_of_day
  FROM track_days
)
SELECT
  stream_date,
  track,
  share_of_day
FROM ranked
WHERE day_rank = 1
ORDER BY stream_date

Build a query over track_days that reports each track's change from its previous day and its rank within each day, then keeps only each day's leader. One tile tries to filter on the rank before the window exists and does not belong.

About this lesson

This section is part of the Window Functions: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.