[SQL Basic] Let's learn some advanced CASE queries!

#19. Marketing/Data Analysis for Beginners

In the 12th lesson, we learned about 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.

#Glossary:

📌Lesson 3: SELECT / FROM / WHERE
📌Lesson 4: ORDER BY
📌Lesson 5: AND / OR / IN
📌Lesson 6: LIKE
📌Lesson 7: AS /DISTINCT / IS NULL
📌Lesson 9: COUNT / SUM
📌Lesson 10: AVG / MAX / MIN
📌Lesson 11: GROUP BY / HAVING
📌Lesson 12: CASE
📌Lesson 13: INNER JOIN
📌Lesson 14: OUTER LEFT JOIN / OUTER RIGHT JOIN
📌Lesson 15: SUBQUERIES
📌Lesson 16: DATE_FORMAT / DATEDIFF
📌Lesson 17: CONCAT/LENGTH/SUBSTR
📌Lesson 18: TRUNCATE/ROUND/MOD

#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

The CASE function 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 the WHEN function is not met, it will follow what is set in the ELSE function.

⭐️ TheELSE clause is optional.

When we used CASE before (lesson 12), 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 the CASE function to calculate the sum spent on rentals per customer and then 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 theFROM function.)

Let’s set some membership ratings based on the total spent per customer:

Total rent paid is below $100: New rank, level 4 (4_New)
Total rent paid is above $100 but below $150: Silver rank, level 3 (3_Silver)
Total rent paid is above $150 but below $200: Gold rank, level 2 (2_Gold)
Total rent paid 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 function first:

SELECT customer_id, total

Set your CASE function to clarify the name of 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 the FROM function 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 function will be inside your SELECT function:

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 the FROM function 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 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 the CASE function with the SELECT function, 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 the FROM function. Your final query will look like this:

SELECT customer_id, active, CASE WHEN active=1 THEN ‘active’ ELSE ‘inactive’ END AS status FROM customer;

And 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 the CASE function 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 the SELECT function.

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 the COUNT function, and use the number 1 to indicate we want the first column counted (which is the CASE function column). One more addition will be the GROUP BY function, 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;

Check out the picture below to see both queries in action:

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.

If you like more visual guidance, check out our video tutorial below:

# Tutorial Video

# [SQL Basic Series] — Marketing/Data Analysis for Beginners

1. What is SQL? — Database Introduction
2. Downloading SQLGate and Connecting to a Database
3. What kind of data is in the ‘film’ table? — SELECT/FROM/WHERE
4. How to sort film lists by price (rental rate)? — ORDER BY
5. Can I rent a 3 hour movie for $0.99? — AND/OR/IN
6. How can I find a movie that’s thrilling? — LIKE
7. How to rename columns and see unique values? — AS/DISTINCT/IS NULL
8. Let’s see Sample Database tables!
9. What is the total length of all the films added together? — COUNT/SUM
10. What is the average film length? — AVG/MIN/MAX
11. How many movies have the same rating? — GROUP BY/HAVING
12. Let’s designate a value based on the rental duration. — CASE
13. Let’s find out the movie genres by joining tables! — INNER JOIN
14. Let’s check movie rentals by the inventory list! — LEFT/RIGHT JOIN
15. Let’s find customers that have rented movies priced $9.99! — Subquery
16. How to work with Date Functions in SQL — DATE_FORMAT, DATEDIFF
17. How to work with String Functions in SQL — CONCAT, LENGTH, SUBSTR
18. Let’s use some Numeric SQL functions — TRUNCATE, ROUND, MOD
19. Let’s learn some advanced CASE queries!
20. Let’s learn some advanced JOIN queries!