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 name: viewership
Solution:
select sum(case when device_type='laptop' then 1 else 0 end) as laptop_views,sum(case when device_type = 'tablet' or device_type='phone' then 1 else 0 end) as mobile_views from viewership
Output:
SQL Script:
CREATE TABLE [dbo].[viewership](
[user_id]
[int] NULL,
[device_type]
[nvarchar](50) NULL,
[view_time]
[date] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[viewership] ([user_id], [device_type], [view_time]) VALUES (123, N'tablet', CAST(N'2022-02-01' AS Date))
GO
INSERT [dbo].[viewership] ([user_id], [device_type], [view_time]) VALUES (125, N'laptop', CAST(N'2022-07-01' AS Date))
GO
INSERT [dbo].[viewership] ([user_id], [device_type], [view_time]) VALUES (128, N'laptop', CAST(N'2022-09-02' AS Date))
GO
INSERT [dbo].[viewership] ([user_id], [device_type], [view_time]) VALUES (129, N'phone', CAST(N'2022-09-02' AS Date))
GO
INSERT [dbo].[viewership] ([user_id], [device_type], [view_time]) VALUES (145, N'tablet', CAST(N'2022-02-24' AS Date))
GO