FAQ: Multiple Tables - With


#1

This community-built FAQ covers the “With” exercise from the lesson “Multiple Tables”.

Paths and Courses
This exercise can be found in the following Codecademy content:

Web Development
Data Science

Learn SQL

FAQs on the exercise With

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!


#2

I’m having a hard time understanding this. I’ve forgotten what some of the commands do. Can someone go through line-by-line and explain the answer?


#3

Hi,

Here is my best attempt at making the query/exercise easier to understand.

  • I put the query challenge aside for a min and consider the request in plain English.
    • The marketing department wants to know more about customers. Specifically, they want to know how many magazines each customer subscribes to AND they their name.
  • In the database there is a table called ‘orders’ and ‘customers’. We need to bring them together to get the info needed for marketing.
    • First a query is run against ‘orders’ to determine how many subscriptions each customer_id has. We get the number of subscriptions per customer id. below is the ‘orders’ query:
SELECT customer_id,

COUNT(subscription_id) AS 'subscriptions'

FROM orders

GROUP BY customer_id;

Though we have the complete number subscriptions to customers, we only have the customer id’s, not names. Marketing wants names.

Using WITH we can store the results of the above query as an alias that can be used with another query to obtain the customer names. To do this we take the above (from orders) and nest it within a WITH clause and assign its result to an alias called ‘previous_query’

WITH previous_query’ AS (SELECT customer_id,

COUNT(subscription_id) AS 'subscriptions'

FROM orders

GROUP BY customer_id)

With above we now have subscriptions per customer id stuffed into an alias called ‘previous_query’.

Next we move onto getting the customer names. To do this we perform a JOIN on ‘customers’ table. To do this we use FROM to source our alias result for customer_id and then JOIN it with ‘customers’ table to get customer names.

WITH previous_query AS (SELECT customer_id,

COUNT(subscription_id) AS 'subscriptions'

FROM orders

GROUP BY customer_id)

SELECT customers.customer_name,

previous_query.subscriptions

FROM previous_query

JOIN customers

ON previous_query.customer_id = customers.customer_id;

In order to get the specific customer names we JOINED ON alias ‘previous_query.subscriptions’ and ‘customers.customer_name’. Doing so provides a name match result for each customer id.

I know it’s a bit much, but I recommend reading the exercise and queries a few times to at least get the general idea.

Hope this helped

Robert


#4

Thanks for your detailed explanation. It has helped me to understand the task better.