📱💻 One Person, Every Screen.
At Disney+ Hotstar's product analytics team, users who access content on three or more different device types are considered deeply embedded in the platform — they are the least likely to churn.
Find all users who have streamed content on 3 or more distinct device types. Return user_id, name, device_count, and the list of devices as a comma-separated string (device_list). Order by device_count descending. Within the device_list, list devices in alphabetical order.
Use GROUP_CONCAT(DISTINCT device_type) for the device list in SQLite.
users Table:
| Column | Type |
|---|---|
| user_id | integer |
| name | text |
streaming_sessions Table:
| Column | Type |
|---|---|
| session_id | integer |
| user_id | integer |
| device_type | text |
| watched_on | text |
Expected Outcome (Illustrative Sample):
| user_id | name | device_count | device_list |
|---|---|---|---|
| 1 | Arjun Bose | 4 | desktop,mobile,smart_tv,tablet |