Laptop vs Mobile Viewership — New York Times Asked SQL Question

  • Post last modified:September 10, 2022
  • Reading time:2 mins read
Photo by Caspar Camille Rubin on Unsplash

Introduction

  • In this article we will solve Laptop vs Mobile Viewership SQL question which is asked by New York Times as per DataLemur website.

If you don’t know DatLemur then please do visit , it’s one of the best website to practice SQL questions and improve your SQL skills.

Question

  • We have been given viewership as input table , our goal is to find total_number views by device (mobile and laptop).
  • Fyi, Mobile devices are combined of tablet and phone.

Solution

  • we can basically filter input viewership table based on condition such as laptop or phone or tablet.
  • Best way to achieve this using case when in SQL, and count each case using count() aggregation .
select 
  count(case when device_type = 'laptop' then device_type end) as laptop_views,
  count(case when device_type = 'tablet' or device_type = 'phone' then device_type end) as mobile_views
from viewership
  • If we execute above sql query we will get our output, its that simple!

Submission

  • Our solution is accepted when we submit it on the platform.

Conclusion

  • In this article we solved very easy SQL problem that was asked by NYT. It’s good to understand how we can execute if else in sql . basically SQL provides Case when approach to filter conditional logic.

Leave a Reply