WINDOW FUNCTIONS

Window function performs a calculation across a set of rows related to the current row The result is calculated per row, but the calculation itself can look at other rows around it.

Window function has over() which turns a normal aggregate function into a window function.

in this example you are able to see the average total amount compared to each room type .

determines the sequence rows are processed in for that window — essential for ranking functions and for anything

_Partition by _

It is usually written inside the over and is used to group data to the thing that you what it be grouped. It just like the group by clause but this one is for window function only, this helps that each group gets its own independent calculation.

order by

determines the sequence rows are processed in for that window essential for ranking functions and for anything
also written inside over()

ROW_NUMBER, RANK_NUMBER,DENSE_RANK,LAG,LEAD,

Row_number
This is just a simple count of row . I have used it multiple
time when the primary key is not in particular order.
Example

RANK AND DENSE RANK
Rank
The rank()function assigns a rank to each row based on the ORDER BY clause in the OVER statement. Rows with the same value receive the same rank, with gaps in the ranking for duplicate values.

TASK:see which jobrole has the highest daiyrate

Dense rank
The DENSE_RANK() function assigns ranks like RANK(), but it doesn’t skip ranks after ties

Conclusion
SQL window functions like ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD(), and LAG() provide powerful ways to analyze data by performing calculations across rows within a defined window.

.