A PARTITION BY clause divides rows processed by a window function into independent groups. The function calculates separately within each group while preserving rows for the query result. section 3.5
Total play duration per song
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; Each user row retains its identity while receiving the total for its song: song 1 totals 800 seconds, and song 2 totals 654 seconds.
Adding an order within each partition
Adding ORDER BY user_id divides each song partition into ordered frames. For song 2, cumulative totals progress from 144 to 350 to 654 seconds.
SELECT function_name(arguments) OVER (PARTITION BY columnA) FROM table;
Use the OVER clause with PARTITION BY to apply a window function independently to each group. section 3.5
