Manipulation 9/10 With

#1

https://www.codecademy.com/courses/learn-sql-multiple-tables/lessons/multiple-tables/exercises/with?action=resume_content_item&course_redirect=learn-sql

Ahh!.. So close to the end and I don’t know what’s going on!
It’s been going pretty well until the “Learn” explanation seemed a little scant in 8/10. Now at 9/10 I just don’t feel I have enough information. The parentheses were not explained and the graphic is unclear to me. Hanging on to my sense of humor though!

The Instructions say:

SELECT customer_id,
COUNT(subscription_id) as subscriptions
FROM orders
GROUP BY customer_id

Place the above query into a WITH statement using the alias previous_query. Join previous_query with customers and select the following columns:

customers.customer_name
previous_query.subscriptions

Admittedly, I’m already lost, but I just went for it anyway:

SELECT customer_id, COUNT(subscription_id) as subscriptions FROM orders GROUP BY customer_id;

WITH previous_query AS (SELECT customers.customer_name, previous_query.subscriptions FROM previous_query JOIN customers ON previous_query.customer_id = customers.customer.id);

What is wrong with my code?

https://www.codecademy.com/courses/learn-sql-multiple-tables/lessons/multiple-tables/exercises/with?action=resume_content_item&course_redirect=learn-sql

#3

I’m having problems with the same scenario. I could use some more help on the “WITH” syntax.

#4

Your pretty close.

The WITH statmenet should be first i.e. WITH a AS (SELECT ....) SELECT ... This way the results of a can be used in the second select statement.

This article might help you unserstand it a little better.

#5

Had some problems with this excercise, but here’s my completed one for reference:

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 customers.customer_id = previous_query.customer_id;

the issue i had was on the “ON” portion where both data types need to match. customers.customer_id and previous_query.customer_id are both INT. previously, I was using “ON customers.customer_name = previous_query.subscriptions” which doesn’t work.

#6

Thank you for that.

The article is interesting. In particular it uses a lot of vocabulary that I don’t believe has been mentioned here – not thus far anyway. Have we talked about CTE or SubQueries at all? The article just hints at how little I know.

#7

Returning this after too long, so maybe I need some review, but I tried this:

WITH previous_query AS (SELECT customers.customer_name, previous_query.subscriptions FROM previous_query JOIN customers ON previous_query.customer_id = customers.customer.id)

SELECT customer_id, COUNT(subscription_id) as subscriptions FROM orders GROUP BY customer_id;

and got the error: “Write the query using the WITH statement”
Ummm…

Still need more information.
Help?

#8

Correct code is:

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 customers.customer_id = previous_query.customer_id;

Look through it to figure out what went wrong, seems your issue is understanding where to put the joins.

I’d say the quickest way to learn/improve on any programming language is to Google your problem and find a stack overflow answer detailing a fix/solution. You’ll pick up the correct terminology really quickly and chances are there isn’t a single programming question you have that hasn’t already been answered there.

#9

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.