THIS right here is the masterpiece! I have read n number of articles of windows functions and still got confused but you so simply and beautifully explained the whole thing in under 18 minutes. Hats off to you, sir 🙌🙌🙌
Hi @@ankitbansal6 Sir, I have inserted two records ,matching the order_date with two existing records. INSERT [dbo].[int_orders] ([order_number], [order_date], [cust_id], [salesperson_id], [amount]) VALUES (80, CAST(N'1995-07-14' AS Date), 9, 3, 500); INSERT [dbo].[int_orders] ([order_number], [order_date], [cust_id], [salesperson_id], [amount]) VALUES (90, CAST(N'1998-02-03' AS Date), 2, 7, 1000); select salesperson_id ,order_number,order_date,amount, sum(amount) over (order by order_date) from int_orders order by order_date,salesperson_id ; I didnt use partition by clause in the above query,but why the first Amount value is 960 not 460. can you please explain how this works in the backend? SALESPERSON_ID ORDER_NUMBER ORDER_DATE AMOUNT TOTAL_SALES 1 30 1995-07-14 460 960 3 80 1995-07-14 500 960 2 10 1996-08-02 540 1,500 2 40 1998-01-29 2,400 3,900 7 50 1998-02-03 600 5,500 7 90 1998-02-03 1,000 5,500 7 60 1998-03-02 720 6,220 7 70 1998-05-06 150 6,370 8 20 1999-01-30 1,800 8,170
I have already learnt all these concepts in your namaste SQL Course in deep but also providing such informative videos on youtube , which is available in this playlist is really a good start for people want to understand all SQL Concepts from basics to advance.. Love this totally!!
Hi Ankit, I am unable to find the video in which you explained the difference between unbounded preceding and simple order by for rolling calaculations.Can you please attach the link?
SQL script: CREATE TABLE int_orders( order_number int NOT NULL, order_date date NOT NULL, cust_id int NOT NULL, salesperson_id int NOT NULL, amount float NOT NULL ) INSERT INTO int_orders VALUES (30, CAST(N'1995-07-14' AS Date), 9, 1, 460) INSERT INTO int_orders VALUES (10, CAST(N'1996-08-02' AS Date), 4, 2, 540); INSERT INTO int_orders VALUES (40, CAST(N'1998-01-29' AS Date), 7, 2, 2400); INSERT INTO int_orders VALUES (50, CAST(N'1998-02-03' AS Date), 6, 7, 600); INSERT INTO int_orders VALUES (60, CAST(N'1998-03-02' AS Date), 6, 7, 720); INSERT INTO int_orders VALUES (70, CAST(N'1998-05-06' AS Date), 9, 7, 150); INSERT INTO int_orders VALUES (20, CAST(N'1999-01-30' AS Date), 4, 8, 1800);
Hi Ankit, I have started watching your videos recently and I have started growing much more interest and curiosity and understanding and learning sql. Thank you so much! My sql knowledge gets increased with each and every video of yours. Do keep up the great work and keep us inspiring and educating. :)
Came across this video today and thought of checking to refresh concepts and i would say you explained every aspect and usecase of aggregation functions very well !
Thank you Sirji...What a masterpiece from your side for SQL....I request you that please make more videos on other imp topic also like Stored procedure , CTE, View, Indexes, etc....I'm desperately waiting to learn these topics from you....Thank you so much again GBU
It was really very helpful. You have clearly explained the concept of using aggregate function with window function and rows between _ preceding and _ following with the help of examples. Earlier, I had a lot of doubts in these concepts but now after watching your video, all my doubts are cleared. Thanks a lot!!
You are the best! every day i learn from you! yesterday i read your post on LinkedIn about that trick when you divide 2 integers today i learn how to make lead and lad with sum .THANK YOU!!!
Great video. I remember a few years backs when I was struggling to understand what the partition by and order by clauses do together and separately. This is a great refresher.
Hi Ankit, Thanks for posting this video I went through this and understood whole concept. I really appreciate you for explaining in simple way. Thanks Again!
Hi Ankit, just wanted to tell you that your videos are really helpful. You explain everything clearly without leaving anything out, and your content is informative as well as engaging. So glad, I came across your channel.
Hey Ankit, I am getting syntax error on 'order'. pls help. @7:59 select salesperson_id,order_number,order_date,amount ,sum(amount) over(partition by salesperson_id order by order_date) from int_orders; Incorrect syntax near 'order'.
Nice video, Could you please also make tutorials for - "Creating a list of dates/months/weeks between start date and end date (I'm trying to display active records over time using start and end date - Data scaffolding method)?
Hello Ankit I have one question. As you said we can use 1 preceding and 1 preceding function which works similar to Lag function. But let's say when there is too much data in the database and if we use the " 1 preceding and 1 preceding " function then how the system will behave in terms of performance. Can we use " 1 preceding and 1 preceding " for query optimisation???
Hi sir, what is the difference between unbounded preceding and simple order by for running totals? I couldn't find the answer in the video. Pls answer this
Hello Sir, If you can add sum(case when .. then .. else .. end) over (partition by .. order by .. rows between .. ) as .. then users will get end to end idea sir.This is from your previous video sir.Kindly append so that new users can get best out of this video.It's already best btw 😀.Just letting you know you can add more cheese.I am one of the first 100 subscribers of this channel.Ever since then i never looked into any other youtube channel or any other udemy course.If we know basic sql and looking for scenario based questions this channel is one stop solution place.Hope you like my comment sir.
Hello Sir, Can you explain how unbounded preceding and current row is different from just order by order_date. Query would give the same result I believe
Hello Sir.. Very informative video, thanks a lot. I shared your channel with my colleagues so that they can benefit from it. Could you please plan to share a video explaining learning path to follow to learn python for data engineers.
Hi sir and everyone, I am stuck with a query: suppose we have a transactions table with (timestamp, user_id, sender_id, amount) fields how to pick those transactions that happen within an hour by a particular user (suppose a user starts 1st trans at 8am, 2nd at 8.25am, 3rd at 9.05am, 4th at 9.50am, 5th at 10.45am... so all of these records must be picked starting from 8am to 10.45am) can anyone please suggest an approach to solve this... 🙏
@@ankitbansal6 we have to pick series of transactions uptil time between two consecutive transactions is less than one hour Eg - for a user he makes following transactions in a day 1. 8 am 2. 8.25 am 3. 9.10 am 4. 9.45 am 5. 12.35 pm 6. 3.20 pm 7. 4.45 pm 8. 5.15 pm 9. 5.50 pm 10. 10pm Out of above transactions we should be able to pick trans no 1,2,3,4 since in this series the time diff between consecutive trans is less than an hour, likewise we should also be able to pick trans no 7,8,9 here also diff between consecutive trans is less than an hour... Ps - if you could provide me anyway(discord server, mail, etc) to share the sample dataset and full query requirement for much better understanding ??
Brother I have a problem in sql. Sum function give threefold or fourfold result with join query. could you please spend time fom me? I have shown the problem many yutube expert but they are procrastinating only. Thanks
Hi Ankit, I have one question- If i have a table like below Id Name Sal 1 A. 1000 2 B. 1500 3 C. 2000 4 D. 2000 I want to fetch the max salary, but i want all the records with max salary and i need to fetch all the column data to be displayed.
Great content and clear explanation of aggregate/window functions. With using over (order by order_date), result set will be sorted whereas using over (order by order_date with rows between unbounded preceding and current row) result set will not be sorted in any order. Is that right ?
If you want to master SQL check out my zero to hero course here :
www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354
Hello Sir, the mentioned site is not reachable.. is it down?
@@itsme_notbatman it's working. Try again
Your videos helped me crack Data Engineering interview in one of the Big 4 companies. Thank You!!
Awesome. Congratulations 🎉
R u fresher
@@VikramKumar-dq2is, having a total of 2.5 yrs experience now.
Distribute sweets
@tapashidutta5136
Please help me to join the Data Engineering journey
Hello sir, I love your content. Instead of roaming here and there for SQL knowledge you have become one stop solution. Please keep on posting
Thank you 😊
THIS right here is the masterpiece! I have read n number of articles of windows functions and still got confused but you so simply and beautifully explained the whole thing in under 18 minutes. Hats off to you, sir 🙌🙌🙌
Thanks Pradyut. It means a lot 😊
Hi @@ankitbansal6 Sir,
I have inserted two records ,matching the order_date with two existing records.
INSERT [dbo].[int_orders] ([order_number], [order_date], [cust_id], [salesperson_id], [amount]) VALUES (80, CAST(N'1995-07-14' AS Date), 9, 3, 500);
INSERT [dbo].[int_orders] ([order_number], [order_date], [cust_id], [salesperson_id], [amount]) VALUES (90, CAST(N'1998-02-03' AS Date), 2, 7, 1000);
select salesperson_id ,order_number,order_date,amount,
sum(amount) over (order by order_date)
from int_orders
order by order_date,salesperson_id ;
I didnt use partition by clause in the above query,but why the first Amount value is 960 not 460.
can you please explain how this works in the backend?
SALESPERSON_ID ORDER_NUMBER ORDER_DATE AMOUNT TOTAL_SALES
1 30 1995-07-14 460 960
3 80 1995-07-14 500 960
2 10 1996-08-02 540 1,500
2 40 1998-01-29 2,400 3,900
7 50 1998-02-03 600 5,500
7 90 1998-02-03 1,000 5,500
7 60 1998-03-02 720 6,220
7 70 1998-05-06 150 6,370
8 20 1999-01-30 1,800 8,170
It is added based upon order_date and value added you ate expecting first value need to be 460 then u need use partition by salespersonid
I have already learnt all these concepts in your namaste SQL Course in deep but also providing such informative videos on youtube , which is available in this playlist is really a good start for people want to understand all SQL Concepts from basics to advance..
Love this totally!!
Hands down the best channel for analytics and SQL on youtube.
Thank you 😊
first complete video in the RUclips which covers all these aggregation.. Thank You
Thank you 😊
THANK YOU! This is one of the best sql explanation tutorial I have seen. It helps a lot!
Great to hear!
very nice way of explaining. Like the pace and keeps it interesting
I had gone through many videos...finally arrived to a FINEST and THE BEST..Thank you for your efforts..Please keep sharing knowledge.
Thank you 😊
Hi Ankit, I am unable to find the video in which you explained the difference between unbounded preceding and simple order by for rolling calaculations.Can you please attach the link?
Best Explanation on Window function, Thank you so much!!
SQL script:
CREATE TABLE int_orders(
order_number int NOT NULL,
order_date date NOT NULL,
cust_id int NOT NULL,
salesperson_id int NOT NULL,
amount float NOT NULL
)
INSERT INTO int_orders VALUES (30, CAST(N'1995-07-14' AS Date), 9, 1, 460)
INSERT INTO int_orders VALUES (10, CAST(N'1996-08-02' AS Date), 4, 2, 540);
INSERT INTO int_orders VALUES (40, CAST(N'1998-01-29' AS Date), 7, 2, 2400);
INSERT INTO int_orders VALUES (50, CAST(N'1998-02-03' AS Date), 6, 7, 600);
INSERT INTO int_orders VALUES (60, CAST(N'1998-03-02' AS Date), 6, 7, 720);
INSERT INTO int_orders VALUES (70, CAST(N'1998-05-06' AS Date), 9, 7, 150);
INSERT INTO int_orders VALUES (20, CAST(N'1999-01-30' AS Date), 4, 8, 1800);
Hi Ankit,
I have started watching your videos recently and I have started growing much more interest and curiosity and understanding and learning sql. Thank you so much! My sql knowledge gets increased with each and every video of yours. Do keep up the great work and keep us inspiring and educating. :)
Thank you
Came across this video today and thought of checking to refresh concepts and i would say you explained every aspect and usecase of aggregation functions very well !
Awesome, thank you!
Excellent Explanation... Wat a Class Sir ... Brilliant ...😍😍🤩🤩🤩
Keep watching💪
Awesome video! Cleared all my doubts around aggregation functions in SQL.
One of the best few videos on Window Functions!
Or I must say 'The Best'...
Glad it was helpful!
Thank you Sirji...What a masterpiece from your side for SQL....I request you that please make more videos on other imp topic also like Stored procedure , CTE, View, Indexes, etc....I'm desperately waiting to learn these topics from you....Thank you so much again GBU
Thank you. Sure :)
Great and Excellent teaching. Thank you for sharing. I have learned a lot.
I am loving your videos now. Please create a video detailing about all the types of indexes. Really appreciate your efforts. Thanks
Sure will do 🙂
Much appreciated. You are really inspiring to learn SQL interestingly. God bless you and all the best.
Happy to hear that!
masterclass to clear aggregation concepts once and for all. Thanks Ankit.
Keep watching🙏
Just awesome , as it says Zero to Hero , beautifully covered different aspects of a confusing topic elegantly
Glad to hear that
Maza agya, Thank you Ankit. Knowing how to work with lead and lag fn w/o actually using them, instead using preceding/following is awesome.
My pleasure
It was really very helpful.
You have clearly explained the concept of using aggregate function with window function and rows between _ preceding and _ following with the help of examples. Earlier, I had a lot of doubts in these concepts but now after watching your video, all my doubts are cleared.
Thanks a lot!!
Thank you 😊
You are the best! every day i learn from you! yesterday i read your post on LinkedIn about that trick when you divide 2 integers today i learn how to make lead and lad with sum .THANK YOU!!!
I am delighted ☺️
Great video. I remember a few years backs when I was struggling to understand what the partition by and order by clauses do together and separately. This is a great refresher.
😊
Just a masterclass, the way you teach is simply outstanding ....Jem of a person :)
Glad you enjoyed it😊
Loved it! you nailed it. Keep growing and keep sharing! 😍
Thank you so much 😄
Excellent content bro ,
Others who teach dont even cover so much depth topics..
keep gng bro...
I had requested this Video, Thank you so much Ankit 🙂….🙏🏻
Thanks Aditya. It was much needed 🙂
Thanks much Ankit..i have my interview today.. and your videos are one point stop for me .. thanks again
Best wishes 🙂
Your way of approach and explanation was excellent.
Glad it was helpful!
Very Good explanation on each level. Excellent Contents.
You sir are an amazing teacher!
Glad you think so!
Fresh morning and new learning...awesome video
Thank you 😊
Hi Ankit, Thanks for posting this video I went through this and understood whole concept. I really appreciate you for explaining in simple way. Thanks Again!
You're most welcome
Excellent, you made SQL easy to learn.
You are really good teacher. Keep it up 👍
Thank you 😊
Great....very informational video...thanks sir 👍
Good to know 😊
Amazing work Ankit Bhai, keep making these suprb videos
Thank you 😊
Thanks for your videos. I have learned a lot. I believe it is not easy to make videos. Thanks for your time.
Very well explained. Thanks for sharing amazing content as you always do.
My pleasure!
Hi Ankit, just wanted to tell you that your videos are really helpful. You explain everything clearly without leaving anything out, and your content is informative as well as engaging. So glad, I came across your channel.
Good to hear that ☺️
Hello Ankit, Really mind blowing explanation along with simple content. Very helpful.👌👌
Thank you 😊
Very well...
Really learning....
Hey Ankit,
I am getting syntax error on 'order'. pls help.
@7:59
select
salesperson_id,order_number,order_date,amount
,sum(amount) over(partition by salesperson_id order by order_date)
from int_orders;
Incorrect syntax near 'order'.
Very Helpful. Thank you very much!
Hi Ankit, could you pls do one video on table partitioning??
Nice way of teaching🙂
Keep watching
Truly insightful. Thanks for sharing Ankit.
Thanks Sumeet 🙏
Amazing. Thank you so much
You doing great work Ankit. Keep posting and Keep educating. It is really helping many of us. Thanks!
Thank you 😊
Thank you sir for sharing such insights getting to know all the things related why and how it is
👍👏
🙏
Nice video, Could you please also make tutorials for - "Creating a list of dates/months/weeks between start date and end date (I'm trying to display active records over time using start and end date - Data scaffolding method)?
@ankitbansal6 yes , please make video for above query
Very informative , explanation is easy to understand!!
Thanks bro 😊
This is awesome.. 😎
i asked my trainer but this solved my problem!! Thanks sir
Most welcome
Awesome Bro.. !!!
Sir. Great Video. You said that you will tell the differences between normal running sum and unbounded preceding and current row.
Check this ruclips.net/video/_GkPa7Iy2Fk/видео.html
@@ankitbansal6 Thank You Sir
Can we seen something realted to performance tunings/ indexes in Tsql
Sure.
Hello Ankit
I have one question.
As you said we can use 1 preceding and 1 preceding function which works similar to Lag function. But let's say when there is too much data in the database and if we use the " 1 preceding and 1 preceding " function then how the system will behave in terms of performance. Can we use " 1 preceding and 1 preceding " for query optimisation???
Lead/lag is meant for that then use that. It is just a work around.
@@ankitbansal6 Got It. Thank you 😊 🙏
Hi sir, what is the difference between unbounded preceding and simple order by for running totals? I couldn't find the answer in the video. Pls answer this
When creating the tables
Why the columns and insert datais kept in the braces
Great explanation 👍
Why it gives running sum only when we use order by?
i just love the way you teach
Thank you 😊
Hello Sir,
If you can add sum(case when .. then .. else .. end) over (partition by .. order by .. rows between .. ) as .. then users will get end to end idea sir.This is from your previous video sir.Kindly append so that new users can get best out of this video.It's already best btw 😀.Just letting you know you can add more cheese.I am one of the first 100 subscribers of this channel.Ever since then i never looked into any other youtube channel or any other udemy course.If we know basic sql and looking for scenario based questions this channel is one stop solution place.Hope you like my comment sir.
It's there in the video 🙂
Thank you Ankit your explanation is so simple
Thank you 😊
god bless you Ankit
Thankyou so much sir for providing these videos on practical sql, please make videos on basics to advanced of CTE/Stores Procedures/Views also.
Sure
Awesome video❤
Thanks so much Ankit. Can we have next video on joins? . It is much needed and would help in demystifying this topic.
I have a video ruclips.net/video/fV8IYHOlONg/видео.html
Please add video on partition by multiple columns
This is Amazing 👏
Thanks Shubham 😊
my god! awesome content
Thank you 😊
My all doubts solved by this video
Awesome👏
Hello Sir,
Can you explain how unbounded preceding and current row is different from just order by order_date. Query would give the same result I believe
Watch this
ruclips.net/video/_GkPa7Iy2Fk/видео.html
Understood.. thank you sir 🙏
Hi sir can you create videos from scratch for beginners
As usual you nailed it .. can you create similar video for windows function zero to Hero …
Sure
After watching your videos, SQL looks so much easier.
Glad to hear that!
Is there any all video playly video of sql
Complex SQL Questions for Interview Preparation: ruclips.net/p/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb
Thanks Ankit....
which database you have used in this tutorial Ankit ji
SQL server
excellent
Thank you 😊
Amazing video. Ankit can you put all videos in your created playlist. It is covienent to track. This video I am not seeing in any playlist
I think it should be there in tips and tricks. I will check anyways.
Fantastic.
Thank you! Cheers!
Awesome
Hello Sir.. Very informative video, thanks a lot. I shared your channel with my colleagues so that they can benefit from it. Could you please plan to share a video explaining learning path to follow to learn python for data engineers.
Thank you so much. Sure will create the road map.
Hi sir and everyone,
I am stuck with a query:
suppose we have a transactions table with (timestamp, user_id, sender_id, amount) fields
how to pick those transactions that happen within an hour by a particular user
(suppose a user starts 1st trans at 8am, 2nd at 8.25am, 3rd at 9.05am, 4th at 9.50am, 5th at 10.45am...
so all of these records must be picked starting from 8am to 10.45am)
can anyone please suggest an approach to solve this... 🙏
What do you mean by within an hour. You are saying all the transaction should be picked up but they are accross multiple hours
@@ankitbansal6 we have to pick series of transactions uptil time between two consecutive transactions is less than one hour
Eg - for a user he makes following transactions in a day
1. 8 am
2. 8.25 am
3. 9.10 am
4. 9.45 am
5. 12.35 pm
6. 3.20 pm
7. 4.45 pm
8. 5.15 pm
9. 5.50 pm
10. 10pm
Out of above transactions we should be able to pick trans no 1,2,3,4 since in this series the time diff between consecutive trans is less than an hour, likewise we should also be able to pick trans no 7,8,9 here also diff between consecutive trans is less than an hour...
Ps - if you could provide me anyway(discord server, mail, etc) to share the sample dataset and full query requirement for much better understanding ??
@@shubhammajhi7493 you can send me an email ankitbansal1988@gmail.com
@@ankitbansal6 okay thanks 🙂
Brother I have a problem in sql. Sum function give threefold or fourfold result with join query. could you please spend time fom me? I have shown the problem many yutube expert but they are procrastinating only. Thanks
Send me problem on sql.namaste@gmail.com
Yes Brother. I am sharing the query.
@@ankitbansal6 Brother I have sent a demo data and query to mail. Please give me solution on it.
@@tazulislam2698 I have not got
@@ankitbansal6 Please check mail again. I have resent.
Hi Ankit,
I have one question-
If i have a table like below
Id Name Sal
1 A. 1000
2 B. 1500
3 C. 2000
4 D. 2000
I want to fetch the max salary, but i want all the records with max salary and i need to fetch all the column data to be displayed.
Use Group by
select*,max(sal) over() as max_sal
from table_test;
Great content and clear explanation of aggregate/window functions.
With using over (order by order_date), result set will be sorted whereas using over (order by order_date with rows between unbounded preceding and current row) result set will not be sorted in any order. Is that right ?
It would have been nice if you had explained with examples.
Share sql playlist please
Complex SQL Questions for Interview Preparation: ruclips.net/p/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb
hi ankit 17/32
video completed
Keep watching
namasthey python course student, thanks for the SQL content, I appreciate your efforts didn't complete Python (start as well).
CFBR
🙏
Sir Hindi me vedio banaye please and each step explain why you are doing in hindi
kuch samjh nehi aya bhai please try to explain why instead of saying the same thing in english