Back to questions
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.
|device_type||string ('laptop', 'tablet', 'phone')|
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!
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.
Solution #2: Using SUM() & CASE statement