Exercise: Window Functions

For each stream, return stream_id, user_id, duration_listened, and the total duration_listened across all streams as total_duration. Use a window function, not a subquery. Order by stream_id.

For each stream by user_id 1, return stream_id, duration_listened, and that user's total listening time as user_total. Order by stream_id.

For each completed payment by user_id 4, return payment_id, payment_date, amount, and a running total of amount ordered by payment_date as running_total. Order by payment_date.

Write a window-function query that returns every stream row with an extra column country_streams showing the total number of streams from that stream's country. Use OVER (PARTITION BY country). Show stream_id, user_id, country, and country_streams. Order by country (NULLs last), then stream_id.

For each stream by user_id 2, return stream_id, started_at, duration_listened, and the running total of duration_listened ordered by started_at as running_total. Order by started_at.

Find every completed payment whose user has a total completed-payment amount greater than 25.00. Return payment_id, user_id, amount, and user_total. Order by payment_id.

Compare partition-wide totals to running totals using the same data. For each stream by user_id 1, return stream_id, started_at, duration_listened, the user's total listening time as user_total, and the running total of duration_listened ordered by started_at as running_total. Order by started_at.