logo

Back to questions

Laptop vs Mobile Viewership

Easy

This is the same question as problem #3 in the SQL Chapter of Ace the Data Science Interview!

Assume that you are given the table below containing information on viewership by device type (where the three types are laptop, tablet, and phone). Define “mobile” as the sum of tablet and phone viewership numbers. Write a query to compare the viewership on laptops versus mobile devices.

Output the total viewership for laptop and mobile devices in the format of "laptop_views" and "mobile_views".

Table:

Column NameType
user_idinteger
device_typestring ('laptop', 'tablet', 'phone')
view_timetimestamp

Example Input:

user_iddevice_typeview_time
123tablet01/02/2022 00:00:00
125laptop01/07/2022 00:00:00
128laptop02/09/2022 00:00:00
129phone02/09/2022 00:00:00
145tablet02/24/2022 00:00:00

Example Output:

laptop_viewsmobile_views
23

Explanation: Given the example input, there are 2 laptop views and 3 mobile views.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.