Write SQL and Python, run instantly in your browser, and track your progress.
You are a Data Scientist at Prime Video. The Personalization team wants to track each user's recent viewing behavior. Calculate a 2-session rolling average of watch_seconds for each user separately, ordered by session_id within each user.
| Column Name | Type |
|---|---|
| session_id | int64 |
| user_id | int64 |
| watch_seconds | int64 |
| quality | object |
You are a Data Scientist at Prime Video. The Personalization team wants to track each user's recent viewing behavior. Calculate a 2-session rolling average of watch_seconds for each user separately, ordered by session_id within each user.
| Column Name | Type |
|---|---|
| session_id | int64 |
| user_id | int64 |
| watch_seconds | int64 |
| quality | object |
| session_id | user_id | watch_seconds | quality |
|---|---|---|---|
| 1 | 101 | 1800 | hd |
| 2 | 102 | 3600 | 4k |
| 3 | 101 | 2400 |
| session_id | user_id | watch_seconds | quality |
|---|---|---|---|
| 1 | 101 | 1800 | hd |
| 2 | 102 | 3600 | 4k |
| 3 | 101 | 2400 |
| session_id | user_id | watch_seconds | user_rolling_avg |
|---|---|---|---|
| 1 | 1 | 2700 | NaN |
| 2 | 1 | 678 | 1689 |
| 3 | 1 | 2460 | 1569 |
| 4 | 1 | 3626 | 3043 |
| session_id | user_id | watch_seconds | user_rolling_avg |
|---|---|---|---|
| 1 | 1 | 2700 | NaN |
| 2 | 1 | 678 | 1689 |
| 3 | 1 | 2460 | 1569 |
| 4 | 1 | 3626 | 3043 |
Showing first 5 of 497 rows.
Showing first 5 of 497 rows.
1. Data Selection:
2. Window Function:
3. Output:
1. Data Selection:
2. Window Function:
3. Output:
| hd |
| hd |
| 4 | 102 | 1800 | hd |
| 4 | 102 | 1800 | hd |
| 5 | 101 | 3000 | 4k |
| 5 | 101 | 3000 | 4k |
| 6 | 102 | 2700 | hd |
| 6 | 102 | 2700 | hd |
| 5 | 1 | 293 | 1959.50 |
| 5 | 1 | 293 | 1959.50 |