AlgoMaster Logo

Exercise: CASE WHEN Expressions

Last Updated: May 10, 2026

1 min read

For this exercise, we will work with below data:

SQL

Exercise 1

Easy

Classify each track by duration: 'Short' for under 180 seconds, 'Medium' for 180 through 300 seconds, and 'Long' for over 300 seconds. Show title, duration_seconds, and length_category, ordered by track_id.

SQL

Expected Query

Expected Output

Exercise 2

Medium

Classify users by signup era: 'Early Adopter' for signups before 2022-01-01, 'Growth Phase' for 2022-01-01 through 2023-12-31, and 'Recent' for 2024-01-01 onward. Show username, signup_date, and user_era, ordered by signup_date.

SQL

Expected Query

Expected Output

Exercise 3

Medium

Sort users by subscription tier with this custom priority: family first, then premium, then free. Within the same tier, sort alphabetically by username. Show username and subscription_tier.

SQL

Expected Query

Expected Output

Exercise 4

Medium

Using conditional aggregation, write a single query that returns the total number of payments and a count for each status. Output columns: total, completed, failed, refunded.

SQL

Expected Query

Expected Output

Exercise 5

Hard

Build a user_segment label using nested logic. A user is 'VIP' if they are premium or family AND signed up before 2022-01-01. They are 'Loyal Free' if they are free AND signed up before 2022-01-01. Otherwise, label them by tier: 'Premium', 'Family', or 'Free'. Show username, subscription_tier, signup_date, and user_segment, ordered by user_id.

SQL

Expected Query

Expected Output

Exercise 6

Hard

Sort users so that those with a NULL country appear at the bottom, and within each non-NULL country group, premium users come before family, then free. Within the same country and tier, sort by username. Show username, country, and subscription_tier.

SQL

Expected Query

Expected Output