We learned covered the basic use of CASE. In this post, we will discuss advanced queries that use CASE to calculate based on values that meet certain criteria.
#Table of Contents
Query 1. Let’s create customer membership ranks based on total movies rented per customer, and count the number of customers in each rank.
Query 2. In the customer table, indicate whether the customer is active or inactive. Then count the number of active and inactive customers.
Query 3. In the film table, mark the rental period in 3 categories (SHORT/MEDIUM/LONG) according to the value in the rental_duration column. Then count the number of movies in each category.
Before we dive into a long CASE example, let’s review the function.
CASE can be more useful when used with a combination of multiple queries or functions. We can use WHEN-THEN with CASE to create various conditions and generate values that are not NULL1 or NULL based on the results.
Here is a quick review of the basics of CASE:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END AS newColumnName
CASE returns a specified result value from the first WHEN to THEN when the condition is met (like a IF-THEN-ELSE statement). If the criteria defined in WHEN is not met, it will follow what is set in ELSE.
⭐️ TheELSE clause is optional.
When we used CASE before, we specified a value that matched the criteria. This time, we will count the number of rows in each group according to the specified result values. If that sounds confusing, it’s okay! 👍 We will break it down for you.
First, let’s look at the payment table.
SELECT * FROM payment;
Here are the columns we can see in the Payment table:
- Payment ID number (payment_id)
- Customer ID number (customer_id)
- Staff ID number (staff_id)
- Rental ID number (rental_id)
- Movie rental price (amount)
- The day the payment was made (payment_date)
- The last update of this information (last_update)
The table here lists each payment related to a movie rental, but contains multiple payment IDs for each customer ID. So we need to calculate the total sum of rent paid by one customer first.
Q1. Let’s create customer membership ranks based on total movies rented per customer, and count the number of customers in each rank.
Most shops operate a membership system to provide benefits to customers. Usually when creating memberships, the rank is based on the number of purchases or the amount spent. We’re going to use that method to create ranks in our example.
We will use a subquery and CASE to calculate the sum spent on rentals per customer. Then we'll create a query which assigns that customer a membership rank (New/Silver/Gold/VIP) based on certain criteria.
First, here is the query that calculates the total rent paid per customer (rename the column as ‘total’):
SELECT customer_id, SUM(amount) AS total FROM payment GROUP BY customer_id;
Now we have the total amount of rent paid per ‘customer_id’ calculated. (This query will now be used as a subquery in FROM)
Let’s set some membership ratings based on the total spent per customer:
Total spent is below $100: New rank, level 4 (4_New)
Total spent is above $100 but below $150: Silver rank, level 3 (3_Silver)
Total spent is above $150 but below $200: Gold rank, level 2 (2_Gold)
Total spent is above $200: VIP rank, level 1 (1_VIP)
Now let’s use these ranks along with the query we ran earlier to create a new column for our customers. Remember, we’re going to use the previous query as a subquery.
Clarify your SELECT first:
SELECT customer_id, total
Set your CASE to name the ranks:
CASE WHEN total < 100 THEN ‘4_NEW’ WHEN total >= 100 AND total < 150 THEN ‘3_SILVER’ WHEN total >= 150 AND total < 200 THEN ‘2_GOLD’ WHEN total >= 200 THEN ‘1_VIP’ END AS membership
Plug in your subquery into FROM and rename the column as sub:
FROM (SELECT customer_id, SUM(amount) AS total FROM payment GROUP BY customer_id) sub
Your final query should look like this:
SELECT customer_id, total, CASE WHEN total < 100 THEN ‘4_NEW’ WHEN total >= 100 AND total < 150 THEN ‘3_SILVER’ WHEN total >= 150 AND total < 200 THEN ‘2_GOLD’ WHEN total >= 200 THEN ‘1_VIP’ END AS membership FROM (SELECT customer_id, SUM(amount) AS total FROM payment GROUP BY customer_id) sub;Great! Now we can see the total paid in rental fees as well as the membership rank. For example, customer ID #1 has paid a total of $119.68 for movie rentals and is in the Silver membership rank.
Let’s now calculate the number of customers in each class.
This time, your CASE will be inside your SELECT :
SELECT CASE WHEN total_amount < 100 THEN ‘4_NEW’ WHEN total_amount >= 100 AND total_amount < 150 THEN ‘3_SILVER’ WHEN total_amount >= 150 AND total_amount < 200 THEN ‘2_GOLD’ WHEN total_amount >= 200 THEN ‘1_VIP’ END AS membership
Then use COUNT to count the members. We’ll use a number here instead of writing the column name. Since CASE is the first column, we can use 1:
COUNT(1) AS count
Plug in your subquery into FROM again:
FROM (SELECT customer_id, SUM(amount) AS total_amount FROM payment GROUP BY customer_id) sub
Organize the results a second time with a GROUP BY outside the subquery:
GROUP BY membership;
Your full query will look like this:
SELECT CASE WHEN total_amount < 100 THEN ‘4_NEW’ WHEN total_amount >= 100 AND total_amount < 150 THEN ‘3_SILVER’ WHEN total_amount >= 150 AND total_amount < 200 THEN ‘2_GOLD’ WHEN total_amount >= 200 THEN ‘1_VIP’ END AS membership, COUNT(1) AS count FROM (SELECT customer_id, SUM(amount) AS total_amount FROM payment GROUP BY customer_id) sub GROUP BY membership;
As you can see, there are 2 VIP members, 44 Gold members, 349 Silver members and 204 New members. Now let’s use the same query and expand on it further to count the number of groups.
Q2. In the customer table, indicate whether the customer is active or inactive. Then count the number of active and inactive customers.
We are going to use CASE with SELECT, and specify that if their active number is 1, they will be labeled as ‘active’. If the number is 0, they are ‘inactive’.
Start with SELECT and the two columns we want to view:
SELECT customer_id, active
Next use WHEN-THEN to label active customers inside CASE :
CASE WHEN active=1 THEN ‘active’ ELSE ‘inactive’ END AS status
Close it with FROM. Your final query will look like this:
SELECT customer_id, active, CASE WHEN active=1 THEN ‘active’ ELSE ‘inactive’ END AS status FROM customer;
We’re also going to count how many active customers we have, and how many inactive customers we have. This can be done using CASE as well.
SELECT CASE WHEN active=1 THEN ‘active’ ELSE ‘inactive’ END AS status, COUNT(1) AS count FROM customer GROUP BY status;
Check out the picture below to see both queries in action:
⭐️Use GROUP BY status in the second query to organize the results!
Looks like we have 584 active customers and 15 inactive customers!
Q3. In the film table, mark the rental period in 3 categories (SHORT/MEDIUM/LONG) according to the value in the rental_duration column. Then count the number of movies in each category.
We’re going to try a similar query here. This time, instead of ranking the members, we will label the duration of movie rentals.
The first query will be used to categorize the rental duration:
If rental duration is equal to or less than 3 days, then name it SHORT
If rental duration is more than 3 days but less than 6, then name it MEDIUM
If rental duration is equal to or more than 6 days, then name it LONG
In SQL, these sentences look like this:
WHEN rental_duration <=3 THEN ‘SHORT’ WHEN rental_duration > 3 AND rental_duration < 6 THEN ‘MEDIUM’ WHEN rental_duration >=6 THEN ‘LONG’
Make sure to add that if the duration doesn’t match what we’ve specified, it should be NULL(ELSE NULL) and close CASE with END:
ELSE NULL END AS Duration
Your final query will look like this:
SELECT title, rental_duration, CASE WHEN rental_duration <=3 THEN ‘SHORT’ WHEN rental_duration > 3 AND rental_duration < 6 THEN ‘MEDIUM’ WHEN rental_duration >=6 THEN ‘LONG’ ELSE NULL END AS Duration, FROM film;
❗You can add to this query to count every film in each category.❗
Since we only want to view the three categories we’re creating, we can delete title, rental_duration and just leave CASE after SELECT.
SELECT CASE WHEN rental_duration <=3 THEN ‘SHORT’ WHEN rental_duration > 3 AND rental_duration < 6 THEN ‘MEDIUM’ WHEN rental_duration >=6 THEN ‘LONG’ ELSE NULL END AS Duration
Then we will add COUNT, and use the number 1 to indicate we want the first column counted (which is the CASE column). One more addition will be GROUP BY, which we will use to organize the results by the duration length.
COUNT(1) AS Count FROM film GROUP BY duration;
Your final query should look like this:
SELECT CASE WHEN rental_duration <=3 THEN ‘SHORT’ WHEN rental_duration > 3 AND rental_duration < 6 THEN ‘MEDIUM’ WHEN rental_duration >=6 THEN ‘LONG’ ELSE NULL END AS Duration, COUNT(1) AS Count FROM film GROUP BY duration;
From the second query results, we can see that we have 403 movies in the LONG category, 394 movies in the MEDIUM category and 203 movies in the SHORT category. Great job!