Laptop vs. Mobile Viewership SQL INTREVIEW QUESTION

 /*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".

viewership Table:*/

--------Creating the table with sample data---------------

create table viewership

(

user_id  INT NOT NULL ,

device_type Varchar(10) NOT NULL ,

view_time DATETIME

)

select * from viewership 

insert into viewership values (123, 'tablet','01/02/2022 00:00:00')

insert into viewership values  (125,'tablet', '02/07/2022 00:00:00')

insert into viewership values  (127,'phone', '05/07/2022 00:00:00')

insert into viewership values  (128,'phone', '06/07/2022 00:00:00')

insert into viewership values  (129,'tablet', '07/07/2022 00:00:00')

insert into viewership values  (122,'phone', '08/07/2022 00:00:00')

insert into viewership values  (121,'laptop', '09/07/2022 00:00:00')

insert into viewership values  (132,'laptop', '11/07/2022 00:00:00')

insert into viewership values  (134,'laptop', '21/07/2022 00:00:00')

insert into viewership values  (155,'tablet', '18/07/2022 00:00:00')

SELECT A.[ Type of Devices],COUNT(1) 'Count of Devices'FROM

(

SELECT CASE WHEN device_type='tablet' THEN 'PhoneView' WHEN  device_type='phone' THEN 'PhoneView' ELSE 'LaptopView' END  as ' Type of Devices' from viewership 

) A

GROUP BY A.[ Type of Devices]

------------------END--------------------------------