Hello, fellow viewers. If you will study window functions and practice them again and again until you have them cold, you'll be far ahead of most of your co-workers and fellow job seekers. I've been seeing numerous job postings requiring the ability to create SQL table joins, but never the ability to use SQL Server window functions - so far, at least. I can only assume that the hiring managers don't want to scare off people, or that they are not familiar with the topic. ** (Your thoughts are welcome - especially you, Brad.) It does take a while to sink into my thick skull, but with repetition I am making progress. It takes a while to soften up the rock, I suppose. Brad's step-wise instructional approach is excellent, and this channel - and specifically this series - is the best online training that I have seen for SQL Server window functions. 👍 ** Similarly, job postings often specify Excel VLOOKUP, and never specify Excel XLOOKUP, or even INDEX MATCH. Obviously, a lot of time is being wasted out there by not using the newer capabilities of software. Inertia?
@@BeardedDevData One of the best content on CZcams for Windows functions. I am going to go over all the videos because its always something new I learned from your videos.
@@BeardedDevData I scored a 770! The video definitely helped understanding better. The exam was pretty hard for me, you've to sign an NDA before the exam so I've to lookup what I can say and not
Congratulations, yes, don’t reveal any questions. I meant more was it what you expected? Did you find some topics more difficult than others? Would you recommended it to people? Let me know if you want a shoutout.
Hi BeardedDev, very helpful video. Can you explain what will be the result of lag and lead if the column of order by (sales_date in this case) has the same value for 2 rows?
that's a really good question, let's say we have the following data, id, date, amount, 1, 21/05/2021, 10.00, 2, 21/05/2021, 20.00, 3, 21/05/2021, 30.00, 4, 21/05/2021, 40.00, if I run the following query SELECT *, LAG(Amount) OVER(ORDER BY Date) AS R FROM [table] then what we end up with is a nondeterministic result meaning each time we run the query we may get different results but they would still be considered correct, when I ran the query I got the following id, date, amount, r, 1, 21/05/2021, 10.00, NULL, 2, 21/05/2021, 20.00, 10.00, 3, 21/05/2021, 30.00, 20.00, 4, 21/05/2021, 40.00, 30.00 but I could have got different results and it would have been correct, there are two important things, lag will not apply grouping, you will have to do that yourself and you should apply deterministic ordering, in this case you can use a tiebreaker like id, hopefully that helps
Thanks for the video on lag and lead. How to use function to see the difference between the succeeding value using lag? If you wanted to see difference between the sales amount of each row.
I am a web developer with 3 years of experience. Days ago I got chosen to a Jr BI Job consisting mainly on SQL and reports. Tell me your experience and opinión about it please., I read you were also a web developer years ago before transitioning into BI
Men this is so helpful, but can you tell me how the heck you could create that window frame. I am 3 days trying to solve it but i cant. Could you help me please (im using mysql)
You can use without a partition by but you must specify an order by however you can overcome this by using the following statement lag(col) over(order by (select null)), but usually it’s best to order by a column that means something when working with lag and lead.
Hi can you explain me the query if I had to use with self join without lag ? I will be joining on customer id and mentioned t1.salesdate < t2.salesdate?
The problem with that is you have a one to many join, the max date in the table will be joined to all other dates. To make this one to one you would also need to have a greater than, if you don’t have another date column to use you could use the DATEADD function. This would assume you have consecutive dates for every customer though otherwise it would be a lot more difficult.
Hi, Thanks for the AWESOME tutorial! Howver, I am having a bit of trouble. I am able to bring back LIST successfully, but I need to bring back the prev/next for one ItemID, but I cannot get anything but NULL values (even though I can confirm that the previous and next record, have a value) What am i missing? Here is my code... SELECT ItemID , ItemModelID , LAG(ItemID) OVER(ORDER BY ItemYear) AS PrevValue , ItemID , LEAD(ItemID) OVER(ORDER BY ItemYear) AS NextValue FROM dbo.item_info WHERE ItemID = 320696 Thanks for any help!
Hi, thanks for the question, I have noticed you have a WHERE clause in your query and it filters on an ID column so I’m guessing only returns one row. As window functions are part of the SELECT they are calculated after filtering is applied therefore correct to return NULL because there is nothing above or below.
@@BeardedDevData That makes perfect sense :) So is there any way to get a single record response, with the prev / next included? I am using this on a ASP page, and yesterday as a solution I used an IF statement to CHOOSE the ItemID from the record set on the actual page instead of the WHERE in the SQL, so that worked. But I feel like it is not very efficient because on some pages I will be pulling back hundreds of records in the background just to get the prev/next for the single item.
You will need to first run the query without the WHERE clause and then apply the filter to the result set. Depending on the size of the data I would probably put it within a CTE.
This is a bit outside my experience level as I am transitioning to more SQL stored procedures using less ASP... I appreciate your quick replies! Would you be able to show me a bit of an example using my previous code? (apologies if this is asking too much)
That’s okay, I’m happy to help, using a CTE the query would be: WITH CTE AS (SELECT ItemID, ItemModelID, LAG(ItemID) OVER(ORDER BY ItemYear) AS PrevValue, LEAD(ItemID) OVER(ORDER BY ItemYear) AS NextValue FROM dbo.item_info) SELECT ItemID, ItemModelID, PrevValue, NextValue FROM CTE WHERE ItemID = 320696, apologies for the spacing, I’m on the CZcams app and doesn’t always work correctly.
Great stuff, BeardedDev. Huge fan of your channel. Kudos.
you've got a knack for simplifying things; keeping them interesting, concise and to the point. Brilliant !
Hello, fellow viewers.
If you will study window functions and practice them again and again until you have them cold, you'll be far ahead of most of your co-workers and fellow job seekers. I've been seeing numerous job postings requiring the ability to create SQL table joins, but never the ability to use SQL Server window functions - so far, at least. I can only assume that the hiring managers don't want to scare off people, or that they are not familiar with the topic. ** (Your thoughts are welcome - especially you, Brad.) It does take a while to sink into my thick skull, but with repetition I am making progress. It takes a while to soften up the rock, I suppose.
Brad's step-wise instructional approach is excellent, and this channel - and specifically this series - is the best online training that I have seen for SQL Server window functions. 👍
** Similarly, job postings often specify Excel VLOOKUP, and never specify Excel XLOOKUP, or even INDEX MATCH. Obviously, a lot of time is being wasted out there by not using the newer capabilities of software. Inertia?
Great video BeardedDev! Very clear and concise!
Thanks so much.
Learning SQL, truly helped me understand Lag and Lead. Thank you!
Thanks so much.
Such a helpful video. Thank you!
Clean and clear❤️
Excellent work! Things are just crystal clear after watching this. Keep posting more.
Thank you so much mate, this helped me a lot when I was trying to calculate the count days since the last price change. Greetings from Chile!
Welcome to the channel, glad I can help.
Thanks alot from bottom of my heart
Never heard of those functions! Thank you!
Thank you for an awesome explanation, windows function has never been so clear too me like this. Thanks a lot.
Thanks for the feedback, glad the video helped.
Thank you so much for this great video!
No problem, glad you enjoyed it 👍.
Amazing, thanks!
Great video series! Keep the good work going
Brilliant explanation and easy to follow
Thank you so much!! for good content
very helpful video and luxury of having table prepped
subbed and will follow thanks :)
Great stuff, thanks again!
Appreciate your structured and detailed approach. Could you plz make a video calculating ratios in windows, using self joins
Amazing content !!! Please do keep creating SQL videos like this.
Don't worry, I'm currently working on my Power BI certification then I will be back posting videos regularly.
@@BeardedDevData One of the best content on CZcams for Windows functions. I am going to go over all the videos because its always something new I learned from your videos.
@@BeardedDevData Please let us know HOW you are studying for your Power BI certification. Thank you.
Thank you so much, greetings from Colombia.
Welcome Colombia.
You are an awesome teacher.
Thanks for the feedback. Let me know if there are any particular areas you are interested in.
I am watching this one day before my 70-461 and it's so much clearer now
Great stuff, good luck with the certification
@@BeardedDevData thank you!
Let me know how you get on and what you thought of the exam, would be good to give you a shoutout on the channel.
@@BeardedDevData I scored a 770! The video definitely helped understanding better. The exam was pretty hard for me, you've to sign an NDA before the exam so I've to lookup what I can say and not
Congratulations, yes, don’t reveal any questions. I meant more was it what you expected? Did you find some topics more difficult than others? Would you recommended it to people? Let me know if you want a shoutout.
Great
thants a ton for increasing font size
perfect
Great! Thank you
really the best videos for studying analytical functions.... can u also make videos on cube and roll up functions?
Yes, I will put them on my list.
Hi BeardedDev, very helpful video. Can you explain what will be the result of lag and lead if the column of order by (sales_date in this case) has the same value for 2 rows?
that's a really good question, let's say we have the following data, id, date, amount, 1, 21/05/2021, 10.00, 2, 21/05/2021, 20.00, 3, 21/05/2021, 30.00, 4, 21/05/2021, 40.00, if I run the following query SELECT *, LAG(Amount) OVER(ORDER BY Date) AS R FROM [table] then what we end up with is a nondeterministic result meaning each time we run the query we may get different results but they would still be considered correct, when I ran the query I got the following id, date, amount, r, 1, 21/05/2021, 10.00, NULL, 2, 21/05/2021, 20.00, 10.00, 3, 21/05/2021, 30.00, 20.00, 4, 21/05/2021, 40.00, 30.00 but I could have got different results and it would have been correct, there are two important things, lag will not apply grouping, you will have to do that yourself and you should apply deterministic ordering, in this case you can use a tiebreaker like id, hopefully that helps
@@BeardedDevData So basically you're saying to have multiple order by fields in such cases to make the sorting unique. Got it.. Thanks!
Thanks for the video on lag and lead. How to use function to see the difference between the succeeding value using lag?
If you wanted to see difference between the sales amount of each row.
Hi lizbethl4, that's a great question, I will post a video shortly on how to calculate the difference.
Hi lizbethl4, I have just uploaded a video on calculating running totals: czcams.com/video/qyjxSzVE5eU/video.html
I am a web developer with 3 years of experience. Days ago I got chosen to a Jr BI Job consisting mainly on SQL and reports. Tell me your experience and opinión about it please., I read you were also a web developer years ago before transitioning into BI
May I ask where you read that I used to be a Web Developer?
Can I do something like sale_amount - LAG(sales_amount)?
Absolutely, commonly done for period over previous period calculations e.g. month over previous month either as a value or a percentage.
@@BeardedDevData I watched your video covering this after I posted this question.
thank you!!
Men this is so helpful, but can you tell me how the heck you could create that window frame. I am 3 days trying to solve it but i cant. Could you help me please (im using mysql)
It should work in mysql without a problem, can you let me know what query tool you are using and the query you are trying to run.
Please if you can share an video on list_aggregate function please.. Your videos are very helpful in understanding
Thanks! Lag and lead only works with windowing functions? Is there a way to use them on single col other than using ot like 'lag(column) over()'
You can use without a partition by but you must specify an order by however you can overcome this by using the following statement lag(col) over(order by (select null)), but usually it’s best to order by a column that means something when working with lag and lead.
@@BeardedDevData Thanks! Amazing tutorial btw
Thank you!
Is there a video of how to calculate Quartile (1 to 4) ?
Yes, you can have a look at my video on NTILE, m.czcams.com/video/DS8QZ2HEqvk/video.html
@@BeardedDevData thank you :)
Great work :D , why dont you teach at Udemy or other paid portals too ?
I have thought about it and it might be something I pursue next year.
@@BeardedDevData That will be great , thanks for helping new comers :)
Hi can you explain me the query if I had to use with self join without lag ? I will be joining on customer id and mentioned t1.salesdate < t2.salesdate?
Hi Nishant, you have explained how to perform the self join on customerid and saledate, can you give me a bit more detail on what you are asking?
Hi I’m trying to run the same query that you’ve done with lag using self join
The problem with that is you have a one to many join, the max date in the table will be joined to all other dates. To make this one to one you would also need to have a greater than, if you don’t have another date column to use you could use the DATEADD function. This would assume you have consecutive dates for every customer though otherwise it would be a lot more difficult.
thanks :) my exam is tomorrow
Good luck for tomorrow
does this work for mysql on MacBook?
It won't matter that you are using a Mac Book. They should be supported on mysql but if you're using quite an old version they may not work.
Hi, Thanks for the AWESOME tutorial! Howver, I am having a bit of trouble.
I am able to bring back LIST successfully, but I need to bring back the prev/next for one ItemID, but I cannot get anything but NULL values (even though I can confirm that the previous and next record, have a value)
What am i missing? Here is my code...
SELECT
ItemID
, ItemModelID
, LAG(ItemID) OVER(ORDER BY ItemYear) AS PrevValue
, ItemID
, LEAD(ItemID) OVER(ORDER BY ItemYear) AS NextValue
FROM dbo.item_info
WHERE ItemID = 320696
Thanks for any help!
Hi, thanks for the question, I have noticed you have a WHERE clause in your query and it filters on an ID column so I’m guessing only returns one row. As window functions are part of the SELECT they are calculated after filtering is applied therefore correct to return NULL because there is nothing above or below.
@@BeardedDevData That makes perfect sense :) So is there any way to get a single record response, with the prev / next included?
I am using this on a ASP page, and yesterday as a solution I used an IF statement to CHOOSE the ItemID from the record set on the actual page instead of the WHERE in the SQL, so that worked. But I feel like it is not very efficient because on some pages I will be pulling back hundreds of records in the background just to get the prev/next for the single item.
You will need to first run the query without the WHERE clause and then apply the filter to the result set. Depending on the size of the data I would probably put it within a CTE.
This is a bit outside my experience level as I am transitioning to more SQL stored procedures using less ASP... I appreciate your quick replies! Would you be able to show me a bit of an example using my previous code? (apologies if this is asking too much)
That’s okay, I’m happy to help, using a CTE the query would be: WITH CTE AS (SELECT ItemID, ItemModelID, LAG(ItemID) OVER(ORDER BY ItemYear) AS PrevValue, LEAD(ItemID) OVER(ORDER BY ItemYear) AS NextValue FROM dbo.item_info) SELECT ItemID, ItemModelID, PrevValue, NextValue FROM CTE WHERE ItemID = 320696, apologies for the spacing, I’m on the CZcams app and doesn’t always work correctly.