Summary of "SQL Window Function | How to write SQL Query using RANK, DENSE RANK, LEAD/LAG | SQL Queries Tutorial"

Summary of the Video:

"SQL Window Function | How to write SQL Query using RANK, DENSE RANK, LEAD/LAG | SQL Queries Tutorial"

This tutorial explains the concept and practical usage of SQL window functions (also called analytic functions) which are essential for solving complex SQL problems and frequently asked in interviews. The video uses an employee table example to demonstrate how window functions work, their syntax, and their differences.

---

Main Ideas and Concepts:

• Window Functions Overview:
  • Window functions perform calculations across a set of table rows related to the current row.
  • Unlike aggregate functions with GROUP BY, window functions do not collapse rows but return a value for each row.
  • Common window functions covered: RANK, DENSE_RANK, ROW_NUMBER, LEAD, LAG.
  • Aggregate functions like MAX, MIN, SUM, etc., can also be used as window functions with the OVER clause.
• Syntax of Window Functions:
  • Basic form: <function>() OVER ([PARTITION BY column] [ORDER BY column])
  • PARTITION BY divides the data into subsets (windows) on which the function operates.
  • ORDER BY defines the order of rows within each partition.
• Using Aggregate Functions as Window Functions:
  • Example: SELECT *, MAX(salary) OVER (PARTITION BY department_name) AS max_salary FROM employee;
  • This returns the max salary per department alongside each employee record.
• ROW_NUMBER():
  • Assigns a unique sequential integer to rows within a partition.
  • Useful for ranking or filtering top N records per group.
  • Example: Assign row numbers partitioned by department and ordered by employee ID.
• Filtering Top N Records Using ROW_NUMBER():
  • Use a subquery to filter rows where ROW_NUMBER() <= N.
  • Example: Fetch first 2 employees who joined (lowest employee ID) per department.
• RANK() vs DENSE_RANK():
  • Both assign ranks based on ordering within partitions.
  • RANK() skips RANK numbers after ties (gaps in ranking).
  • DENSE_RANK() does not skip RANK numbers after ties.
  • Example: Ranking employees by salary within departments.
  • Use subqueries to filter top 3 salaries per department by RANK.
• Difference Between ROW_NUMBER, RANK, and DENSE_RANK:
  • ROW_NUMBER() assigns unique sequential numbers regardless of ties.
  • RANK() assigns same RANK to ties but skips subsequent ranks.
  • DENSE_RANK() assigns same RANK to ties but does not skip ranks.
• LEAD() and LAG():
  • Used to access data from subsequent (LEAD) or preceding (LAG) rows within the same partition.
  • Useful for comparing current row values with previous or next row values.
  • Syntax includes optional arguments to specify how many rows ahead/behind to look and default values.
  • Example use case: Compare current employee salary with the previous employee's salary in the same department.
  • Can be combined with CASE statements to label salary changes as "higher", "lower", or "same".
• Additional Notes:
  • The video encourages liking and subscribing for more SQL and data science tutorials.
  • Mentions other window functions like NTH_VALUE(), FIRST_VALUE(), LAST_VALUE(), etc., which can be covered in future videos.

---

Methodologies / Instructions Presented:

• How to Use Aggregate Functions as Window Functions:
  • Write aggregate function with OVER().
  • Use PARTITION BY inside OVER() to group rows without collapsing them.
• Assigning Row Numbers:
  • Use ROW_NUMBER() OVER (PARTITION BY <column> ORDER BY <column>).
  • Use subquery filtering WHERE ROW_NUMBER <= N to get top N rows per group.
• Ranking Employees by Salary:
  • Use RANK() or DENSE_RANK() with partition and order by salary descending.
  • Filter top 3 ranks using a subquery.
• Comparing Current and Previous/Next Rows:
  • Use LAG(column, offset, default) or LEAD(column, offset, default) with partition and order.
  • Use CASE to compare and label the differences.

---

Speakers / Sources Featured:

• The video is presented by a single instructor (unnamed) who guides through the concepts and SQL query examples step-by-step.
• No other speakers or external sources are mentioned.

---

End of Summary

Category

Educational

Share this summary

Share on X/Twitter Share on Facebook Share on LinkedIn Share on Reddit Share on WhatsApp Share on Telegram

Video