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:

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:

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

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

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:

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:

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