Bhai sach bta rha hu....ekdm izzat se padha aur smjha de rhe ho ❤
@RishabhMishraOfficial8 ай бұрын
Glad you liked it ✅️
@official_mahendra22111 ай бұрын
Rishab bhai, maine 80% sql apke videos se sikha hai, thanks .........thanks lot ..........lots of love !
@RishabhMishraOfficial11 ай бұрын
Glad it was helpful ✅️
@Erengaming53513 күн бұрын
Sir window function jis tarah se aapne samjhaya hai merko mere to dimag mein chap gaya thank you sir ye sql serious follow karke bhot kuch sikhne ko mila thanks 🙏🙏🙏❤️❤️
@RishabhMishraOfficial12 күн бұрын
Glad you liked ✅️
@tejasthete7007 Жыл бұрын
It is really help full for me and i found roll model for data analyst thanks bro for enhance our skill, please Rishabh make a vedio on real time experience of data analyst project so that we can get clarity please.
@RishabhMishraOfficial Жыл бұрын
Glad you liked it, thanks a lot. Yes i will make a complete video on how we analyze data using Python from scratch
@tejasthete7007 Жыл бұрын
Thanks we will wait your movement
@labdhibavishi24304 ай бұрын
Did you get a job?@@tejasthete7007
@infotech53704 ай бұрын
When we are using order by new_id , following is the output I am getting in contrast with the output shown in the video new_id new_cat Total 100 Agni 100 200 Agni 300 500 Dharti 500 700 Dharti 1200 200 Vayu 200 300 Vayu 500 500 Vayu 1000
@mdsarwaralam348515 күн бұрын
Removing the ORDER BY clause from the window functions in your query, give same output as in pdf Kindly, see below query- SELECT new_id, new_cat, SUM(new_id) OVER (PARTITION BY new_cat) AS "Total", AVG(new_id) OVER (PARTITION BY new_cat) AS "Average", COUNT(new_id) OVER (PARTITION BY new_cat) AS "Count", MIN(new_id) OVER (PARTITION BY new_cat) AS "Min", MAX(new_id) OVER (PARTITION BY new_cat) AS "Max" FROM test_data; or use- "ORDER BY new_cat" instead of "ORDER BY new_id" SELECT new_id, new_cat, SUM(new_id) OVER (PARTITION BY new_cat ORDER BY new_cat) AS "Total", AVG(new_id) OVER (PARTITION BY new_cat ORDER BY new_cat) AS "Average", COUNT(new_id) OVER (PARTITION BY new_cat ORDER BY new_cat) AS "Count", MIN(new_id) OVER (PARTITION BY new_cat ORDER BY new_cat) AS "Min", MAX(new_id) OVER (PARTITION BY new_cat ORDER BY new_cat) AS "Max" FROM test_data
@randomvideo78915 күн бұрын
Thnx @@mdsarwaralam3485
@pkabc34545 ай бұрын
in aggregate fiction ex:- after order by add new_cat not include new_id ,Because new_id result are 100,300 and new_cat result are 300,300
@vishalkumar-hi7wi2 ай бұрын
thx bro
@GovindBisht-le4xxАй бұрын
same problem bro . if you get any solution please share with me
@sadiqahassan3 ай бұрын
Really liked your teaching style Mr. Rishabh Mishra Sir
@aqawewewe4 ай бұрын
Really liked your teaching style bro .. keep it up
@Ap.underscore Жыл бұрын
To summarize, "Rank" leaves gaps if there are ties (same scores), while "Dense Rank" doesn't leave any gaps and keeps counting without skipping any numbers when there are ties.
@RishabhMishraOfficial Жыл бұрын
Awesome 👌
@poojamahajan187817 күн бұрын
Really like your Teaching Rishab keep it up.
@RishabhMishraOfficial16 күн бұрын
Glad you liked ✅️
@saeelsalvi17393 күн бұрын
00:01 Windows functions apply aggregate, ranking, or analytic functions on a window 01:42 SQL window functions allow for aggregate operations over a set of rows. 05:03 SQL window functions summarize data for analysis. 06:41 SQL Window Functions can be used to partition and order data in SQL queries. 10:14 SQL window functions explained with examples 11:58 Understanding SQL Window Functions - RANK and DENSE RANK 15:13 Understanding SQL Window Functions - RANK, DENSE RANK, LEAD/LAG 16:50 Understanding the functionality of lead function in SQL Window Function 19:48 Understanding the functionalities of RANK, DENSE RANK, ROW NUMBER, LEAD, and LAG in SQL Window Functions 21:14 Understanding lead, lag, and window functions for interview preparation
@Userlearner22298 ай бұрын
"IZZAT SE CHALTA HAI" 😄😄😄 THANK YOU Rishabh Bhaiya , lot of thanks
@Adityasahu003927 күн бұрын
clean and simple, I like the way you explained everything.
@RishabhMishraOfficial25 күн бұрын
Glad you liked it! ✅️
@Bobby-mf6fw28 күн бұрын
Best sql videos on youtube.
@RishabhMishraOfficial28 күн бұрын
Glad you liked it ✅️
@MubarakAli-qs9qq2 күн бұрын
Sir u explained very nicely
@RishabhMishraOfficialКүн бұрын
Glad you liked ✅️
@manavjain96489 ай бұрын
great video especially for interviews
@RishabhMishraOfficial9 ай бұрын
Glad you like them! 👍
@ReviewwithAshish-qc8xb22 күн бұрын
Rishabh bhai thank you so much. I am preparing for data analyst interview and your video's are helping me a lot to understand the SQL more easily
@baki_12052 ай бұрын
Superb @Rishabh Mishra sir❤
@arhaam_valАй бұрын
Thanks... Really helpful!
@RishabhMishraOfficialАй бұрын
Glad it helped! ✅️
@Divyanshu_Arts3 күн бұрын
Thanku soo much sir ❤
@RishabhMishraOfficial3 күн бұрын
Glad you liked ✅️
@automationwithwasi Жыл бұрын
Thanks for the videos, very useful!! I got the answer to the question you asked without using the method. First, we will get the columns with lead and lag then apply the lead and lag again on those columns.🙃
@RishabhMishraOfficial Жыл бұрын
Awesome, keep learning n keep growing 🚀
@Vaishnavikaithwas7862 ай бұрын
Thanks for these superb explaination sir..... I will never forget these topic bcoz of ur wonderful teaching
@RishabhMishraOfficial2 ай бұрын
Glad you liked ✅️
@Pritamgogreen10 ай бұрын
Thank you so much for the clear and fluid explanation. My search for a good explanation of "window function" ends here.
@RishabhMishraOfficial10 ай бұрын
Glad it helped! ✅️
@annusharma96224 күн бұрын
Thanks a lot sir ☺️
@RishabhMishraOfficial3 күн бұрын
Glad you liked ✅️
@sanketsarolkar94446 ай бұрын
Bhai tumhare videos sabse best aur easy way mein samjh mein ate hai….❤
@RishabhMishraOfficial6 ай бұрын
Glad you liked ✅️
@sudiptanath4103 Жыл бұрын
Thank you rishabh...
@yasink18 Жыл бұрын
Sir you taught in a very simple manner.. Love your tutorial
@RishabhMishraOfficial Жыл бұрын
Glad you liked ✅️
@shubhverma4579 Жыл бұрын
sir! jab kuchh search krta hu sql ke liye hindi mein or apki shakal dikh jati hai tho search krna chhod deta hu kynki dil se ek hi awaz aati hai,"Bhai! hai tho apna." #Thanks_for_teach_us_sir
@princekhunt1357911 күн бұрын
good content
@RishabhMishraOfficial7 күн бұрын
Glad you liked ✅️
@himanshushukla922910 ай бұрын
bht hi awesome bhai.. perfectly explained thanks
@RishabhMishraOfficial9 ай бұрын
Glad it was helpful ✅️
@nehamegha3384 Жыл бұрын
Thanku so much sir for the tutorial 🤩🤩🤩🤩 crystal clear explanation 😍😍😍😍 searching everywhere but finally got the best tutorial thanks alot
@RishabhMishraOfficial Жыл бұрын
Glad it was helpful ✅️
@sandhyapatro8688 Жыл бұрын
Very well explained Rishabh.
@RishabhMishraOfficial Жыл бұрын
Glad you liked ✅️
@MuhammadSaleem-id2ye2 ай бұрын
Thanks and Love from Pakistan
@bhaveshbharti4782 Жыл бұрын
thankyou so much kitna acha padhaya hai
@RishabhMishraOfficial Жыл бұрын
Glad you liked it ✅️
@george8687 Жыл бұрын
Really nice video mate! You got a new subscriber.
@RishabhMishraOfficial Жыл бұрын
Glad you liked it n Welcome aboard! 🚀
@aniketbanerjee3463 Жыл бұрын
very easy to understand, nice video.
@RishabhMishraOfficial Жыл бұрын
Glad it was helpful! ✅️
@MamataHati-e3mАй бұрын
Thanks for this vdo
@RishabhMishraOfficialАй бұрын
Glad you liked ✅️
@sonalsingh4151 Жыл бұрын
Thanks for explaining windows fun..really helpfull
@RishabhMishraOfficial Жыл бұрын
Glad you liked it 👍
@jjk43511 ай бұрын
Been looking for it. In a world full of free resources, found this of great help ❤.thank you
@RishabhMishraOfficial11 ай бұрын
Glad I could help!
@yashaggarwal7701 Жыл бұрын
thanks bro for this wonderful explaination of window function
@RishabhMishraOfficial Жыл бұрын
Glad you liked it ✅️
@ronakpatel81857 ай бұрын
In 6:57 the output is different from the expected output, so just remove the order by clause and u are good to go
@swarnimamishra70076 ай бұрын
or use ORDER BY new_cat instead of new_id
@eddybear94365 ай бұрын
@@swarnimamishra7007exactly. Ab sahi aa tha hai. Otherwise to Sum aur average me first value of I'd as it is print ho rhi thi
@shristikumari8828 Жыл бұрын
Simplest explaination ever.🌹
@RishabhMishraOfficial Жыл бұрын
Glad you liked it
@introvertwhiz-ll2ip26 күн бұрын
Explanation superb, make him the professor of computer science Department.
@RishabhMishraOfficial25 күн бұрын
Glad you liked the content ✅️
@andythomson22275 ай бұрын
Excellent teaching skills❤
@RishabhMishraOfficial5 ай бұрын
Glad you liked ✅️
@anshuljain310528 күн бұрын
i have and interview and it helped me a lot
@RishabhMishraOfficial28 күн бұрын
Awesome, keep learning n keep growing 🚀
@ArmaanKing173 ай бұрын
Very well explained ❤
@RishabhMishraOfficial3 ай бұрын
Glad you liked ✅️
@vineetanand5341 Жыл бұрын
Good Video. Please share share more videos with real Time example. Also 1 query, For functions like FIRST_VALUE, KST_VALUE, LEAD & LAG in select clause we can have more col's & function can be applied on any one col or more col also?
@varshamate7566 Жыл бұрын
Thanku so much,I understand very well
@sharma-vasundhara10 ай бұрын
Made it very simple!
@RishabhMishraOfficial10 ай бұрын
Glad you liked ✅️
@Product2501210 ай бұрын
At first a great thanks for ur explanation, Ekk Doubt h Hame kb smjh ayega ki window function use krna h ya normal sql query lgana h ......Plzzz Reply
@RishabhMishraOfficial10 ай бұрын
Practice n you will understand
@ullua5671Ай бұрын
Best video on SQL
@DPMotivationDhruvPanditАй бұрын
Bahut badiya tutorial banaya hai rishav bhai aapne ❤
@adwaydandwate3 ай бұрын
Hi Rishabh, thanks for your video. I see there is one discrepancy in output of first example: "Partition BY new_cat Order BY "new_id". This doesn't give the output as shown in the picture in the video. It has to be ordered by "new_cat" instead of "new_id", then you will get that output. Eg: Partition BY new_cat Order BY new_cat. Please try this once and let me know if am wrong here. Thanks!
@abhishekkumarjha23523 ай бұрын
good observation, with the query mentioned in the video we will get the cumulative sum in that group
@AnandPatil-eu1tl4 ай бұрын
Thank You Rishabh
@RishabhMishraOfficial4 ай бұрын
Glad you liked ✅️
@hoodal__ob851 Жыл бұрын
Great bhaiya❤❤
@gauravpal15693 Жыл бұрын
Thanks for the videos, very useful!!
@RishabhMishraOfficial Жыл бұрын
Glad you like them! 👍
@user-jb3cx2qk8b19 күн бұрын
Pyspark playlist please..
@dheerajkoranga50122 ай бұрын
smooth like a smoothie :) before : window function is difficult to understand after this video: what! window function is sooo easy
@RishabhMishraOfficial2 ай бұрын
Glad you liked ✅️
@saikatbagh88453 ай бұрын
Window functions maintain the individual rows while also providing aggregate-like results.
@ravindraslahane3507Ай бұрын
Very well explained
@pavan.avasthiАй бұрын
Thanks sir ❤
@rubysrivastava228811 ай бұрын
The output for the first query will be: new_id new_cat total 100 Agni 100 200 Agni 300 500 Dharti 500 700 Dharti 1200 200 Vayu 200 300 Vayu 500 500 Vayu 1000 Here, ORDER BY clause serves the dual purpose of sorting the data and calculating the cumulative sum or any other aggregate calculations within the window. @Rishabh please clarify here.
@RishabhMishraOfficial11 ай бұрын
Here we have sorted the data based on category n total column. Eg- Agni house is sorted first as it starts with alphabet 'A' n then in agni house first value is 100 followed by 300 which is again sorted
@aseemanand110 ай бұрын
@@RishabhMishraOfficialman did you understand what she asked? She’s asking about the total column’s output. Shouldn’t it be the cumulative sum/running sum?
@ape78910 ай бұрын
Actually the order by should be below the from statement and outside the over clause. inside the over bracket it is working on cumulative way.
@poojadange90889 ай бұрын
@@ape789 yes if we did it in that way as u said it is working as normal sum not like cumulative
@shraddhadhakad11548 ай бұрын
SELECT new_id, FIRST_VALUE(new_id) over(order by new_id) as "First_Value", LAST_VALUE(new_id) over(order by new_id) as "Last_Value", LEAD(new_id) over(order by new_id) as "Lead", LAG(new_id) over(order by new_id) as "LAG" FROM [practice_sd].[dbo].[windows function]
@akankshasoni3479 Жыл бұрын
Thank you so much bhaiya for uploading such a wonderful content onto KZfaq which is of great use to beginners like me
@RishabhMishraOfficial Жыл бұрын
Glad you liked it ✅️
@poojamankotia7353 Жыл бұрын
Hello, first of all your videos are really helpful, thankyou. Secondly, I am trying to run the Windows Aggregate func but its giving different input, kinldy help. Attaching the code and the output below: select new_id, new_cat, sum(new_id) over(partition by new_cat order by new_id)as "Total" new_id new_cat Total 100 Agni 100 200 Agni 300 700 Dharti 700 500 Dharti 500 200 Vayu 200 300 Vayu 500 500 Vayu 1000 from test_data
@vansaber10 ай бұрын
If you remove the ORDER BY from the statement or ORDER BY new_cat then it will display exactly like in the video.
@AnjaliKumari-fz9lr6 ай бұрын
Thanks for such a easy to understand video. ❤❤ Also I have se concern regarding data analyst job like ....is it necessary to know ML also or predictive modelling for this job??
@RishabhMishraOfficial6 ай бұрын
It's good to hv but not compulsory
@111rhishishranjan211 ай бұрын
Hello Bhaiya, Actually You forgot to make interview question videos related to window functions and LEAD/LAG and its been quite a while like 10 months , please do it, it would be much helpful for us because your explination is really great .
@RishabhMishraOfficial11 ай бұрын
Noted ✅️
@shraddhadhakad11548 ай бұрын
SELECT new_id,new_cat, sum(new_id) over(order by new_id rows between unbounded preceding and unbounded following) as "Total", AVG(new_id) over(order by new_id rows between unbounded preceding and unbounded following) as "Average", Count(new_id) over(order by new_id rows between unbounded preceding and unbounded following) as "Count", MIN(new_id) over(order by new_id rows between unbounded preceding and unbounded following) as "Minimum", MAX(new_id) over(order by new_id rows between unbounded preceding and unbounded following) as "Maximum" FROM [practice_sd].[dbo].[windows function]
@surajsidar32802 ай бұрын
Can you explain what is use of above query? What is ROWS clause
@shraddhadhakad11548 ай бұрын
SELECT new_id,new_cat, SUM(new_id) over(partition by new_cat order by new_id) as "Total", AVG(new_id) over(partition by new_cat order by new_id) as "Average", COUNT(new_id) over(partition by new_cat order by new_id) as "Count", MIN(new_id) over(partition by new_cat order by new_id) as "Minimum", MAX(new_id) over(partition by new_cat order by new_id) as "Maximum" FROM [practice_sd].[dbo].[windows function]
@dipanjansamanta1112 ай бұрын
Bro full program do piz with schema
@sunilrao60902 ай бұрын
Big fan of you sir,
@Deepakyadav-dn1gg11 ай бұрын
13:23 Ijjat se chalta hai dense rank.........waaah rishabh bhai
@pavan.avasthi10 ай бұрын
Thank you so much sir ❤
@RishabhMishraOfficial10 ай бұрын
Happy to help ✅️
@user-fi7zr2eq7h7 ай бұрын
Bahot achese sikhate ho
@pragati_5_82 ай бұрын
u R amazing
@hamza_Techy Жыл бұрын
Great
@RishabhMishraOfficial Жыл бұрын
Glad you liked ✅️
@hamza_Techy Жыл бұрын
@@RishabhMishraOfficial Thanks
@simranjeetsingh4101 Жыл бұрын
Thanku sir.
@adwaydandwate3 ай бұрын
Hi Rishabh, I ran below queries however the output is same for both. Could you please check and explain once pls. Thanks! select new_id, first_value(new_id) over(order by new_id) First_Value, last_value(new_id) over(order by new_id) Last_Value, Lead(new_id) over(order by new_id) Lead, Lag(new_id) over(order by new_id) Lag from test_data; --WITH RBUPAUF (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) select new_id, first_value(new_id) over(order by new_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) First_Value, last_value(new_id) over(order by new_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Last_Value, Lead(new_id) over(order by new_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Lead, Lag(new_id) over(order by new_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Lag from test_data;
@dinkarjha4500 Жыл бұрын
Hi Risabh your first Querry after running in PostgreSQL Result showing different as per the Pdf sheet select * from test_dat select new_id, new_cat, sum(new_id) over(partition by new_cat order by new_id)as total from test_dat new_id new_cat Total 100 Agni 100 200 Agni 300 700 Dhart 700 500 Dharti 500 200 Vayu 200 300 Vayu 500 500 Vayu 1000
@dinkarjha4500 Жыл бұрын
As i observed should be new_cat in place of new_id
@realfacts3517 Жыл бұрын
Same
@RashidKhan-tz5ct Жыл бұрын
@@dinkarjha4500 Absolutely right
@poojamankotia7353 Жыл бұрын
@@dinkarjha4500 I am having the same results, what changes did you make?
@dinkarjha4500 Жыл бұрын
@@poojamankotia7353 compare shared query with pdf shared by Rishabh and result. partition by and order by both should be in new_cat in place of new_id.
@shwetarawat4027 Жыл бұрын
very helpful
@shraddhadhakad11548 ай бұрын
SELECT new_id, ROW_NUMBER() over(order by new_id) as "Row_Number", RANK() over(order by new_id) as "Rank", DENSE_RANK() over(order by new_id) as "Dense_Rank", PERCENT_RANK() over(order by new_id) as "Percent_Rank" FROM [practice_sd].[dbo].[windows function]
@rahulrahul6259 Жыл бұрын
Thanks
@ss827310 ай бұрын
superb❤❤❤❤
@RishabhMishraOfficial10 ай бұрын
✅️
@ganeshn3800 Жыл бұрын
we can use group by instead of partition by, is there any specific difference between these 2?
@sarjakmaniar369 Жыл бұрын
Dense Rank - Magar yeh ijjat se chalta hai 😂 Loved the way of your teaching!! Thank you :)
@RishabhMishraOfficial Жыл бұрын
Glad you liked ✅️
@rajkumarkori78885 ай бұрын
your explaination is good. however, you need to write the Query insted of directly pasting. if you write the query i belive your views will increase. thank you.
@ysc70685 ай бұрын
Very useful 👌👌
@Prernajain273011 ай бұрын
CREATE TABLE LEADLEGG (NEW_ID INT); INSERT INTO LEADLEGG VALUES (100), (200), (200), (300), (500), (500), (700); SELECT * FROM LEADLEGG; SELECT NEW_ID, LEAD(NEW_ID,2) OVER (ORDER BY NEW_ID) AS LEADID, LAG(NEW_ID,2) OVER (ORDER BY NEW_ID) AS LEADIDS FROM LEADLEGG;
@sheetalmishra12589 ай бұрын
Please explain partion by function
@vaibhavpatharkar6794 Жыл бұрын
Bahut hi aaacha padhate ho aaap. U earned a subscriber and a like. Aaaap apna course shuru karo, minimal fees like 200 or 300 rakhlo. Aaur pura detail mein sql lo. Kyunki market mein log short course le rahe hai. Aur pura 6 ghanta le rahe hai, lekin usme 4 ghante ki advertisement kar rahe hai aur baaate kar rahe hai. Lekin 4 ghanta nahi padha rahe , sirf 2 ghanta pada rahe hai, masterclass mein. Uske wo 400 rs le rahe. Aur lakhon kama rahe , aur baccho se gaddari kar rahe hai. Aaap toh acche insan ho acche teacher ho , aaap masterclass lo , aur 6 ghanta mein sql khatam kar do. Let me add 1ce again , you teach very lucidly, especially the hindi language
@shubhambali48557 ай бұрын
Please provide use case situations along with solution
@vishalchauhan9832 Жыл бұрын
It would be great if u could upload a dedicated video for partition by.
@RishabhMishraOfficial Жыл бұрын
Okayy noted ✅️
@aamirzaman83102 ай бұрын
very nice rishabh, itni mehnat karte ho to insert statement bhi de diya karo bhai :p 😜
@kapilkumarkol15478 ай бұрын
assignment ans SELECT new_id, LEAD(new_id,2) OVER(ORDER BY new_id) AS "LEAD", LAG(new_id,2) OVER(ORDER BY new_id) AS "LAG" FROM testdata ;
@abhinavbhoyar11 Жыл бұрын
I think it should be 'order by new_cat' instead of 'order by new_id' at 06:04 otherwise result will not be the same as shown.
@amrit_raj_dubey Жыл бұрын
Even I have commented same now 😂
@divakarsaini9849 Жыл бұрын
Thanks for the video
@shubhu35333 ай бұрын
bro informative video, but can you share test data file used in video
@rishabhbisht8717Күн бұрын
Solution for the Question asked: SELECT input_id, LAG(input_id, 2) OVER (ORDER BY input_id) AS lag, LEAD(input_id, 2) OVER (ORDER BY input_id) AS lead FROM your_table;
@RishabhMishraOfficial22 сағат бұрын
✅️
@rithikahuja8203 Жыл бұрын
Thank you so much rishabh bhaiya for giving such a beautiful content ❤❤
@user-ky1zu1wz8d11 ай бұрын
SELECT NEW_ID, LEAD(NEW_ID,2)OVER(ORDER BY NEW_ID) AS LEAD, LAG(NEW_ID,2)OVER(ORDER BY NEW_ID) AS LAG FROM TEST_DATA;
@sridharmurari30079 ай бұрын
one basic q if not silly : in Rank() > is it applied on partition by col or order by col : while assign for duplicates does it look at partition by or order by data thx