Скачать или смотреть SQL for Data Science Day 46 | NY Times SQL Interview Question | DataLemur Day 5 | PostgreSQL | SQL

Datalemur Day 5 | Laptop vs. Mobile Viewership
NY Times SQL Interview Question Solving in PostgreSQL

In this video, we solve a New York Times SQL interview question from DataLemur that focuses on conditional aggregation and CASE expressions in PostgreSQL.

Problem Statement
You are given a table containing user viewership data categorized by device type.

The device types are:
• Laptop
• Tablet
• Phone

Your task is to calculate:
• Total laptop viewership
• Total mobile viewership, where mobile is defined as the combined viewership of tablet and phone devices

The output should include:
• laptop_views
• mobile_views

Note
Effective April 15, 2023, the solution was updated to provide a more concise and easy-to-understand approach.

Table: viewership
Columns
user_id – integer
device_type – string
view_time – timestamp

Example Input
user_id | device_type | view_time
123 | tablet | 01/02/2022 00:00:00
125 | laptop | 01/07/2022 00:00:00
128 | laptop | 02/09/2022 00:00:00
129 | phone | 02/09/2022 00:00:00
145 | tablet | 02/24/2022 00:00:00

Example Output
laptop_views | mobile_views
2 | 3

Explanation
From the sample data, there are 2 laptop views and 3 mobile views in total.

Note
The dataset used for evaluation may differ from this example. This sample is provided for clarity.

SQL Solution

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;

This query uses conditional aggregation to calculate separate totals for laptop and mobile devices in a single pass.

This problem is commonly asked in analytics and data science SQL interviews and is a great way to practice CASE statements.

Don’t forget to like, share, and subscribe for more daily SQL interview problems.

Hashtags
#SQL #PostgreSQL #DataLemur #NYTimesSQL #SQLInterview #DataScience #LearnSQL #Analytics #SQLPractice #bhuvneshbhatt #bhuvnesh #bhatt