SQL Window Function | How to write SQL Query using RANK, DENSE RANK, LEAD/LAG | SQL Queries Tutorial

so simple and understandable, Thank you so much

Very useful content! Thanks

Thank you for explaining so well👍

Awesome explanation.
Thanks for thr effort and time.

Hi Sir Good Morning,
Please solve the queries below:-
Dept id 10,20 update where 10 update with 30 and 20 update with 40 in single queries.

here why are not we using salary_range instead of full query in case statement

This was really helpful, was working on a challenge that involved checking if the next employee had a higher salary racked my brain for hours and came here only to see it in seconds

can u also add date functions dedicated video, like dateadd, datediff , etc ?

Ye video dekh kar maza aaaygaya , sahi mein beautiful explanation..pl pl pl take full course of sql. Pl take sql advanced questions also. Please please Please please Please please Please please Please please Please please Please please Please please Please please Please please Please please Please please Please please Please please Please please Please please

Can we have multiple columns inside over clause partition by?

Awesome video. But I'm getting error while I was downloading We couldn't find the page you were looking for. This is either because

nice

Good explanation.
Just to add -
when you use the ROW_NUMBER() function, it operates on the rows that have already passed through the WHERE clause filtering. This is important to note because the row numbering is affected by the filtered data, and you won't get row numbers for rows that were filtered out by the WHERE clause.

example -
SELECT
EmployeeID,
FirstName,
LastName,
Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS row_num
FROM
Employees
WHERE
Salary > 50000;

i was expecting output row_num to be discontinuous as i thought row_num is assigned before where , but first where is applied then row_num is assigned.

In SQL, the order of execution of clauses is as follows in order:

FROM: The initial source of data.
WHERE: Filters out rows that do not meet the specified conditions.
GROUP BY: Groups the remaining rows into summary rows by one or more columns.
HAVING: Filters out groups that do not meet the specified conditions.
SELECT: Computes expressions, produces computed columns, and computes scalar expressions.
DISTINCT: Removes duplicate rows from the result set.
ORDER BY: Sorts the rows in the result set by the specified column(s).
TOP/LIMIT: Returns the specified number of rows.
OFFSET/FETCH: Skips a specified number of rows before returning the next set of rows.
WINDOW FUNCTIONS: Applies window functions like ROW_NUMBER(), RANK(), etc.

Hi; Thank you for making it so simple to understand. Great job.

can i get the videos to download of the bootcamp of sql whic was conducted from june 2020 to aug 2023

Link for downloading query is broken please fix

Very very helpful! Excellent work super easy to understand! God bless you with all the success. Thank you Toufiq bhai 😀

This was very very helpful to me. I'm currently learning MYSQL and this was the best explanation of windows functions I came across. I would love to see more videos on MYSQL!

Super bro , Clearly understand

The link to download the scripts is broken. Please fix.

Instead of partition by the command I have to use order by in order to make the commands work.

In the example of fetch the top 3 employee salaries it’s not appropriate to use rank function because how about if you have more then 3 employees have the same high salary , by using rank function you will fetch more than 3 instead you can use the row number function

very simple and clear cut, thnk you

This is the most straightforward version of the window function I ever learned. Super helpful! Please teach more advanced SQL language in this channel.

you're a life saviour

We can also use CTE for lag function to optimise this query. Here's how:
-- Using with clause
with prev_emp_sal as (
select e.*,
lag(salary) over (partition by dept_name order by emp_id) as prev_emp_salary
from employee as e
)
select * ,
case when salary > prev_emp_salary then 'Higher than previous salary'
WHEN salary < prev_emp_salary THEN 'Lower Than previous salary'
WHEN salary = prev_emp_salary THEN 'Same as previous employee'
end as sal_range
from prev_emp_sal;

What SQL you are using?

I have been struggling with understanding window function for a really really really long time. But just one video of yours has made the concepts crystal clear to me. Thanks a lot!

very nice explanation sir .... but instead of using lag (salary) over( ) in case clause we can use alise_name i.e, pre_emp_salary .....that made little confusion other than that it is very clear sir tq

Am I right to say that the problem statement - fetch top 3 employees in each department earning the max salary - can be solved using row_number() as well?

Can we solve the rank function question using row_number? if yes, please comment the logic :)

Awesome!

Your Explanation Really Amazing , Simple And Incredible <3

Super Brother nice explanation.

Thank you!

when will you upload the video for other window function

such nicely taught lecture , i loved it!!

Awesome explanation bro!!! If possible please post Snowflake related stuff also . Thanks for your great teaching skills.

Amazing Explanation.Thank you very much. Could you please post a video on level function in sql too?

This was really very helpful to understand the window function concept, you described it precisely, now moving to next part of it, Many thanks

Is window function and analytical functions both same ?

Crystal clear explination thanks bro

Awesome video. Apt knowledge. to the point.

Thank you sir for clean explanation of these function. One doubt sir, in interview if they ask to find the top 3 employees as per the question you have solved then what is the answer expected from us. Is it rank() or dense_rank()?

@techTFQ you are amazing man!

beautiful lecture

Very well explained!

Great video, mate. You saved a lot of time. My head was spinning having listened to the AI teaching voice in my udemy sql course(365 careers). You are awesome

thank you my Indian brother. The tutorial was very clear!