A window function calculates a value across rows related to the current row while retaining every row in the output. The OVER clause divides rows into windows, and the function operates within each window. section 3.5
SELECT function_name(arguments) OVER (PARTITION BY columnA) FROM table;
Use PARTITION BY to place rows with the same value into a window, then apply an aggregate such as SUM or AVG, or a built-in function such as RANK or ROW_NUMBER. section 3.5
Keep users while calculating song totals
A grouped aggregate returns one row per song. A window function keeps each song-user row and repeats the song total: 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; The result includes users 1, 2, and 3 for song 1, each with a total duration of 800, and users for song 2, each with 654. section 3.5
Common applications include:
- The broader pattern supports sorting, cumulative sums, moving averages, and lag or lead analysis. section 3.5
