[SQL Basic] Let’s find customers that have rented movies priced $9.99! — SQL Subquery

SQLGate

29 Sep 2018 • 7 min read

#15. Marketing/Data Analysis for Beginners

#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

Finally! We’ve covered most the basics! And this tutorial will cover everything we learned so far to write some subqueries.

#Table of Contents

Query 1. Find customers that have rented movies priced $9.99.

SELECT customer_id, first_name, last_name, active, email FROM customer WHERE customer_id IN (SELECT customer_id FROM payment WHERE amount=9.99) AND active=1;

Query 2. Find customers in store #1 that spent less than $2.99 on individual rentals, but have spent a total higher than $5.

SELECT customer_id, SUM(amount) FROM (SELECT payment_id, customer_id, amount FROM payment a WHERE a.staff_id=1) sub WHERE sub.amount<2.99 GROUP BY sub.customer_id HAVING SUM(sub.amount)>=5;

#Subquery using `WHERE`

Q1. Find customers that have rented movies priced $9.99 (using a subquery).

Subquery using WHERE clause: SELECT [column1] FROM [table 1] WHERE [name of column you want to join] IN (SELECT [column name2] FROM [table2] WHERE condition2) (AND condition 3~ can be omitted )

We’re going to use the customer and payment tables for this example. Suppose you want to make a list of customers that rent movies with price is $9.99. How could you make it?

Check out the data in the customer and payment tables first to get more familiar with the information presented.

select * from customer;
select * from payment;

For this exercise, we need the columns customer ID, first name, last name, email, and activity status from the customer tale. From the payment table, we only need customer ID, rental ID and amount.

When writing a subquery, we will have an inside query and an outside query. For a subquery to work correctly, we need to first make sure the inside query works on its own. So let’s write and test that first.

We want to check the customers with rentals that are in the higher price bracket, so let’s go into the payment table first. Show the customer ID column and filter the amount.

SELECT customer_id FROM payment WHERE amount=9.99;

This will give you the IDs of all customers that have rented $9.99 movies. Now we need to combine this new table with the customer table, so we can see the name of all the customers instead of just ID numbers. It’s time to write the outside query. Let’s start by stating what columns we want to see in the customer table:

SELECT customer_id, first_name, last_name, active, email FROM customer

Next, we need to plug in the inside query. We’re going to do this using the WHERE and IN queries. After the WHERE query, we will specify which columns we will combine. And using the IN query, we will designate our inside query:

SELECT customer_id, first_name, last_name, active, email FROM customer WHERE customer_id IN (SELECT customer_id FROM payment WHERE amount=9.99);

🔑 Be careful not to write a semicolon (;) for the inside query. You only need 1 at the very end, OUTSIDE the parenthesis.

💡 BONUS TIP:

To clean up your query, you can use SQLGate’s feature by hitting F9. This will reformat your query to look like the one below:

⭐️ Notice how in this table, there are some customers that are not active (the number in the active column is 0). To get more accurate information, you can plug in active = 1 at the end. Make sure to use AND to connect them, like this:

SELECT customer_id, first_name, last_name, active, email FROM customer WHERE customer_id IN (SELECT customer_id FROM payment WHERE amount=9.99) AND active=1;

#Subquery using `FROM`

Q2. Find customers in store #1 that spent less than $2.99 on individual rentals, but have spent a total higher than $5.

This query might look complicated, but we’re going to break it down for you. Let’s first look at payment, store table. Payment table does not contain store number, but store table together shows store 1 if staff_id is 1 and store 2 if staff_id is 2.

Before we start, let’s take another look at the payment table.

SELECT * FROM payment;

We already pointed out that the payment table doesn’t give any details on the customers besides their ID numbers. And as you can see, it doesn’t say anything about the store either. We have two locations where we rent out our imaginary movies, store ID 1 and store ID 2.

So let’s check out the store table:

SELECT * FROM store;

Wow, look at that. The staff ID corresponds to the store ID! So the staff with the number 1 ID are working in the first store. That’s good to know!

Since we want to get the SUM of amount spent, we need to work first in the payment table. Let’s write our inside query first:

SELECT payment_id, customer_id, amount FROM payment;

This will filter which payment was made by which customer. Now using this information, let’s add up the total amount spent by each customer by using a subquery. There are a few steps we need to follow here. First, we need to name the new subquery. We will use the common title sub and write it at the end of the inside query. Next, we need to use a GROUP BY query to organize the table or we will get an error. Your query will look like this:

SELECT customer_id, SUM(amount) FROM (SELECT payment_id, customer_id, amount FROM payment) sub GROUP BY sub.customer_id;

Great! This gives us the total spent by each customer. We’re going to expand on this query to filter the information further. We want to filter the information we have by two important factors: which customer spent less than $2.99 (WHERE amount<2.99) on individual rentals, but has spent a total greater than $5.00 (HAVING SUM(amount)>=5))!

Because we only want to look at customers from the #1 store, we need to use store ID 1. That information is in the customer table… but we’re using the payment table to find the total (SUM). This causes a slight dilemma. Slight because.. we already found a solution! Earlier when we checked the store table, we found that staff ID is relative to the store ID.. and sure enough, we have the staff ID column in the payment table! So we’re going to use that to filter our information (staff_id=1)

🔑 Since we’re using multiple tables, we need to use this format: table.column. And because we named our new subquery table sub, when we reference a column from that new table we would write it as sub.column!

⭐️ Don’t forget, we have to use a GROUP BY to organize the new table!

Your query should look like this:

SELECT customer_id, SUM(amount) FROM (SELECT payment_id, customer_id, amount FROM payment a WHERE a.staff_id=1) sub WHERE sub.amount<2.99 GROUP BY sub.customer_id HAVING SUM(sub.amount)>=5;

PRACTICE TIME

📰 Use JOIN as well as a subquery to check the highest rental amount of customer list (limit 10) with the customer and payment table.

Start by writing your inside query, where you define the highest rental amount of customer:

SELECT customer_id, SUM(amount) AS total FROM payment GROUP BY customer_id ORDER BY total DESC LIMIT 10

And start your outside query by setting the columns you want to see: customer_id, first_name, last_name from a (customer table) and total from b (payment table subquery):

SELECT a.customer_id, a.first_name, a.last_name, b.total

Put them together with INNER JOIN and name the new subquery ‘b’:

SELECT a.customer_id, a.first_name, a.last_name, b.total FROM customer a INNER JOIN (SELECT customer_id, SUM(amount) as total FROM payment GROUP BY customer_id ORDER BY total desc LIMIT 10)b

Clarify which columns will be joined:

ON a.customer_id=b.customer_id

Put it all together:

SELECT a. customer_id, a.first_name, a.last_name, b.total FROM customer a INNER JOIN (SELECT customer_id, SUM(amount) as total FROM payment GROUP BY customer_id ORDER BY total desc LIMIT 10) b ON a.customer_id=b.customer_id;