Way too fast (Multiple Tables 9/10)


#1

I didn’t have any trouble with this course up until the late excersises in Multiple Tables. I think you move way too fast here.

Could someone please explain step by step what’s going on in this code, especially the last part:

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;


#2

Hi @borgelin,

Disclaimer: I have not performed this lesson, so if i cover anything you already know, please excuse that. :slight_smile:

This is what is known as a CTE (Common Table Expression).
Although it is not, for simplicity think of it as a type of reversed derived table: In the sense that you are pulling in data from a result set you made on the fly, but instead of it being created after the select it is created before. I understand the order by which SQL engine builds queries, I am simply referring to how it is read not how it is built :slight_smile:

With that in mind. CTE’s start by using the WITH statement, and then the alias name you want to use to reference the data this result set will make:

WITH someAliasName AS ( 
   --Put the result set you want to query against in here, between parens
)

As the comment above then calls out, you define a result set by writing a SQL statement of sorts in between the parens. It should be noted you can define more than one, but that is beyond the scope of this reply :slight_smile:

SELECT customer_id,
COUNT(subscription_id) AS ‘subscriptions’
FROM orders
GROUP BY customer_id

So, at this moment, we created a table, that is giving us back, a COUNT of subscriptions, for each Customer’s ID.

Now, the thing is although we’ve defined, for want of a better term, the data set, we haven’t actually accessed it yet, and for CTE’s that’s where we SELECT from, after the data set is built. Think of it as if you’re saying, from what I have created, I now want to SELECT this and this.

WITH someAliasName AS (
   SELECT customer_id,
   COUNT(subscription_id) AS ‘subscriptions’
   FROM orders
   GROUP BY customer_id
)
SELECT theFieldsIWant
FROM the someAliasName CTE I just made above

And that is really all there is to it.

It just goes one step further in this case, because just like Temp Tables, Dervied Tables and Views, CTE’s can be joined on to other SQL Objects, like other tables, to help filter and produce specific result set. They are, as the name calls out, just another type of TABLE after all.

That’s what the last piece does.

WITH someAliasName AS (
   SELECT customer_id,
   COUNT(subscription_id) AS ‘subscriptions’
   FROM orders
   GROUP BY customer_id
)
SELECT theFieldsIWant
FROM the someAliasName CTE I just made above
   INNER JOIN someOtherTableICanCorrelateOn;

As to the break down here; it looks like we basically took a COUNT of subscriptions for each Customer ID we have from the ORDERS table. This makes up the CTE we’re going to reference.
We then go use that information to get the Customer’s details, such as the Customer Name, because after all how good is an ID to most people, unless they memorized who that ID represents. To that we said, hey, for this Customer ID, that I have a COUNT of subscriptions for, go to the Customer’s Table and get me their other details.
That’s what the ON part of the JOIN is doing.

Hopefully with the terms given here, you can scour around the internet to search for more in depth explanations if needs be, or to expand further you could practice writing this as a Temp Table, or View, or Derived Table case :slight_smile:


#3

Thank you so much, Mike, that was a really nice breakdown. :grinning:

I still find it fairly complicated given that you don’t really see the tables and the changes you make.
In the line SELECT customers.customer_name it would translate to SELECT this_table.this_row, right?


#4

Almost, in that given case what we’re doing is EXPLICITLY declaring the table we want to grab a FIELD from. We reference Fields, and each line back is a row/record. :slight_smile:

This is how we get around the error that sometimes occurs with SQL claiming ambiguous columns names. Which happens when you query on TWO or more joined tables that can have the same field name EG: customer_Id.
SQL doesn’t know which table “customer_Id” might come from, so we tell it Customers.customer_Id and previous_query.customer_Id

So, using the original code you gave. There is no decent way to do formatting in so hopefully the following translates well: Customers table has been set to BOLD. Our CTE is ITALIC.

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

Hope that helps :slight_smile:


#5

Thanks again, Mike, really appreciate you taking the time to explain.

Do you have any tips to where i should go from here?
I’m finished with codecademy SQL and im not too impressed with it (used PRO), seems I just began to scratch the surface.


#6

SQL is like most other languages really; always evolving, so there is always another layer. OK, maybe it’s not .NET or some web languages, but there is always new syntax :stuck_out_tongue:

SQL is mostly platform agnostic, from a syntax/type of usage stance, the libraries and GUI will differ, but to be fair, depending where you want to take it/what you wanna do will probably determine what you should follow up on.

I guess that means, if you want something like Wordpress sites, then study what that relies on, like PHP and My SQL, and go search around for information on that online. Should you go to a Microsoft based shop, you’ll want TSQL as your main base, lots on MSDN sites for that etc. That’s really what it comes down to mostly.

There should be no shortage on what you want to study, and you can actually switch, though the functions, syntax, and GUI will change between types of SQL. The main question you need to decide is what you want to do, so you can dive into that area.


#7

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