DataPlayArena
SQL Reference Guide
Revision Time: 4 mins

11. Window Functions Reference

Calculations across partitions.

ROW_NUMBER()
Return: Value

Sequential increment index values.

Syntax signature:ROW_NUMBER() OVER (PARTITION BY grp ORDER BY val);
Code snippet:
python
SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) as rn FROM employees;
Expected Output:Ordered indices list.
Remember: Always ensure correct syntax formatting when calling ROW_NUMBER().
RANK()
Return: Value

Rank with duplicate gaps.

Syntax signature:RANK() OVER (PARTITION BY grp ORDER BY val);
Code snippet:
python
SELECT name, RANK() OVER (ORDER BY salary DESC) as rk FROM employees;
Expected Output:Gapped rankings.
Remember: Always ensure correct syntax formatting when calling RANK().
DENSE_RANK()
Return: Value

Rank without gaps.

Syntax signature:DENSE_RANK() OVER (PARTITION BY grp ORDER BY val);
Code snippet:
python
SELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) as drk FROM employees;
Expected Output:Continuous rankings.
Remember: Always ensure correct syntax formatting when calling DENSE_RANK().
LAG()
Return: Value

Fetch preceding row value offset cell.

Syntax signature:LAG(col, offset) OVER (ORDER BY col);
Code snippet:
python
SELECT price, LAG(price, 1) OVER (ORDER BY date) FROM stock;
Expected Output:Preceding price values.
Remember: Always ensure correct syntax formatting when calling LAG().
LEAD()
Return: Value

Fetch succeeding row value offset cell.

Syntax signature:LEAD(col, offset) OVER (ORDER BY col);
Code snippet:
python
SELECT price, LEAD(price, 1) OVER (ORDER BY date) FROM stock;
Expected Output:Following price values.
Remember: Always ensure correct syntax formatting when calling LEAD().
FIRST_VALUE()
Return: Value

First value in partition slice.

Syntax signature:FIRST_VALUE(col) OVER (PARTITION BY grp ORDER BY val);
Code snippet:
python
SELECT name, FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC) FROM employees;
Expected Output:Highest salary per department mapped to every row.
Remember: Always ensure correct syntax formatting when calling FIRST_VALUE().
LAST_VALUE()
Return: Value

Last value in partition slice.

Syntax signature:LAST_VALUE(col) OVER (PARTITION BY grp ORDER BY val);
Code snippet:
python
SELECT name, LAST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM employees;
Expected Output:Lowest salary per department mapped to every row.
Remember: Always ensure correct syntax formatting when calling LAST_VALUE().
NTILE()
Return: Value

Distribute rows into bucket tiles.

Syntax signature:NTILE(buckets) OVER (ORDER BY col);
Code snippet:
python
SELECT name, NTILE(4) OVER (ORDER BY salary DESC) FROM employees;
Expected Output:Quartile buckets.
Remember: Always ensure correct syntax formatting when calling NTILE().
Previous Topic10. SubqueriesNext Topic12. CTE (WITH)