An OVER clause defines the rows, partitions, ordering, and frames used by a SQL window function. It lets calculations such as SUM(), RANK(), and ROW_NUMBER() operate across related rows while preserving each row in the result. section 3.5
Use the clause to control how a window function sees data:
- PARTITION BY divides rows into independent windows, such as one window per song. section 3.5
- ORDER BY establishes calculation order, enabling running totals and ordered ranking. section 3.5
- Unlike GROUP BY, a window calculation adds results without collapsing each group to one row. section 3.5
SELECT function_name(arguments) OVER (PARTITION BY columnA) FROM table;
Start with the minimal form: apply a function over partitions formed by matching column values. section 3.5
Total duration for every listener
WITH plays_with_total AS ( SELECT song_id, user_id, SUM(play_duration) OVER (PARTITION BY song_id) AS total_duration FROM streaming.plays ) SELECT DISTINCT song_id, user_id, total_duration FROM plays_with_total ORDER BY song_id, user_id; For each listener row, total_duration repeats the song-wide total. Add ORDER BY user_id inside OVER to produce a running total instead. section 3.5
