SQL WITH Clause Tutorial | (Common Table Expression)
🧠 Don’t miss out! Get FREE access to my Skool community — packed with resources, tools, and support to help you with Data, Machine Learning, and AI Automations! 📈 https://www.skool.com/data-and-ai-aut...
Today we are taking a look at how to use SQL With Statements, also known as CTEs (Common Table Expressions). These can easily help simplify your code and subqueries if you are confused on how to use them.
Everything is coded within MSSQL and inside SQL Server Management Studio.
🚀 Hire me for Data Work: https://ryanandmattdatascience.com/da...
👨💻 Mentorships: https://ryanandmattdatascience.com/me...
📧 Email: ryannolandata@gmail.com
🌐 Website & Blog: https://ryanandmattdatascience.com/
🖥️ Discord: / discord
📚 *Practice SQL & Python Interview Questions: https://stratascratch.com/?via=ryan
📖 *SQL and Python Courses: https://datacamp.pxf.io/XYD7Qg
🍿 WATCH NEXT
SQL Tutorials Playlist: • SQL Tutorials for Data Analysts
SQL Case Statements: • SQL CASE Statements Explained: 4 Practical...
SQL String Functions: • 12 Essential SQL String Functions You Must...
SQL Stored Procedures: • SQL Stored Procedures Made Easy: 3 Essenti...
In this video, I teach you how to use WITH CTEs (Common Table Expressions) in SQL through three practical query examples. I first discovered the power of CTEs when solving a medium-difficulty LeetCode database question using subqueries, only to find there was a much simpler solution using WITH statements. Once I saw how clean and readable CTEs made my queries, I knew this had to become part of my SQL toolkit.
We walk through three progressively complex examples using a Billy Strings concert tour database. First, you'll learn how to find venues where 95% or more tickets were sold. Then, we identify venues that sold above-average tickets. Finally, we use multiple CTEs together to compare state-level concert averages against the overall average.
By the end of this tutorial, you'll understand how to structure WITH statements, join them with regular tables, perform calculations within CTEs, and chain multiple common table expressions together. CTEs are essential for writing clean, maintainable SQL code and are frequently tested in technical interviews. Whether you're preparing for data analyst interviews or just want to level up your SQL skills, mastering CTEs will make your queries more elegant and easier to debug.
TIMESTAMPS
00:00 Introduction to WITH CTEs (Common Table Expressions)
00:26 First Query Setup - Billy Strings Tour Table
01:04 Building Your First WITH Statement
02:15 Calculating Concert Percent Sold
02:46 Joining WITH Statement to Main Query
04:00 First Query Results - Venues Above 95% Capacity
04:19 Second Query - Venues Above Average Sales
05:28 Second Query Results and Verification
06:09 Third Query - Multiple WITH Statements
06:44 Finding State-Level Song Averages
07:53 Calculating Overall State Average
08:32 Final Query - States Above Average
09:23 Results and Wrap Up
OTHER SOCIALS:
Ryan’s LinkedIn: / ryan-p-nolan
Matt’s LinkedIn: / matt-payne-ceo
Twitter/X: https://x.com/RyanMattDS
Who is Ryan
Ryan is a Data Scientist at a fintech company, where he focuses on fraud prevention in underwriting and risk. Before that, he worked as a Data Analyst at a tax software company. He holds a degree in Electrical Engineering from UCF.
Who is Matt
Matt is the founder of Width.ai, an AI and Machine Learning agency. Before starting his own company, he was a Machine Learning Engineer at Capital One.
*This is an affiliate program. We receive a small portion of the final sale at no extra cost to you.
In questa pagina del sito puoi guardare il video online SQL WITH Clause Tutorial | (Common Table Expression) della durata di ore minuti seconda in buona qualità , che l'utente ha caricato Ryan & Matt Data Science 17 maggio 2023, condividi il link con amici e conoscenti, su youtube questo video è già stato visto 2,250 volte e gli è piaciuto 42 spettatori. Buona visione!
