Objective

Write a SQL query to calculate total viewership for laptop devices and mobile devices (tablet plus phone combined).

Table Schema:

• viewership

Task Requirements:

• Calculate total views from laptop devices
• Calculate total views from mobile devices (tablet + phone combined)
• Return aggregated counts for both device categories
  
  

Output columns: laptop_views, mobile_views

SELECT 
    SUM(CASE WHEN device_type = 'laptop' THEN 1 ELSE 0 END) AS laptop_views,
    SUM(CASE WHEN device_type IN ('tablet', 'phone') THEN 1 ELSE 0 END) AS mobile_views
FROM viewership;

Explanation

Step 1: Count Laptop Views

Use SUM(CASE WHEN device_type = 'laptop' THEN 1 ELSE 0 END) to count rows where device_type is 'laptop'.

SUM(CASE WHEN device_type = 'laptop' THEN 1 ELSE 0 END) AS laptop_views

Step 2: Count Mobile Views

Combine 'tablet' and 'phone' into a single mobile count using the IN operator.

SUM(CASE WHEN device_type IN ('tablet', 'phone') THEN 1 ELSE 0 END) AS mobile_views

Step 3: Aggregate Without GROUP BY

No GROUP BY is needed - the aggregate functions process all rows and return a single result row with the total counts.