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.
Is there another way to get customer names and count of subscriptions - without using WITH. @djkentuckyham
select customers.customer_name, count (orders.subscription_id) as ‘subscriptions’
from customers
join orders
on customers.customer_id = orders.customer_id
group by customers.customer_name;
This part through me off because it one point it says to use “previous results” then the next it saying “previous query” Is that a mistake in the text?
I think previous_results is specific to the example and previous_query is specific to the instructions. It had me confused too. I think the idea that they were trying to convey is that you can use any name as an alias for WITH use_any_name_here AS ().
practicing different combinations of WITH iwrote this:
WITH countorder
AS (SELECT customer_id, COUNT(subscription_id) AS ‘counter’ FROM orders GROUP BY customer_id)
SELECT *
FROM customers
JOIN countorder
ON customers.customer_id = countorder.customer_id;
But in the resulting query I get two customer_id columns can someone explain why?
In the project of “Multiple tables with REBU”, I realized that WITH clause exists only during a single statement. This means I cannot claim it at top of the file then use it later on.
It would be great to mention this in the WITH section. Please correct me if I’m wrong.
Following is the observation from the REBU project. Done in browser.
WITH mergeTable as(
SELECT * FROM table1
UNION
SELECT * FROM table2
)
SELECT * FROM mergeTable; -- this will show me the mergeTable.
WITH mergeTable as(
SELECT * FROM table1
UNION
SELECT * FROM table2
)
SELECT * FROM otherTable
--do something with it
;
SELECT * FROM mergeTable; -- this will NOT show me the mergeTable
Isn’t the purpose of WITH is to save lives and let the developer claim it at top of the file, then use it later on with a peace in mind?
I think these have confused me more because we switch between two data sets with similar data. Newspapers/Online and Orders/Customers are very similar, but for instance the former has first and last names separate, but the latter has the first and last name under the same column.
SELECT customers.customer_name, previous_query.subscriptions
FROM previous_query
As far as I understand customers.customer_name is not part or previous_query and I wonder why we can select it FROM previous_query. How does that work?
Thanks
Remember that the query is better understood as a whole, context is important, so, consider that after the FROM previous_query comes the JOIN customers which is when the SELECT customers.customer_name, previous_query.subscriptions makes sense because you are ‘selecting’ the columns resulting from the two joint tables previous_query & customers.
SELECT customers.customer_name,
previous_query.subscriptions
FROM previous_query
JOIN customers
…
Now, you may be wondering about the order in which you’re stating the SELECT clause on each column, but that will only change the order in which each column is displayed, meaning that under this context, there’s no difference between SELECT customers.customer_name, previous_query.subscriptions & SELECT previous_query.subscriptions, customers.customer_name neither there is a difference in result by doing FROM previous_query JOIN customers or FROM customer JOIN previous_query.
Note, nonetheless, the last example holds true as long as we’re not talking about a LEFT JOIN.
I cannot seem to get the NEXT button to highlight.
I think I have the answer correct below but I cannot move forward. Help?!
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;
I’m wondering why the result of the WITH exercise doesn’t include customers that do not subscribe to any newspaper. I think this query would make more sense to me since it includes those customers.
I understood well the WITH command. My doubt here is that in the “final” SELECT.
The hint says to put customers.customer_id FROM previous_query and then it goes the join, with the other table.
Since the select is from previous_query, shouldn’t it jeopardize/lag the system, since previous_query doesn’t have a customer_id column ? or because of the JOIN clause, I can put anything in the select that when retrieving, FROM will also include the table that is being selected after JOIN?
) below!
) to up-vote the contribution!