Write SQL and Python, run instantly in your browser, and track your progress.
You are a Product Analyst at Prime Video. The Streaming Quality team wants to understand viewing patterns across different device types and streaming quality levels. Merge playback sessions with devices, then create a pivot table showing both session count and total watch time (in hours) by device type and streaming quality.
| Column Name | Type |
|---|---|
| session_id | int64 |
| device_id | int64 |
| watch_seconds | int64 |
| quality |
You are a Product Analyst at Prime Video. The Streaming Quality team wants to understand viewing patterns across different device types and streaming quality levels. Merge playback sessions with devices, then create a pivot table showing both session count and total watch time (in hours) by device type and streaming quality.
| Column Name | Type |
|---|---|
| session_id | int64 |
| device_id | int64 |
| watch_seconds | int64 |
| quality |
| object |
| object |
| status | object |
| status | object |
| Column Name | Type |
|---|---|
| device_id | int64 |
| device_type | object |
| Column Name | Type |
|---|---|
| device_id | int64 |
| device_type | object |
| session_id | device_id | watch_seconds | quality | status |
|---|---|---|---|---|
| 1 | 101 | 3600 | hd | completed |
| 2 | 102 | 1800 | 4k | completed |
| session_id | device_id | watch_seconds | quality | status |
|---|---|---|---|---|
| 1 | 101 | 3600 | hd | completed |
| 2 | 102 | 1800 | 4k | completed |
| device_id | device_type |
|---|---|
| 101 | tv |
| 102 | tv |
| 103 | mobile |
| 104 | tablet |
| device_id | device_type |
|---|---|
| 101 | tv |
| 102 | tv |
| 103 | mobile |
| 104 | tablet |
| device_type | metric | 4k | hd | sd | uhd |
|---|---|---|---|---|---|
| console | count | 41 | 22 | 20 | 24 |
| console | watch_hours | 33.70 | 22.02 | 14.34 | 19.61 |
| mobile | count |
| device_type | metric | 4k | hd | sd | uhd |
|---|---|---|---|---|---|
| console | count | 41 | 22 | 20 | 24 |
| console | watch_hours | 33.70 | 22.02 | 14.34 | 19.61 |
| mobile | count |
Showing first 5 of 10 rows.
Showing first 5 of 10 rows.
1. DataFrames:
2. Merging:
3. Reshaping:
4. Output:
1. DataFrames:
2. Merging:
3. Reshaping:
4. Output:
| 3 |
| 3 |
| 101 |
| 101 |
| 5400 |
| 5400 |
| hd |
| hd |
| completed |
| completed |
| 4 | 103 | 2700 | sd | abandoned |
| 4 | 103 | 2700 | sd | abandoned |
| 5 | 102 | 7200 | 4k | completed |
| 5 | 102 | 7200 | 4k | completed |
| 6 | 104 | 1200 | hd | completed |
| 6 | 104 | 1200 | hd | completed |
| 7 | 103 | 3000 | sd | completed |
| 7 | 103 | 3000 | sd | completed |
| 15 |
| 15 |
| 24 |
| 24 |
| 29 |
| 29 |
| 18 |
| 18 |
| mobile | watch_hours | 12.18 | 25.21 | 30.34 | 17.84 |
| mobile | watch_hours | 12.18 | 25.21 | 30.34 | 17.84 |
| tablet | count | 32 | 33 | 28 | 37 |
| tablet | count | 32 | 33 | 28 | 37 |