In this video, we will learn CTE in PostgreSQL step by step. CTE stands for Common Table Expression, and it helps us write clean, readable, and reusable SQL queries using the WITH clause.
We will start with the basic definition of CTE, then understand the WITH clause, CTE syntax, practical examples, best practices, and finally a multi-step CTE query. I have also added homework tasks so you can practice CTE queries on your own.
If you are learning PostgreSQL, SQL queries, backend development, data analysis, or database concepts, this video will help you understand how CTEs make complex SQL queries easier to read and manage.
In this video, you will learn: ✅ What is CTE in PostgreSQL? ✅ What is Common Table Expression? ✅ How WITH clause works in SQL ✅ CTE syntax in PostgreSQL ✅ Simple CTE query example ✅ Best practices of CTE ✅ Multi-step CTE practical query ✅ Homework tasks for practice
CTE is very useful when your SQL query becomes long or complex. Instead of writing everything in one nested query, we can break the query into smaller readable steps using CTE.
Watch the full video and try the homework task at the end.
------------------------------------------------------------------------ ⏱️ TIMESTAMPS 0:00 Intro – CTE in PostgreSQL 0:30 What is a CTE? (Definition) 1:05 The WITH Clause explained 2:07 CTE Syntax breakdown 4:25 Practical Query #1 – Your first CTE 8:45 Best Practices of CTE 11:20 Homework / Task for you 11:40 Practical Query #2 19:10 Practical Query #3 – Multi-Step CTE
AI Generated·May contain errors
Video Summary
AI Generated
This video introduces Common Table Expressions (CTEs) in PostgreSQL, explaining how they function as temporary virtual tables to simplify complex SQL queries. It emphasizes that while CTEs offer similar performance to subqueries, their primary value lies in improving code readability, maintainability, and logical organization.
Hello, what's up, guys?
Welcome to the PostgreSQL Series
Today we are going to see CTE
What is CTE?
I'm experimenting a little bit
As we do in school
Handwritten notes
I'm trying to make something like that
Maybe you'll like it
You can see its definition.
First of all
Is a named
Temporary result set
Defined within a SQL statement
You can understand it
Like a temporarily virtual table
That exists only while
The statement is running.
It helps in breaking down complex logic
Into smaller, easier-to-read sections
Steps
This is a temporary table
Which holds the result
For a very specific
Amount of time
Until you are executing the query
This is a
Temporary table
Now
If we want to create a
Temporary table
With the help of this
With clause
With is a keyword used to
Declare a CTE
It appears before the main select
Insert, update, or delete
Basically you have to write this
Always starts
At the top of the query
Which I just told you
Then we have this readability
It enhances readability.
Because you define the logic first
And use it later
You can hold your CTE like
We define a variable in JavaScript.
You can hold it like that
And use it later
Or in my opinion
You can call it a function
Which you can reuse
Whenever you need it
Quick points
Improved readability
Break the logic into steps.
Reusable within the same statement
Excellent for complex queries
We have understood.
A CTE is a temporary table.
Which holds the result
For a specific amount of time
Now let's understand syntax
How it works
Here you can see
In syntax
We need to use
The with keyword
You have to use
The with keyword
Here you can see
With
The name of the temporary table
Like this
As
This is the query
Which you normally use
Hold all the queries
And then select
Everything from this particular
Table
Here you can see
This
What is this?
This is your CTE
And you use it here
You use this table here
And when you call it
It will run for that duration.
And then it won't run
Define first
Use later
Here you can see
If you want to write multiple CTEs
You have to use
Comma trick
You have to use with
Here you can see
With is used only once.
Here you can see
A comma is used.
You have to write with
Comma second CTE
Comma third CTE
And then you can see
Select everything from this
First CTE
And then you can
Join it with your second CTE
Like this
Don't worry
Some things you have to remember
For example
With com first
Each CTE is assigned a name.
As contains the query
I forgot to tell you
Here you can see
Even here as well
Here there is no alias.
But you can see
With
A set of parentheses
This is it
And main query
Use the CTE like a table
Now, guys
Let's see an example
To clear your doubt
But
Before understanding this example
Let's understand it practically
For example
You can see
On day 18
I guess day 18 or 19
We found courses
That cost more than the average.
Fee using a subquery
In the WHERE clause
Let's rewrite it using a CTE
It makes the code instantly self-documenting
If you remember
There was a query
We have to find out such courses.
Whose price is
More than the average course
And we did it
With the help of a subquery
Today we have to
Do it again
But this time with the help of
CTE
I think it's somewhere around here.
Where the clause
Here it is
Select tuition fee from courses
Where the fee is greater than
Your scalar subquery
You can see
Let's copy it
I have to write it in CTE
Let's run it
You can see
I have to
Recreate it again
But this time with the help of CTE
CTE
Should be at the top
If you want to create a CTE
We have to use the WITH keyword.
You can give the name of any table.
Fee or Feature
As
As is important
And then parentheses
And this statement you wrote
Remove it
And
Leave it
So, this is your CTE
It's starting from the beginning.
Tembré Table name
what we will have to give
And that's it.
Now, what exactly do we need to do here?
Look, this will remain exactly as it is.
There is no problem with the courses.
That's enough, just select
average fee from courses that was
What needs to be written here?
Here, I just need to select me
This
Let's give it a name.
It will become exactly the same.
I've already paid the average fee.
Select this
from this
This is it.
Alright
So if I run this
Look from the goat first here
So in total we have 8 rows.
Alright
Whatever it may be
The One and Only
The most humble one is indeed correct, right? The dough is ready.
It should be above now. If you're going to do this
I run and boom guys
We are getting the same output.
we are getting
the same output so this one right now
We have obtained this with the help
of CTE
Alright, now you think
Wow, I ended up coming here so easily
The work on the telegraph line is done.
So much had to be done here.
That doesn't make any sense at all.
Well, that was it
I wrote this instead of him.
Because it is representing this
Instead of courses, I have this temporary table
I've already called him.
To explain to you
that CTE if we have
If we need to create it, how can we do it?
I am not saying that
It has been perfect here.
Here it's even better.
Because it is very easy
It's happening in the line, and we're beginning to understand.
But even on that, this too
I'm beginning to understand it already.
There are just a few extra lines of code here.
But Atlas, you've now found out
a code
The one we all made fun of, we ridiculed her thoroughly.
It has happened to him now
The greatest advantage comes at that point.
Now I will tell you when it comes.
Here you can see its best practices.
You've seen an example, haven't you?
There are two more, and it will be fun.
Please take a look at the first one.
I'm doing a little bold thing
And this is how I am here like this
I'll put it down; you can see it very clearly.
the only one with the keyword
Now see how easy it will become
To understand this best practice
Because we have already seen an example
Coding something
And run it
You just need to write one thing
Then after that, you
Whatever your table name is
and then as the keyword and then pair of parentheses
You have to write
Write it all in one go with you
So how is that possible
I can see Cleary here.
Once he came with it, after that
Table name as parenthesis
Then coma and this kind of learning
We have already seen it.
With one more time, rebel sir, with coma
Scope Life Spam
Look here
So the first is the second.
And third, look at this
All queries
There is a performance issue in the Dhone Kar system.
If you fully understand all the queries
And your output is coming up now
Use it with him.
But if you feel it's not, man, in this
The logic is a bit problematic for me.
Catch it.
Catch the subset
That's what I feel too.
I'll beat him up, function, you've kept one already.
Wherever you need to call that function
After writing the entire function, call that function.
Kind of up, that way
Catch it
It was all on you.
Above you is
For 10 seconds
If you don't understand the subquery
Catch it without fail.
It's all up to you.
For which you have made one
You have his rikhia
But it will definitely reach the interview.
CTE in all queries
Alright and
It's the same as always
I have homework for you guys
The link that is below description
I'll share the source code in the comment section.
From rest, you are there
You can't access it, as always
I have done it all.
The first, second, and third questions
Here it is, please try this, you must definitely give it a go.
and for a while
Now you can see the three of them, all three of them, very clearly.
Please take these questions along with you.
I had set up the camera so that you could see this third
Please read the question.
Alright, now let's proceed to Siddha.
Our second question
the virtual table aggregating Look
We want a list of students.
and the total amount of money
they have spent
We need the list of students.
and how much money did they have in total
I need the list of what he has spent.
Need but
We want to calculate the math.
before we join the tables
to keep things fast and clean
Meaning, even before joining me
I need math, I mean, him
How much did the student spend?
I have to create it for me already
I mean, it has to be implemented.
I need to create it, basically I need to write a query
and then join after that
So this is a normal way.
Look, I just said it again, what did I say?
CTE is a medium.
to make the logic clear
That's not right.
This is a question like no other.
Without CTE, I cannot do it.
It's not like that without CTE
I have placed this right here before all of you.
If I run this
So you will see that I
Now comes something like this
Same output again me ko
Need it, but this time with the help
of CTE
You understand, don't you?
It is said here
Math, you must do first
before joining
So, you should already understand it.
that CTE of yours there
will not join
Here, simple is the same.
with
What is the name of your table?
as and then parenthesis
You must already know about it.
Syntax has already come to your IDE.
What needs to be done?
Here you can write comfortably.
select
amount as total spent
Something like this
from
Obviously, your payments
perfect
and where is this
It's been stuck, that's for sure.
select sum as total
from payments
Where it has already been completed
and
in the payments table
Just once quickly
It is to see the student ID
What did I write? select
start from
Payments, okay, that's it
Once I saw it quickly, what is it?
Student ID is
Alright, student ID
So here, I also need the student ID
It will be fine, okay
perfect
Brother, the fun's over in auto-complete.
You see, there's one thing
We are using an aggregate function.
And with him, we are normal.
Also provide access to the field here
Basically, you're trying to select the checkbox.
and as per the rule, we know
If I call it
Look here, I'll get a warning
Because it states that if you use an aggregate function
but if with him
You are using another field for extrusion.
I mean, do you want to
They should appear in your result.
for that we need to use
group by
group by and then
p.your
student ID
This is it.
In this way
I run it.
What output will I get if I look closely?
A second something
Hey man, these payments
Who will write the 'p'?
I'll have to write the 'p' for payments, won't I?
That's exactly why he'll find out what this 'p' means.
It's happening, look here, root
So we have student ID 9.
10 12
14 15
and then 17
This is the student ID, and this is theirs.
We've already received the data in this manner.
But here's a problem here
It's still the same as before.
Join already we haven't done it yet and
This one, student ID 9, its
What is your name? I don't know the name, and then he
How much have you spent, that's what I want to know.
That is why it is needed
We need an inner join here.
because of the SIP payments table
But my work was getting done
There is a confusion about who this is.
the student ID whose was 10
What was the name, understand you
That's exactly why
Now look at me, it's mine, so I'm ready
and now I'll give it a name
The bid is correct. I have one
Just give me a name in this way
Now this group is bound
I'm removing this; I don't need it.
Look, now here at this place
total spent
I'll put it down, but there's one thing
inner join payments
I don't need to write anymore now
I will have to perform an inner join.
With this temporary table
So I picked this up here.
You don't need this.
Philal is here.
I am working.
Let me also keep this one.
I'm also putting it down here.
So what will you get now
An ID will also be provided
His name will also be found.
How much did he spend in total?
It will also be available from here
Now I am complete
I will select.
And I will run it
P.dot
Student
ID
Here
Coma
By the way, the cow
Here, we have grouped them into a group.
So I think the need for this won't be here for us.
That is what happens
And it seems that
Group by
P.dot
I don't feel any problem at all.
But why is this happening?
Here
Don't give a comma
Alright
It's not that I don't know.
But it is certainly true that
That's just my bus style.
Look at it this way:
But here you have to give it
Here, B, well, here
Even if not given, it still goes below; there's nothing beneath it.
But now it has become my job.
And look at this
I didn't see it, just because I needed a name
We need an inner join
For this one
Inner join is very much.
That's why we've used the inner join here in this manner.
And it's done with me.
How much has Ayush Cystea spent, Amid Berman, so much
Bikram not that much
So, this is your CTE in this manner.
Do a job
You are given all of this
It will have to be done altogether.
While this run is ongoing, just for the time being
This is your run.
So, this is your virtual table.
Your Tembirli table
It has not been stored on the permanent database.
We have placed it in a virtual table.
From peace
Now whatever he has
Not only here
If you think this is also being used in any other query
So you can also use it there.
As we enter the world of Jagaskrip
Whatever variables we have
Constant or whatever you call it, this function
We keep it on top and we use it.
So just like that way
Now, check this out — one more interesting thing
Here it has come to where
Let's change cities together
We must change them now.
I mean, here we are, just one seat now
We made not one, but a temporary one
Table, we have created it, but what is it?
If I have to make two crates twice and then
Later, if I need to use it again
We've already seen it.
With you
CT1 Table name at that time
Today in this way
Then it will be our turn to face the consequences.
This is his syntax.
This is syntax, and so here it is
But you will have to join.
What is written here?
Active students
From the table
From the courses table, you
Premium Courses
Here, take it
You are taking the premium course.
Catch it this way
And now let's just go ahead and join him.
I'll just copy and paste it.
Copy this
Pasted it.
Look, is there any difference?
with cte first table
as parenthesis kauma
This is your second CTE
select id name
from your users
where the role is that of his student
and be active
we only want to look at the
active students
from the users table
I need only active students
That's why it's true
and obviously, the users table also includes students
Admin is also there, professionals are also there
So, role equals this
We made this simple one and just placed it here.
Second, premium courses for us
I mean, this time again ID then title
from your courses
Where the fee is greater than 4000
So do one thing
If I run this
So, I'm all tense and wired like this, you know
I have obtained the ID and their name.
The filial active
If I do this
Then I will receive a list of all those courses.
Those whose price is more than 4000
Total 6 is there
Now, look, what needs to be done
Now then, because
Because it is an active tenant
The first CTE
I am presenting myself like this today.
So premium courses from P
So this thing here that I have
My uses name
P means your
I need the title from the courses table
from enrollments
And has enrolled in such premium courses
Make a list for them
Isn't this amazing?
So, Gais, that was your CTE
You must have liked it very much
And Gais, I forgot that you people are
You must have liked it very much
I mean performance.
CTE is not always faster, as we have already seen.
PostgreSQL Optimizer
determines the best execution
plan but CTEs are
excellent for readability and clarity
query structure
So, yeah guys, this is it, the video for which only Tanhee
I hope you really liked the video because
It takes a lot of effort to create videos like this.
And this last animation also took a very long time to complete.
But I was told to show you
But the link that is, the source code, and whatever notes there are
You will get descriptions and comment sections completely free of charge
Definitely check it out
Liked the video? Don't forget to share it with your friends and spread the joy!
You have an at-like subscriber about you.
You haven't subscribed yet, so please subscribe now
See you in the next video
Nex is the subscriber.
It will go away later.