CTEs in SQL

When, How, & Why to use CTEs in SQL

Got BDE?⚡️

Hi BDEs! Today we’re talking all about CTEs, and I prepped so much juicy info for you!🤩⚡️ This newsletter is sponsored by my ah-mazing partner, Equals! They’re the next-generation spreadsheet conquering the many challenges of traditional spreadsheets. Check them out for free HERE🔗!🤩

---

Today’s newsletter is all about SQL CTEs! But first, here’s a fun poll:

Dataset of the Week⚡️

Today’s dataset is on Vehicle Sales🔗, and we’re gonna be practicing CTEs. Be sure to read the rest of the article for all the knowledge🧠

Project 1: CTE Practice

Vehicle Sales🔗 dataset from Kaggle🔗.

1. What is the average revenue per year? Hint: you’ll need to add up all the sellingprices for each year and then take the average across all the years.

2. Which car model has the maximum average miles (odometer)? Hint: you’ll need to calculate the average miles per model and then calculate the maximum of those.

3. Which car make has the minimum number of sales? Hint: you’ll need to count the number of car sales for each car make and then calculate the minimum number.

Share your results on social media and tag me📸

Share with a data friend who you want to do this project with you❤️

---

Social Highlights⚡️

My thoughts on being called an entitled Snowflake for valuing work life balance😵‍💫 🔗HERE

Mustachio is back! And showing how men apply to jobs vs. women. 🔗HERE

Wining and dining myself in NYC❤️ 🔗HERE

---

Let me know your thoughts on this article & how I can improve my newsletter.

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 course: Solving Real-World Data Problems in SQL🔗

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). More info HERE🔗. 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_name

Benefits 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