logo

SQL CASE Tutorial With Examples

The statement in SQL allows you to shape, transform, and manipulate data based on specified conditions. It's a powerful tool that lets you customize query results, create new categories, and apply conditional logic.

Case Statement Example


The statement goes through the specified conditions one by one and returns a value when it encounters the first true condition. If no condition is met, the clause is used to provide a default value.

In this tutorial, we'll explore different ways of using the statement with the dataset. This dataset contains information about Marvel Avengers actors and their associated social media presence.

Example:

actorcharactersuperhero_aliasplatformfollowerspostsengagement_rateavg_likesavg_comments
Robert Downey Jr.Tony StarkIron ManInstagram5000002008.2012000800
Chris EvansSteve RogersCaptain AmericaTwitter3000001506.508000500
Scarlett JohanssonNatasha RomanoffBlack WidowInstagram7000003007.80150001000
Chris HemsworthThorThorYouTube4000001009.10200001200
Mark RuffaloBruce BannerHulkTwitter200000805.306000400

Assigning Categories with CASE Statement Example

Here's an example CASE SQL query that determines the popularity category of each Marvel character based on their number of followers ("Popular" is if they have 500,000 or more followers).


Here's the output:

charactersuperhero_aliasplatformpopularity_category
Tony StarkIron ManInstagramPopular
Steve RogersCaptain AmericaTwitterNULL
Natasha RomanoffBlack WidowInstagramPopular
ThorThorYouTubeNULL
Bruce BannerHulkTwitterNULL

Handling Multiple Conditions with CASE Statement

We can create groups using multiple conditions in the statement. For example, we can categorize the Marvel actors based on their social media engagement rates as follows:

  • High Engagement: Engagement rate greater than or equal to 8.0
  • Moderate Engagement: Engagement rate between 6.0 and 7.9 (inclusive)
  • Low Engagement: Engagement rate less than 6.0

Result

actorcharacterengagement_category
Robert Downey Jr.Tony StarkHigh Engagement
Scarlett JohanssonNatasha RomanoffModerate Engagement
Jeremy RennerClint BartonModerate Engagement
Tom HiddlestonLokiHigh Engagement
Zoe SaldanaGamoraLow Engagement

Using ELSE Clause with CASE Statement

When none of the previous conditions match, we can use the clause to provide a default value.

For the Marvel Avengers characters, let's classify their into three categories based on their number of followers:

  • For characters with 700,000 or more followers, label them as "Highly Popular."
  • For characters with followers between 300,000 and 699,999, label them as "Moderately Popular."
  • For characters with fewer than 300,000 followers, label them as "Less Popular."

Result

charactersuperhero_aliasplatformpopularity_category
Tony StarkIron ManInstagramModerately Popular
Steve RogersCaptain AmericaTwitterModerately Popular
Natasha RomanoffBlack WidowInstagramHighly Popular
ThorThorYouTubeModerately Popular
Bruce BannerHulkTwitterLess Popular

CASE WHEN ELSE Practice Exercise

Now, it's your turn to practice writing a SQL query in an exercise which uses and statements.

Analyze the marvel actors dataset, and categorize them based on the average number of likes:

  • "Super Likes" for characters with an average of 15,000 or more likes.
  • "Good Likes" for characters with an average between 5,000 and 14,999 likes (inclusive).
  • "Low Likes" for characters with an average of fewer than 5,000 likes.

NYT SQL Interview Question: Laptop vs. Mobile Viewership

Let's use the CASE statement, along with , to solve a real SQL Interview Question from NYTimes digital team. In this SQL assesment, your given a table on user viewership categorized by device type where the three types are laptop, tablet, and phone.

Example Input:

user_iddevice_typeview_time
123tablet01/02/2022 00:00:00
125laptop01/07/2022 00:00:00
128laptop02/09/2022 00:00:00
129phone02/09/2022 00:00:00
145tablet02/24/2022 00:00:00

Write a query that calculates the total viewership for laptops and mobile devices where mobile is defined as the sum of tablet and phone viewership. Output the total viewership for laptops as and the total viewership for mobile devices as .

Example Output:

laptop_viewsmobile_views
23

What's Next: JOINS

Up until now, we've focused on analyzing a single table of data at a time. Whether it be the , , or datasets, we've only been applying all these fancy arithmetic operators, hunting down nulls, and applying to data that come from a single table.

It's time to change it up, and start analyzing data from TWO tables using the keyword!

Next Tutorial: SQL Joins


Next Lesson

SQL JOINS 🀝