Lead/Lag Window Analytical functions in SQL | Advance SQL concepts

In this video we will learn lead/lag analytical functions. these functions are used when you need to compare current row with another previous or next row. Very important for interview preparation as well.

[@rks.siddhartha]

I always get confused with the combination of lead/lag and order by in the over clause which lead to couple of tries to get the desired output, faced similar confusion today and searched your channel for the these functions. Thank you for covering exactly what I was looking for!!

[@ankitbansal6]

Glad it helped you 😊

[@Annakootbhoog]

That's the best explanation I found on lead and lag, thanks bro

[@ankitbansal6]

Glad it helped

[@ianpropst-campbell6028]

This was a helpful demonstration of the lead and lag functions. Thank you!

[@abhisheknigam3768]

After watching your videos i can say that " Interview khi se bhi ho kha ka bhi ho ghanta fark nhi padta " . I faced similar questions and also questions from joins operation. I successfully solved it in sql interview. 🙏.Thank you

[@adityeshchaturvedi6553]

Short crisp and really informative!

[@shubhammeshram8504]

Great. Very informative 😀.

[@lokeshsharma9386]

Thank you, I have got a job with 50% , I have learnt virtually from your videos, my concept got clear . Thank you so much

[@ankitbansal6]

👌

[@amitkushwah8401]

Really Good. Explained in the simplest way.

[@learnwithAashish]

Thanks for your valuable knowledge Sharing. Although I learned SQL but everytime I came here , always feel I know nothing.
Keep Good work going 👍 & also if possible please start sharing your knowledge on Python it will be very helpful.
Thanks

[@ankitbansal6]

Sure

[@sharans5771]

Excellent explanation, especially with real time scenario example

[@ankitbansal6]

Glad it was helpful!

[@dfkgjdflkg]

As usual, great content. Excellent work. thank you

[@jannatbhengray1650]

Thank you so much, it made my topic crystal clear :-)

[@minlin9705]

It is well explained. The example is aweomse. Thank you, Ankit!!

[@ankitbansal6]

🙏

[@mantisbrains]

Thank you,Ankit. very useful!

[@sivasuthan004]

Crisp and clear explanation!

[@ankitbansal6]

Thank you 😊

[@AjayVyas-h6k]

Very good explanantion

[@vikasvk9174]

very informative video ... Thank you

[@mehmetkaya4330]

Thank you for the great examples!

[@ankitbansal6]

Glad you like them!

[@gyan_chakra]

Awesome 😎👍.. such a lovely & simple explanation 😊

[@mdfurqan]

Hi Ankit,
I am using history table where create_dt is the indicator for comparison between 2 rows, I have used this lag logic to generate scd2 on my history table. Thanks

[@apexemperor]

Hi... I am also probably looking for the same thing . I have a table which has Scd2 records and the last active records ends with high end dt something like 2200-01-01
Do u think this logic will work with some twigs ofcourse?

[@mdfurqan]

@@apexemperor yes have implemented and working fine

[@pavitrashailaja850]

Very neatly explained!

[@ankitbansal6]

Glad you liked it

[@DishantGupta-t]

Helpful 👍

[@ankitbansal6]

Glad it helped

[@abhisheknigam3768]

Can we get more interview questions on lag and lead with window function. Like there are two tables employees id with x and employees id with y then find employee who are doing work from home 3 days continuously.

[@anirudhupadhyay695]

Congratulations and thanks.

[@ankitbansal6]

Thank you 😊

[@god_father007i]

Thanks men it helps

[@shankrukulkarni3234]

It really good if you can provide the dataset link so that we can practice simultaniously

[@dfkgjdflkg]

Look online, lots of datasets

[@vikastyagi7811]

Crisp and clear

[@gazart8557]

Excellent

[@ankitbansal6]

Thank you! Cheers!

[@ankush_in_sync5998]

Love you sir, love you

[@PhysicsCoachAnkitSaraswat]

do bring some more example question related to sql topics and provide data shert for practvie sir

[@ankitbansal6]

Sure

[@anamikachaudhary6814]

Order_date column is in text format , maybe that is why it is giving me null select month(order_date) from superstore data table. Can you please suggest why is this happening. it pull null values for me. I am using MySQL workbench and when I use select EXTRACT(MONTH FROM Order_Date) as month from superstore_orders; it pulls null values for all the columns . Can someone help please.

[@sreedharagandla]

Hi ankit ,
Can u let me know super data where it is or can share the link pls so that we can prasctise .
Thanks

[@shifanasheriff]

Hi Ankit ,
What change should do in the query
If I want to see the sales of same period last year till the date
(YTD previous year)? Can you help ?

[@pavan5208]

Create a YTD Flag first and use that as a filter?

[@maqbul313]

Sir, where to get the orders table data csv file. Will you please mention the link of the video that contains the file link?
Thank you

[@prasadchowdary4870]

Where we can download the superstore data

[@pavankumarchahar]

Suppose we have the YEAR, Revenue column but the year is 2012, 2015, 2016, 2017, and 2019 then this case how to calculate the missing default year 0 and subsequent.

[@themightyvk]

Thanks buddy...

[@ankitbansal6]

Always welcome

[@ritikajaiswal3824]

Can you make a video on the code which is different in different database like oracle, mysql workbench, postgresql, ms server?

[@ankitbansal6]

Sure

[@Jessicaladyjuno]

Please provide the create and insert query so that we can practice

[@ankitbansal6]

Rashi scripts are there in most the videos description box. Will fix if anyone is missing

[@Jessicaladyjuno]

@@ankitbansal6 can you let me know in which video you have imported the data or share the link I cant find it

[@ankitbansal6]

@@Jessicaladyjuno You can check in description box only for individual videos

[@D-InFluencer4u]

It is basically current vs previous year comparison

[@riyalmasla]

thank you

[@ankitbansal6]

You're welcome

[@jasleenkaur-cw2fw]

can you please provide the dataset?

[@harshupreti1526]

Why use sql when we have powerful tools like powerbi , tableau ? I am very curious and no way mean to disrespect . Please answer

[@aayushibirla2590]

SQL is used for storing and retreiving data from database. Power BI and tablaeu are the data visualization tools. Here we just load data and make visualizations from it.

[@afonsoosorio2099]

@Harsh, that is a valid and legitimate question. I had the very some concern but after doing some training in SQL, now I understand that it is a must skill to command in data analytics.
You are absolutely right there are powerful data analysis tools such as power query, power BI, Python and R.
We need SQL because is where we will often get plain raw data stored in multiple related tables (RDBMS).
So we have to query and retrieve the subset or dataset we are interested in for detailed analysis using those powerful data analysis tools.
Hardly you will get stored in spreadsheet and tabular format like Excel, or CSV and ready for analysis.
In a nutshell, we need SQL for extracting datasets from database and then transform and get loaded in a powerful data analysis tool.

[@mujeebalam7885]

SELECT employee_id, first_name, salary,
LAG(salary, 1) OVER (ORDER BY salary) AS previous_salary,
LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary
FROM employees;

[@apexemperor]

Hi... I am also probably looking for the same thing . I have a table which has Scd2 records and the last active records ends with high end dt something like 2200-01-01
Do u think this logic will work with some twigs ofcourse?
Came across your channel yesterday and there are lot of things to learn ❤

[@ankitbansal6]

What exactly do you want to achieve from your scd2 table ?