Sql Window Functions — Free Sql Tutorial

TL;DR: Learn Sql Window Functions in Sql with a free, beginner-friendly tutorial, examples and practice for Indian students on Syllab.in.

Sql Window Functions in Sql

Window functions perform calculations across a set of rows related to the current row, without collapsing rows like GROUP BY does. They use the OVER() clause.

ROW_NUMBER() assigns sequential numbers. RANK() gives the same rank to ties (with gaps). DENSE_RANK() no gaps. NTILE(n) divides rows into n buckets.

PARTITION BY divides the data into groups (like GROUP BY but rows are preserved). ORDER BY inside OVER() defines the ordering within each partition.

LAG() and LEAD() access the previous/next row's value — perfect for calculating month-over-month changes, running differences, and trends.

-- ROW_NUMBER, RANK, DENSE_RANK
SELECT name, marks,
  ROW_NUMBER() OVER (ORDER BY marks DESC) AS row_num,
  RANK()       OVER (ORDER BY marks DESC) AS rank_pos,
  DENSE_RANK() OVER (ORDER BY marks DESC) AS dense_rank
FROM students;

-- PARTITION BY (rank within each class)
RANK() OVER (PARTITION BY class ORDER BY marks DESC)

-- Running total
SUM(amount) OVER (ORDER BY date)

-- Compare to previous row
LAG(amount, 1) OVER (ORDER BY date) AS prev_amount

Learn Sql Window Functions step by step with Syllab's free interactive Sql tutorial — runnable code examples, practice exercises and instant AI feedback, all free with no signup. Explore the full Sql course →