Often times, we want to combine two tables, but one of the tables is the result of another calculation.
Let’s return to our magazine order example. Our marketing department might want to know a bit more about our customers. For instance, they might want to know how many magazines each customer subscribes to. We can easily calculate this using our orders
table:
SELECT customer_id, COUNT(subscription_id) AS 'subscriptions' FROM orders GROUP BY customer_id;
This query is good, but a customer_id
isn’t terribly useful for our marketing department, they probably want to know the customer’s name.
We want to be able to join the results of this query with our customers
table, which will tell us the name of each customer. We can do this by using a WITH
clause.
WITH previous_results AS ( SELECT ... ... ... ... ) SELECT * FROM previous_results JOIN customers ON _____ = _____;
- The
WITH
statement allows us to perform a separate query (such as aggregating customer’s subscriptions) previous_results
is the alias that we will use to reference any columns from the query inside of theWITH
clause- We can then go on to do whatever we want with this temporary table (such as join the temporary table with another table)
Essentially, we are putting a whole first query inside the parentheses ()
and giving it a name. After that, we can use this name as if it’s a table and write a new query using the first query.
Instructions
Place the whole query below into a WITH
statement, inside parentheses ()
, and give it name previous_query
:
SELECT customer_id, COUNT(subscription_id) AS 'subscriptions' FROM orders GROUP BY customer_id
Join the temporary table previous_query
with customers
table and select the following columns:
customers.customer_name
previous_query.subscriptions
Check the answer in the hint below.