SQL analytic functions in Oracle, with input and output examples

Analytic functions (also called window functions) operate over a set of rows and return values for each row, unlike aggregate functions which collapse rows into one.

Top 10 Analytic Functions in Oracle

Function	Purpose	Example Input	Example Query	Example Output
ROW_NUMBER()	Assigns unique sequential number to rows	Employees table with salaries	SELECT emp_name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees;	Each employee gets a unique rank (1,2,3…)
RANK()	Assigns rank with gaps for ties	Employees with same salary	SELECT emp_name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees;	If two employees tie at rank 1, next rank is 3
DENSE_RANK()	Assigns rank without gaps	Same as above	SELECT emp_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk FROM employees;	If two tie at rank 1, next rank is 2
NTILE(n)	Distributes rows into n buckets	Employees salaries	SELECT emp_name, salary, NTILE(4) OVER (ORDER BY salary DESC) AS quartile FROM employees;	Divides salaries into 4 quartiles
LEAD()	Accesses next row’s value	Employees salaries	SELECT emp_name, salary, LEAD(salary) OVER (ORDER BY salary) AS next_sal FROM employees;	Shows next higher salary
LAG()	Accesses previous row’s value	Employees salaries	SELECT emp_name, salary, LAG(salary) OVER (ORDER BY salary) AS prev_sal FROM employees;	Shows previous lower salary
FIRST_VALUE()	Returns first value in window	Employees salaries	SELECT emp_name, salary, FIRST_VALUE(salary) OVER (ORDER BY salary DESC) AS top_sal FROM employees;	Shows highest salary for all rows
LAST_VALUE()	Returns last value in window	Employees salaries	SELECT emp_name, salary, LAST_VALUE(salary) OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS bottom_sal FROM employees;	Shows lowest salary for all rows
CUME_DIST()	Cumulative distribution (fraction ≤ current row)	Employees salaries	SELECT emp_name, salary, CUME_DIST() OVER (ORDER BY salary) AS cd FROM employees;	Values between 0 and 1 showing percentile
PERCENT_RANK()	Relative rank as percentage	Employees salaries	SELECT emp_name, salary, PERCENT_RANK() OVER (ORDER BY salary) AS pr FROM employees;	Shows relative position (0–1)

Example Walkthrough (Employees Table)

Suppose we have:

CREATE TABLE employees (
  emp_id NUMBER,
  emp_name VARCHAR2(50),
  salary NUMBER
);

INSERT INTO employees VALUES (1, 'Alice', 5000);
INSERT INTO employees VALUES (2, 'Bob', 7000);
INSERT INTO employees VALUES (3, 'Charlie', 7000);
INSERT INTO employees VALUES (4, 'David', 9000);
INSERT INTO employees VALUES (5, 'Eve', 4000);

Employees Table (Input Data)

emp_id	emp_name	salary
1	Alice	5000
2	Bob	7000
3	Charlie	7000
4	David	9000
5	Eve	4000

Analytic Functions with Query + Output

1. ROW_NUMBER()

SELECT emp_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

emp_name	salary	row_num
David	9000	1
Bob	7000	2
Charlie	7000	3
Alice	5000	4
Eve	4000	5

2. RANK()

SELECT emp_name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;

emp_name	salary	rnk
David	9000	1
Bob	7000	2
Charlie	7000	2
Alice	5000	4
Eve	4000	5

3. DENSE_RANK()

SELECT emp_name, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;

emp_name	salary	drnk
David	9000	1
Bob	7000	2
Charlie	7000	2
Alice	5000	3
Eve	4000	4

4. NTILE(3)

SELECT emp_name, salary,
       NTILE(3) OVER (ORDER BY salary DESC) AS bucket
FROM employees;

emp_name	salary	bucket
David	9000	1
Bob	7000	1
Charlie	7000	2
Alice	5000	2
Eve	4000	3

5. LEAD()

SELECT emp_name, salary,
       LEAD(salary) OVER (ORDER BY salary DESC) AS next_sal
FROM employees;

emp_name	salary	next_sal
David	9000	7000
Bob	7000	7000
Charlie	7000	5000
Alice	5000	4000
Eve	4000	NULL

6. LAG()

SELECT emp_name, salary,
       LAG(salary) OVER (ORDER BY salary DESC) AS prev_sal
FROM employees;

emp_name	salary	prev_sal
David	9000	NULL
Bob	7000	9000
Charlie	7000	7000
Alice	5000	7000
Eve	4000	5000

7. FIRST_VALUE()

SELECT emp_name, salary,
       FIRST_VALUE(salary) OVER (ORDER BY salary DESC) AS top_sal
FROM employees;

emp_name	salary	top_sal
David	9000	9000
Bob	7000	9000
Charlie	7000	9000
Alice	5000	9000
Eve	4000	9000

8. LAST_VALUE()

SELECT emp_name, salary,
       LAST_VALUE(salary) OVER (ORDER BY salary DESC
       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS bottom_sal
FROM employees;

emp_name	salary	bottom_sal
David	9000	4000
Bob	7000	4000
Charlie	7000	4000
Alice	5000	4000
Eve	4000	4000

9. CUME_DIST()

SELECT emp_name, salary,
       CUME_DIST() OVER (ORDER BY salary) AS cd
FROM employees;

emp_name	salary	cd
Eve	4000	0.2
Alice	5000	0.4
Bob	7000	0.8
Charlie	7000	0.8
David	9000	1.0

10. PERCENT_RANK()

SELECT emp_name, salary,
       PERCENT_RANK() OVER (ORDER BY salary) AS pr
FROM employees;

emp_name	salary	pr
Eve	4000	0.0
Alice	5000	0.25
Bob	7000	0.75
Charlie	7000	0.75
David	9000	1.0