Advanced SQL Tutorial | CTE (Common Table Expression)
HTML-код
- Опубликовано: 6 сен 2024
- Take my Full MySQL Course Here: bit.ly/3tqOipr
In today's Advanced SQL lesson we walk through how to use CTEs. ____________________________________________
SUBSCRIBE!
Do you want to become a Data Analyst? That's what this channel is all about! My goal is to help you learn everything you need in order to start your career or even switch your career into Data Analytics. Be sure to subscribe to not miss out on any content!
____________________________________________
RESOURCES:
Coursera Courses:
Google Data Analyst Certification: coursera.pxf.i...
Data Analysis with Python - coursera.pxf.i...
IBM Data Analysis Specialization - coursera.pxf.i...
Tableau Data Visualization - coursera.pxf.i...
Udemy Courses:
Python for Data Analysis and Visualization- bit.ly/3hhX4LX
Statistics for Data Science - bit.ly/37jqDbq
SQL for Data Analysts (SSMS) - bit.ly/3fkqEij
Tableau A-Z - bit.ly/385lYvN
Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!
____________________________________________
SUPPORT MY CHANNEL - PATREON
Patreon Page - / alextheanalyst
Every dollar donated is put back into my channel to make my videos even better. Thank you all so much for your support!
____________________________________________
Websites:
GitHub: github.com/Ale...
____________________________________________
All opinions or statements in this video are my own and do not reflect the opinion of the company I work for or have ever worked for
I might be a bit late for this, but Alex you are doing such an amazing job. I'm an absolute beginner at SQL with no coding background. I have already gone through beginner, intermediate and advanced SQL tutorials in the last 3 to 4 days. I cannot thank you enough for making this so simple and easy. Thank you!
Have you made it up?
Watching the tutorials is one thing, but you can only cement your learning through experience.
how is your learning going on?
@@viron6734 True.
Hmmm 4 days 😮
Started watching this channel knowing nothing about data analysis/science. Did someone online courses on SQL and now I can actually keep up with videos like this. Feels good man
That's fantastic! We are just starting to tough on the good stuff! Haha
Two years ago but still educative. I appreciate you, Alex.
sql was created in the 70s what u talking about bro
It's unCOMMON how helpful these videos are, Alex
:D
@@AlexTheAnalyst You always bring so much to the TABLE, Alex. Thanks. :D
I love how chatgpt lead me to this tutorial. Seems like you are indeed the GOAT in SQL tutorials, Alex.
I was waiting for the SQL series to be continued after completing intermediate SQL tutorials. I am so glad you made it. Thank You! Keep Growing😊
Haha you're most welcome! :D
Best video about CTE, even put important points about CTE in 3 min video not just implementation. fast and to the point. Thank you Alex.
Glad you enjoyed it!
Slow and steady, it's all making sense, did some practice questions on Stratascratch and couldn't get past. But now I think once I'm done with the Intermediate Section I should be able to take on more challenges.
OMG! Thank you SO very much for this simple tutorial. You are the only person who said, take your existing code and simply encase it all into the WITH clause and then SELECT * the clause. I had spent hours chasing deadends until I stumbled onto your tutorial. Many, MANY thanks!! 😊
Glad it helped!
The easiest understanding of CTE's I have ever seen. Thank you Alex
New to learning SQL through your channel and it has been great. One question I have is when would be an appropriate time to use CTE's? Feel like I get it but unsure of when exactly it would be used
I left SQL ten years ago.but needed a refresher today. This was useful
As an enterprise data analyst I use CTEs, subqueries and temp tables regularly. However I have never really looked into the benefits from a performance perspective of using one over the other. It would be an interesting video I think.
There is no performance benefit in using CTEs over subqueries. The real benefit in using CTEs is readability. Subqueries can get really messy if they're complex.
@@chillvibe4745 interesting. My preference is to use CTEs for the reasons you describe but I work with very large data sets so it's good to know I'm not putting myself at a disadvantage performance wise.
I understand the process described in this video of creating a CTE, but I still don't understand why you'd want to use one. From what I can see you're just naming a specific query, but you can only call it right after you name it. Why name it at all? Why not just execute the query? Maybe I'm missing something.
@@thenikkihuff Because some times you need to aggregate or transform the grain of a table before you query it further so you do your transformation in the CTE and then query the CTE as if that is the natural state of the table.
So for example if you had a table of all transactions of all your customers and you wanted to see the 1st, 50th and 100th transaction for each customer. Trying to do this is a single query is very difficult but with a CTE we can add a column that doesn't exist when after the CTE we can query that new column for example
WITH my_cte AS (
SELECT row_number() OVER (PARTITION BY customer, transaction_date) ROW_NUMBER, ct.* FROM customer_transactions ct
)
SELECT * FROM my_cte WHERE row_number IN ('1','50','100')
This would return all 1st, 50th and 100th transactions for each unique customer even though the row number doesn't exist in the original query.
You could even take that original CTE then turn it into three more CTEs creating a mock table for all the 1st, 50th and 100th records in their own tables.
You could then pull get an averages across all of those categories in a single final query all of which were values that didn't exist in the original table.
@@iShaymus Well explained. That's a good example
thank you for doing this tutorial series, it was quick, comprehensive, and put me up to speed on my knowledge of SQL..you need more subscribers and you just got one today...again thank you.
Your explanation is so clear and very easy to understand regardless of ppl's background. Simply saying thank you is not enough to express our feelings.
Thank you for posting. All of your videos have been great to support my learning through a Data Analysis boot camp. The way you explain concepts simplifies even these more complex concepts in a way that makes me breathe a sigh of relief 🙂
The way you explain open my mind than reading from the website elsewhere, very easy to understand, thanks
I keep taking courses on coursera and datacamp and I understand nothing and every time I end up understanding concepts from your videos. you are amazing, keep it up!
This video is invaluable! I’m in a beginner without any coding experience, hence I’m currently in a community college. The prof and book’s explains lesson so complex and out of touch to beginners that it’s hard to visualize, unlike this simple 3.5 min example. Appreciate this
undoubtedly I am so ecstatic to make it till here!!! :))))
Alex, I am just loving your tutorials and now want to practice and test myself!
please suggest to me some assignments I can work upon to polish my SQL skills!
edit: and yes..thank u❤ so much for connecting with me through linkedin. To be honest, I really need ur tips as u are already working as The Great Analyst (my dream profession!😊)
hi Manasi.. Can you please share the assignments or any useful practice source you got from Alex sir??? It would be really helpful. Thank you!!
Have you seen any yourself?@@pranavgupta4065
Thanks Alex. This is what I was looking for. Done with all your tutorials. This is some quality stuff out there in the RUclips for SQL. If possible, please upload more on advance SQL topics. 🙏
I already finished intermediate series of sql. I am at this series now. Your explanation about sql just great
Expected this to be complicated. Grateful Alex that you've simplified a seemingly complex query. THX!
I know this a bit old but I cannot wrap my head around a use case for these ; I'm sure it will come to me eventually lol. Wonderful series . I've been really intimidated by a couple of these concepts up until now.
When doing subqueries using a from clause , much easier to use a CTE than a sub query
They make your query arguably more readable, especially if you take the time to give your CTE (or CTEs) an explicit name (as opposed to "cte" or something). It makes your code look like a step-by-step recipe, and the main query at the end becomes much more concise and easier to wrap your head around as a result.
Same-- why not use temp tables?
@@julierenee6668 yeha if there are equivalents then why do this?
For those who want to copy paste....
WITH CTE_Employee as
(SELECT FirstName, LastName, Gender, Salary
, COUNT (Gender) OVER (PARTITION BY Gender) as TotalGender
, AVG (Salary) OVER (PARTITION BY Gender) as AvgSalary
FROM [SQL Tutorial].dbo.EmployeeDemographics as emp
join [SQL Tutorial]..EmployeeSalary as sal
on emp.EmployeeID = sal.EmployeeID
WHERE Salary > '45000'
)
also noticed that instead of having ".dbo" you just input ".." and it had the same result. Thank you!
This is very informative! My question is: in what business scenario could/would this be applicable? I'm a bit lost in that regard. Thanks!
There are a lot of reasons you may use this. They are often used to break up complex queries into more easily digestible chunks. Another reason is to perform more advanced calculations. You can query off of the CTE and perform additional calculations on top of the CTEs calculations. So you can use an aggregate function like Sum, Avg, etc in the CTE and then later do another aggregate function when querying off the CTE.
In addition to Alex's comments (all of which I agree with!), the clue is in the name -- when you're using some common logic that would be used in a few different tables. For example, I might use a CTE over a subquery when I want to perform some initial calculations (say some cleansing, adding some LAG/LEAD columns, etc) which feed into multiple subqueries of my main query -- otherwise, the logic for the CTE would have to be repeated in each subquery
I use Redshift so a simple use-case is producing a summary table with different aggregates (Redshift doesn't have ROLLUP). I'd use the CTE to build a 'base' table from the data in the database, then reference the CTE in subsequent queries to aggregate the 'base' table in different ways:
WITH base AS (/* build my base table */)
SELECT
daily.week,
daily.day,
daily.day_count,
weekly.week_count,
FROM (
SELECT week, day, COUNT(*) AS day_count FROM base GROUP BY 1, 2
) AS daily
LEFT JOIN (
SELECT week, COUNT(*) AS week_count FROM base GROUP BY 1
) AS weekly USING(week)
ORDER BY 1, 2
;
There are non-CTE ways to do the above, but I like the readability of the CTE approach
Couldn't get any easier explanation than this!!
Thank you so much for the comprehensive tutorials Mr.Alex, really helpful!
Amazing tutorial! So easy to understand and straight to the point. Thank you!
CTEs are the best. So many use-case scenarios for this.
You are very loyal . I really respect u because you give us a chance to love our dreams to become a data analyst
Great videos and playlist Alex. They have really helped me learn SQL at a pace I didn't think was possible. While still being interesting and engaging.
Such an abstract subject easy to understand.
Anyone know which video he's referencing where he built that query that was added into the WITH statement? Curious about the 'OVER ( PARTITION by' statement.
With "SELECT INTO TEMPORARY ..." would make it possible to use the temp table in more than one SQL statement in one transaction.
I just did this in DataCamp, and, although I completed the exercise OK, I felt that I didn't really get it. Now I feel that I do.
Simple and to the point explanation 💯
so using Temporary Table is more powerful than CTE, because it still stored during the session, right?
is there any benefits for using CTE over temp Table ?
Yes, in general temp tables are usually better to use.
Have you done any other Microsoft sql server classes or just the MySQL classes? Essentially where should I continue on with sql after you beginner, intermediate and advanced videos?
you are really a great person, thank you so much
Hey Alex...much appreciated about the video and content you teach to the community. Am waiting for your Portfolio project series, which will be much helpful in proceeding further. Thanks much. Cant thank you enough :)
This is finally the "hello world" app from which I can understand CTEs.
Hey Alex, I think you have not uploaded your video about sub queries. Hope you see this comment and upload it. I love your videos. Hope to see more of your content. 🙂
Question, do you think that CTE has better performance than creating a Temp Table? For Example by doing a Create Table and inserting into it?
Thank you for a great example and lesson. Still a bit confused about how aggregate functions are used without a group-by clause.
On my journey through the boot camp...
Advance!
Thanks for your hard work Alex!
I really like your content and I'm a Huge fan of your broad smile :D
I have a request, could you please make few more videos on Advance SQL featuring Rank, dense rank, aggregate functions as window functions etc. I need to understand in what scenario we can use which function accurately.
You explained this very well and simply! Great demo, thanks
I like these short tutorials. Keep them coming.
Will do! :D
Hey there, i have a question please, whats the difference between subqueries and CTEs ?
Thank you Alex for creating such helpful series...more power to you👍
Question!
When we use the select statement at the end of the CTE, so say if we have CTE.Column1, CTE.Column2 etc will the query become slower?
I read that each reference to the CTE name will cause the query to re-run?
It shouldn’t - it gathers the data one time, similar to a sub query, the when you hit off of it you’re just hitting off of that subset of data which is pretty standard in speed.
@@AlexTheAnalyst Oh I see, I'll test this out thanks for clarifying.
This is my third time learning about CTE's but I'm still struggling to understand the purpose of them. 😟 When is it more beneficial to use a CTE vs subqueries, HAVING statements, or PARTITION BY?
Hi Alex, Just wanted to know what all topics in SQL must data analysts know? Is it enough if we go through your easy medium and intermediate SQL playlist? By the way your videos are awesome
Did you find out? And what is answer?
Perfect veideo! Was very clear and efficient video for understanding CTE's
Very useful when you want to query a calculated column
Code that he pasted in:
WITH CTE_Employee as (
SELECT Firstname, lastname, gender, salary, count(gender) over (PARTITION by gender) as totalgender,
avg(salary) OVER (PARTITION BY gender) as avgsal
FROM employeedemographics dem join employeesalary sal
ON dem.employeeid = sal.employeeid
where salary > '45000'
)
CTE is really a breaking head on SQL, it's kind of the only thing I can't really manage well.
Hi Alex! Awesome stuff as per usual
A3:15 what does it mean its gonna work only after the direct CTE I have made?
If you write the code later it won't work. You have to write it right after the CTE for it to understand that's what it's supposed to do.
Could help to explain in what scenario we could use CTE? When we face.....?
This is so dope, thank you!!!
CTE is amazing
Thank you for refreshing my memory about that, you have a great way to simplify things! Can I get the database, please?
following everything but it appears that the syntax used in the video is now outdated? unfortunately, i cannot follow along well because i keep receiving errors..
Very comprehensive
CTEs only stay in memory if there is room for them. With enough rows / memory pressure they will spool to disk same as any query.
hey man you doing a great work, thanks for sharing your knowledge
This would be more helpful with a few use cases, bc it looks like there's no point to this when temp tables exist... right? When would CTEs be better than temp tables?
Great stuff Alex, it was taught in a comprehensible way. I want your advice that how to actually retain SQL syntax. Should I go through lectures and practice along with it. Or just finish entire module and then take up projects. Since there are too many commands and its really difficult to recall them all the time. The same issue is happening while learning Pandas/Python. Really difficult to recall exact command/syntax. Appreciate your assistance.
i think this is how it is learned, no one can learn it by heart at start. The concept is necessary to learn because if you know the concept name and what it does then its easier to search and use it in code and this is the way to code IMO
So cte are basically instances of a function in terms of python? We can call the cte any time but right after we have created them. Correct?
Thank you very much for the video. I have a question: why is it necessary in the where statement in the CTE to write the number betwenn ''? Thanks in advance for trying to clarify!
Thank you Alex! This is very helpful. Are you planning to create Python training videos in the near future at all? Your way of teaching is very clear, and helpful, so I thought it will be great if you could create some Python beginner videos for data analysts! Thank you!
I definitely will!
I can't thank you more Alex.
I'm still a little fuzzy on why you would use CTE vs just creating a subquery??
Hey man I’ve been acquiring analyst skills for a few months now and received some certifications, but I’ve haven’t gotten anywhere with getting entry level data analyst interviews, can you recommend maybe another job as a first step to becoming an analyst?
I would look into Business Analyst and BI Analyst as well. There is a lot of overlap in those 2 jobs and there may be more opportunities. Hope that helps!
Did he add Toby? I dont have him in results when i run my query
Great video! Do you find that performance is better on a CTE or a temp table? Let's say my underlining dataset is 6 million rows. If I wanted to do the "heavy lifting" and break it down to a more manageable size to then query off of would a CTE or a temp table be better?
Thanks
It was really useful, Thank you so much ❤️
Can you use the CTE to do joins or unions before your actual end query?
Hey Alex I'm sorry I'm confused. Is this the first in the Advanced SQL Tutorial set? It seems to suggest I should be mastering the TEMP TABLES and the SUBQUERIES.
Great Video
AS AN FRESHER IN DATA ANALYST MUST I ALSO KNOW CTE
WHAT ARE THE OTHER TOPICS MUST I KNOW.
Alex ,
First of all I want to say thank you. Thanks for all the work that you do. It’s amazing. I have a question: I’m creating my resumee/CV, and I want to know if I should add SQL as a skill after doing your tutorials. (The same goes for Tableau series of videos) . I’m also doing the Google Certificate Course, but it will be enough for calling me as skilled in SQL/Tableau after watching your tutorials?
Thank you again!
Thanks for sharing this Alex. It seems you are using the MsSQL Server IDE. In the PgAdmin IDE, might there be a way to join multiple CTE tables ?
this will help me in my exam on the morrow
I attempted to alias Salary to S in the CTE but it didn't work. IDK y but I just wanted to mention it
So I can do what ever I want and it doesn't effect the original database?
Hi Alex! Please correct me if I'm wrong; CTEs are the same as a "function" in Python. Correct?
I have a very basic question, why not create different tables? Why would CTE have an advantage?
I am doing exactly what you are doing however it does the gender count correctly BUT not the average Salary. My average salary column returns exactly the same values as salary column :(
What does this mean -> " OVER (PARTITION by Gender) " ... ??
How come the average salary is over partition by Gender?
I keep getting an error when running this query. "Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'." Can someone please help me out? I have looked it multiple times and I don't see what I'm doing wrong. Thank you
I kept getting the exact same error because I hadn't added the last two lines: Select FirstName, AvgSalary
FROM CTE_Employee
It should be fine after adding them
Keep up the good work
Thank you!!
Thank you so much
I'm not sure to understand why would we use CTE over a sub query ?
Amazing content!! :D