Modules

The SQL CASE statement is super powerful for handling different scenarios in your data. It's kind of like the IF/THEN logic you might use in Excel or programming. You can think of it as a way to set up conditions and actions in your query.

Let's say you're dealing with the user_posts table from Instagram. This table includes details like post_id, user_id, likes_count, and post_type (like 'photo', 'video', or 'reel').

Sample of the user_posts table:

Classifying Popular Posts

Suppose you want to categorize posts based on their popularity:

SELECT post_id,
       likes_count,
       CASE WHEN likes_count > 1000 THEN 'Highly Popular'
            ELSE 'Less Popular' END AS popularity_category
  FROM instagram.user_posts;

Here's what's happening:

• The CASE statement checks if each post has more than 1000 likes.
• If true, it labels the post as 'Highly Popular'.
• Otherwise, it's labeled as 'Less Popular'.

Handling Multiple Conditions:

SELECT post_id,
       post_type,
       CASE WHEN post_type = 'photo' THEN 'Picture Post'
            WHEN post_type = 'video' THEN 'Video Post'
            ELSE 'Other' END AS post_category
  FROM instagram.user_posts;

This sorts posts into 'Picture Post', 'Video Post', or 'Other', based on their type.

Combining Conditions:

SELECT post_id,
       likes_count,
       post_type,
       CASE WHEN likes_count > 500 AND post_type = 'photo' THEN 'Popular Photo'
            WHEN likes_count > 500 AND post_type = 'video' THEN 'Popular Video'
            ELSE 'Standard Post' END AS post_status
  FROM instagram.user_posts;

Here, we're classifying posts as 'Popular Photo' or 'Popular Video' if they have over 500 likes and are a specific type. Otherwise, they're just 'Standard Posts'.

Why Use CASE?

The CASE statement is versatile and powerful. It helps you create dynamic, conditional logic in your queries. For example, if you're analyzing social media data, you can categorize posts, users, or interactions based on specific criteria, helping you draw meaningful insights from your data. Whether you're identifying trends, segmenting data, or preparing reports, CASE is the statement to use!