Back to questions
Laptop vs. Mobile Viewership [New York Times SQL Interview Question]
This is the same question as problem #3 in the SQL Chapter of Ace the Data Science Interview!
Assume you're given the table on user viewership categorised by device type where the three types are laptop, tablet, and phone.
Write a query that calculates the total viewership for laptops and mobile devices where mobile is defined as the sum of tablet and phone viewership. Output the total viewership for laptops as and the total viewership for mobile devices as .
Effective 15 April 2023, the solution has been updated with a more concise and easy-to-understand approach.
Table
| Column Name | Type |
|---|---|
| user_id | integer |
| device_type | string ('laptop', 'tablet', 'phone') |
| view_time | timestamp |
Example Input
| user_id | device_type | view_time |
|---|---|---|
| 123 | tablet | 01/02/2022 00:00:00 |
| 125 | laptop | 01/07/2022 00:00:00 |
| 128 | laptop | 02/09/2022 00:00:00 |
| 129 | phone | 02/09/2022 00:00:00 |
| 145 | tablet | 02/24/2022 00:00:00 |
Example Output
| laptop_views | mobile_views |
|---|---|
| 2 | 3 |
Explanation
Based on the example input, there are a total of 2 laptop views and 3 mobile views.
The dataset you are querying against may have different input & output - this is just an example!
Solution
To calculate the viewership on different devices (laptops vs. mobile devices), we can utilize the aggregate function along with the clause to apply conditional expressions.
In the given example, the device types 'tablet' and 'phone' are considered as 'mobile' devices, while 'laptop' is treated as a separate device type.
The following query can be used to obtain the desired result:
In the first column , calculates the count of rows where the device type is labeled as 'laptop'.
In the second column , counts the number of rows where the device type is a tablet or a phone.
The result would have two columns, and displaying the respective counts of views for each device type.
| laptop_views | mobile_views |
|---|---|
| 2 | 3 |
Solution #2: Using SUM() & CASE statement
PostgreSQL 14