Session 36 - Window Functions in SQL | DSMP 2023

Session 36 - Window Functions in SQL | DSMP 2023
-------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE marks (
student_id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
branch VARCHAR(255),
marks INTEGER
);

INSERT INTO marks (name,branch,marks)VALUES
('Nitish','EEE',82),
('Rishabh','EEE',91),
('Anukant','EEE',69),
('Rupesh','EEE',55),
('Shubham','CSE',78),
('Ved','CSE',43),
('Deepak','CSE',98),
('Arpan','CSE',95),
('Vinay','ECE',95),
('Ankit','ECE',88),
('Anand','ECE',81),
('Rohit','ECE',95),
('Prashant','MECH',75),
('Amit','MECH',69),
('Sunny','MECH',39),
('Gautam','MECH',51)
-------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
| Time stamp |
-----------------------
00:00:00 - Session Start
00:04:06 - Agenda
00:05:46 - What are Window Functions?
00:11:41 - Aggregate Function with OVER() (Example #1)
00:15:52 - Example #2
00:21:21 - Doubt clearance
00:22:43 - Example #3
00:26:17 - RANK()
00:31:15 - DENSE_RANK()
00:33:08 - ROW_NUMBER()
00:37:05 - Short summary for the above 3 window functions
00:37:52 - Example #4
00:45:42 - Doubt clearance
00:47:15 - FIRST_VALUE()
00:51:20 - LAST_VALUE()
00:52:31 - Concept of Frames
01:05:02 - Doubt clearance
01:06:18 - NTH_VALUE()
01:08:21 - Example #5
01:15:29 - Have to paste the right result
01:18:14 - LAG()
01:20:39 - LEAD()
01:21:57 - Example #6
01:28:07 - Summary of the session
01:29:04 - Doubt clearance & Session end

#datanalytics #Database #DBMS #SQL #DML #sqlgroupby #groupby #sqlwindow

[Anannya_C]

My Humble appreciation to Nitish Sir, for the way he clarifies complicated concepts in a layman manner. As a novice in this field, it becomes very easy to grasp in, especially when you're from a non-tech background trying to self learn. 1:07:50 For displaying the 2nd last value (changed the ordering to ASC from DESC: SELECT *, NTH_VALUE(name, 2) OVER(PARTITION BY branch ORDER BY marks ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM marks;

[Lancer_Soldier]

you are life saver i wish your mother always be happy

[shivaaryaprakash]

What a dedicated teaching. I just enjoy your teaching and learn in depth.
Thank you 🙏🙏

[parthraghuwanshi]

The best thing about sir is he understand things from student point of view where student get struck and truly help us to come out of that dig

[techtonik]

You really make it easy to understand! Hatsoff!! Thak you!

[nitishchauhan]

Yeah baat . 💯 I wasn't understanding this window fun anywhere, but here within initial few mins I got the clarity. Super. 👏✨

[bhaktipadwal]

Finally all my doubts are clear, thanks a lot!!!

[rajikaursandhu]

Very well Explained...Hatsoff!! Thak you!

[balrajprajesh]

70k hone par badhai. Hopefully we touch 100k very soon.

[ArchitKumar-rc]

Damn ! You are the best teacher available on the internet for data science, i have literally wasted more than 80k + on a XYZ coaching, i wish i could have known you earlier, anyways i am following you now and mastering the skills all thanks to you..

[soumikdey]

Sir, Sach m itna quality content kahi nhi milega, we will touch 100K very soon

[DataGeekRS]

Sir aap gajab ho ye samajh hi nhi aara tha first 15min k video m hi clear kar diye aap. waah !! Thank You!!

[not_a_userz]

Thank you NITISH Sir, it really helps me alot 😀

[ravinarvariya]

thank you so much Nitish sir, the way you explain the topics is amazing.. hats off sir.🤗

[pvn_gupta]

At 1:16:41, there is syntax error.
Correct code :
SELECT name, branch, marks
FROM (
SELECT *,
LAST_VALUE(name) OVER w AS topper_name,
LAST_VALUE(marks) OVER w AS topper_marks
FROM marks
WINDOW w AS (PARTITION BY branch ORDER BY marks DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
) t
WHERE t.name = t.topper_name
AND t.marks = t.topper_marks;

[umarijaz]

First time ever i comment on a lecture Sir you are great

[VinodKumari-btuo]

A great and geuiune guy . All the best for coming 1 lakh subsricbers..

[devendraahirwal]

1:12:20, we could use " select distinct branch,
first_value (name) over (partition by branch order by marks desc) as tpr_name,
first_value (marks) over (partition by branch order by marks desc) as tpr_marks
from marks;" instead of using where

[deeprajpanwar]

Found this great channel few days ago can anybody suggest more channel for AI, ML courses

[pragyasingh]

Sir, Well define WINDOW Function, Please provide Interview question for sql.