Advanced Analytic Functions in Oracle SQL

What analytic functions are

Unlike GROUP BY which collapses rows, analytic functions compute values across related rows without reducing the result set:

graph LR
    subgraph "With GROUP BY — rows collapsed"
        GB[dept 10 → 1 row<br/>dept 20 → 1 row<br/>dept 30 → 1 row]
    end
    subgraph "With Analytic Function — all rows kept"
        AF[emp 101, dept 10, sal 5000, rank 1<br/>emp 102, dept 10, sal 4500, rank 2<br/>emp 103, dept 20, sal 8000, rank 1<br/>emp 104, dept 20, sal 7000, rank 2]
    end
    style GB fill:#2d0f0f,stroke:#EF4444,color:#e2e8f0
    style AF fill:#0f2d1f,stroke:#22C55E,color:#e2e8f0

The OVER clause anatomy

FUNCTION_NAME() OVER (
  PARTITION BY column    -- divide rows into groups
  ORDER BY column        -- define row order within group
  ROWS/RANGE BETWEEN ... -- define the window frame
)

ROW_NUMBER, RANK, DENSE_RANK

SELECT
  employee_id,
  department_id,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rnk
FROM employees;

employee_id	dept	salary	ROW_NUMBER	RANK	DENSE_RANK
101	10	9000	1	1	1
102	10	8500	2	2	2
103	10	8500	3	2	2
104	10	7000	4	4	3

RANK skips numbers after ties (1,2,2,4). DENSE_RANK does not (1,2,2,3).

Top-N per group — the most common use case

-- Top 3 earners per department, using ROW_NUMBER
SELECT dept_id, employee_name, salary
FROM (
  SELECT
    department_id AS dept_id,
    first_name || ' ' || last_name AS employee_name,
    salary,
    ROW_NUMBER() OVER (
      PARTITION BY department_id
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
WHERE rn <= 3
ORDER BY dept_id, rn;

LAG and LEAD — period comparisons without self-joins

SELECT
  month_year,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY month_year)         AS prev_month_revenue,
  revenue - LAG(revenue, 1, 0) OVER (ORDER BY month_year) AS month_change,
  ROUND(
    (revenue - LAG(revenue,1) OVER (ORDER BY month_year))
    / LAG(revenue,1) OVER (ORDER BY month_year) * 100, 1
  )                                                       AS pct_change
FROM monthly_sales
ORDER BY month_year;

Running totals and moving averages

SELECT
  sale_date,
  daily_amount,
  -- Running total since start of year
  SUM(daily_amount) OVER (
    PARTITION BY TO_CHAR(sale_date, 'YYYY')
    ORDER BY sale_date
  ) AS ytd_total,
  -- 7-day moving average
  ROUND(AVG(daily_amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS moving_avg_7d
FROM daily_sales
ORDER BY sale_date;

NTILE — split into buckets

-- Divide employees into 4 salary quartiles
SELECT
  employee_id,
  first_name,
  salary,
  NTILE(4) OVER (ORDER BY salary) AS quartile,
  CASE NTILE(4) OVER (ORDER BY salary)
    WHEN 1 THEN 'Bottom 25%'
    WHEN 2 THEN '25-50%'
    WHEN 3 THEN '50-75%'
    WHEN 4 THEN 'Top 25%'
  END AS salary_band
FROM employees;