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
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
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.
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.
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!
No no both are different. They have specific use case. When you will solve interview questions you will understand. Already uplaoded a video for that, Try solving interview questions on this channel.
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]
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;
@@RishabhMishraOfficial sir I have checked your GitHub.. but those files are in CSV format I thik.? Do I have to use those CSV files or you have shared it in SQL format also? Sir please reply
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]
I'm Beginner in Data Analytics in some topic facing such issue and I'm searching such videos and also i'm studying from other tech youtuber but accidently i'm watching your videos and I'll appreciate you and your understanding or knowledge in Data Analytics and thank a lot for these free knowledge for giving us for building our Carrier Thankyou so Much ❤❤❤
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
In aggregation functions if i do Order by new_id then answer is different by your output sir after that i changed to Order by new_cat, output is same as per o/p table ... pls reply sir @rishabh mishra if i made a mistake
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;
SELECT NAME, LAG(NAME,2) OVER(ORDER BY NAME) AS "LAG_2", LEAD(NAME,2) OVER(ORDER BY NAME) AS "LEAD_2" FROM FRIENDS; This is the correct answer of your last question.👍👍👍👍
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 .
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]
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?
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??
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]
I think that the ORDER BY clause is not to be used since i am gettig the different result....by using order by clause each line is getting calculted instead of the defined window.....please correct me if anybody is also getting the different result instead of the desired result .....i am talking about this section 6:32
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
bro thanks a lot i am following your sql plylist this one your teaching is very good and iam from pakistan but i am facing an issue in window function when i use sum with window function it not working like yours its show first the low value in in the next value add both values in show the sum in the third row combine all the three and show the sum like not one unique value when u read my comment please guide me are tell me the name of mistake what iam doing so i can find some soulution on the internt
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.🙃
Ma data analyst ki job k liay one year say struggle kr rahi hon or one year say ap ko hi follow kr rahi hon but Mera interview clear nhi hota plzzz Meri help kran plzzz sir
CREATE TABLE CUST (NEW_ID INT8 ); INSERT INTO CUST (NEW_ID ) VALUES (100, 200, 200, 300, 500, 500, 700); GEETING ERROR IN THIS CODE HOW TO MAKE THE TABLE PLEASE TELL ME
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.
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
@@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?
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;
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
@@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.
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
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 Crafted by Merlin AI.
Yes you can say that. A window function, also known as an analytic function, computes values over a group of rows and returns a single result for each row. This is different from an aggregate function, which returns a single result for a group of rows.
sir if i apply sum function on new_id with the same values and same new_cat values i am getting an answer in total column as 100 and 300 while using same code plz tell me where am i making a mistake?
I have a qstn plz reply 🙏🙏 I did my graduation in BCA, should I do MCA, then look for a data science field or BCA is enough for job and future also??? Plz reply🙏🙏🙏🙏
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.
Trying to learn sql to enter data analytics as i want to switch from Amazon seller support, Thank you bhaiya will try super hard to learn all the skillsets❤you have shared its a blessing for us
Your explanation of almost all the concepts is very good, but I found very difficult to understand row between concept as it was not explained much, you should have elaborated it bit more.
Example that you have given is bit wrong , while discussing Window function example Order by used can’t give exact answers as you show in table. Instead of Order by new_id need to use Order by new_cat. Hope you will change
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
