Unlocking the Power of Oracle Window Functions

Title: Ranking, Running Totals, and More: A Deep Dive into Oracle Window Functions

Overview: Cover RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD. Example Query:

SQL> ed
  1  SELECT
  2      deptno,
  3      empno,
  4      sal,
  5      RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS salary_rank
  6  FROM
  7*     emp
SQL> /

   DEPTNO    EMPNO     SAL    SALARY_RANK
_________ ________ _______ ______________
       10     7839    5000              1
       10     7782    2450              2
       10     7934    1300              3
       20     7788    3000              1
       20     7566    2975              2
       20     7369     800              3
       30     7698    2850              1
       30     7499    1600              2
       30     7844    1500              3
       30     7521    1250              4

10 rows selected.

Use Case: Finding top earners in each department.