CTEs in SQL made easy!! đ§
don't let them confuse u anymore, I gotchu đ¤
Got BDE?âĄď¸
Hi BDEs! âĄď¸
Hope youâre having an amazing week! Iâm currently feeling super inspired making lots of content right now since I have the time with it being my full time job now đ Iâm also prepping for a KEYNOTE for a conference at the end of May! EEEEEP đł
Please support my amazing newsletter sponsor to support my free content âŹď¸
Quadratic AIđ is the COOLEST spreadsheet ever because it integrates with many data sources including databases & APIs, and you can even vibe code your own Python visualizations using the built-in AI. The native AI also writes and debugs your SQL and Python codeâ itâs actually INSANE!
Quadratic AI empowers all business professionals (not just data people) to perform their own analyses and uncover insights which ultimately helps companies be more data-driven and frees up analytics teams from all the ad hoc requests.
Tutorial below âŹď¸
âĄď¸If youâre new here:
đđ˝ââď¸ Who Am I?
Iâm Jess Ramos, the founder of Big Data Energy and the creator of the BEST SQL course and community: Big SQL EnergyâĄď¸. Check me out on socials: đLinkedIn, đInstagram, đYouTube, and đTikTok. And of course subscribe to my đnewsletter here for all my upcoming lessons and updatesâ all for free!
âĄď¸ Social Highlights:
CTEs in SQL
CTEs are one of the most important skills that will allow you to take your SQL skills to the next level. Theyâre so essential to coding SQL, itâs really hard to advance from a beginner in SQL to intermediate without them.
Iâll fully admitâ I used CTEs for years at my first startup job without knowing their name. I remember going to data analytics interviews and being asked if I knew what CTEs were. Obviously, I was confused because I had never heard the formal name, and I likely missed many opportunities because of that slight lack of information. So itâs really important to not only know how to do something, but also the name of it and why you need it. This info will help you out when youâre interviewing.
I HIGHLY recommend my hands-on CTE Chapter in my intermediate course, Big SQL Energyđ for a deep dive with a LOT of practice on real business problems.
So whatâs a CTE?
A CTE is a common table expression (quick! write that down so you donât end up like me lol). In simple terms, a CTE is a temporary result set defined within the scope of the query. This means that you can refer to it whenever you want and however many times you want only within the scope of your current query. You can think of a CTE as a table you design with a queryâ and then you give it a name and tell SQL: âhold onto this and remember it for later!â Then you can use it just like you would a regular table. Hereâs a very simple example of the syntax:
with cte_name as(
select *
from table1
)
select *
from cte_nameBenefits of CTEs:
Iâve already hinted at one of the biggest benefits of CTEs: theyâre reusable, so theyâre really handy to use if you have to refer to an output multiple times in the same query. Theyâre only usable within that query thoughâ so if you donât rerun the CTE, you wonât be able to use it.
One of the other major benefits of using CTEs is that they offer major readability benefits. A really important part of coding is making it useful long term, especially if someone else takes over or needs to edit your work. CTEs make it much more easier to understand code (especially compared to subqueries which I also go over in my intermediate courseđ) because you define output and sections one part at a time. This gives the reader the advantage of slowly digesting and interpreting your code one step at a time.
Another great benefit of CTEs is that they can be recursive. Although I havenât had many use cases for this in the real world, it is a more advanced programming concept and is worth knowing and learning. This is where CTEs can stand out over subqueries since subqueries donât have this capability.
CTEs vs. Subqueries:
I keep mentioning subqueries because itâs pretty impossible to not talk about CTEs and subqueries togetherâ theyâre kind of like peanut butter and jelly. CTEs are kind of the inside out version of a subquery. And you guessed it, there are some instances where you may prefer to use a subqueryâ but thereâs not always a black and white answer. Although Iâll unpack subqueries in further detail in a future newsletter, itâs worth mentioning that almost all CTEs can also be written as a subquery (with recursive CTEs as an exception) and vice versa.
Why do you need to use CTEs?
Sometimes you may choose to use a CTE for better readability even if it offers no real advantages for syntax or optimization. I do this all the time if Iâm writing code that will end up in a dashboard or be passed onto a teammate. Using CTEs and making your code readable is a nice thing to do to your coworkers. Please never send me multiple nested subqueries!
However, there are other times when you HAVE to use a CTE because you canât do something all in one select statement. A really common beginner example is nested aggregate functions. In SQL, you canât nest aggregate functions, so if you want to take the average of a bunch of sums, and you write avg(sum(column)), youâll get an error yelling at you about nested aggregate functions. This is where CTEs come into play. You first have to define the summed column in a CTE, and then you can call on the result in the second query like this:
with order_counts as(
select
user_id,
sum(ordered) as num_orders
from
table1
group by 1
)
select avg(num_orders) as avg_per_user
from order_countsOther common times you need to use a CTE is when you need to create other datasets with mismatching granularities. For example, maybe you have a dataset of orders and the day they were placed on. You may want to also include the monthly total orders for the company in that same row by the order date. You could create a CTE that counts the number of orders per month, which aggregates the dataset on month by summing the number of orders per month. Then in the following select statement, you can join the monthly totals CTE to the original dataset with the day the order was placed. This allows you to have the day the order was placed along with that monthly total side-by-side in the same row.
with monthly as(
select
date_trunc(âmonthâ, order_date) as month
count (*) as monthly_orders
from orders
group by 1
)
select
o.order_id,
order_date,
monthly_orders
from
orders o
left join
monthly m
on date_trunc(âmonthâ, o.order_date) = m.month
;Try to do this without a CTE (or subquery), and I guarantee you youâll have a hard time. Depending on the complexity of the query, number of datasources, and length, queries can have any number of CTEs. In my everyday job, I frequently write queries with 3-5+ CTEs. Sometimes theyâre mandatory, and other times I opt in to use them optionally to make my code more readable and be nice to my coworkers.
CTEs are one of the most useful concepts to know if SQL, so make sure you master them! If you want extra practice on CTEs, I HIGHLY recommend my hands-on CTE chapter in my course: Big SQL Energyđ.
Helpful knowledge on CTE's