Dezbor
← Back to Blog
SQL

SQL Window Functions Explained with Examples

If you've been writing SQL for a while, you've probably hit a wall where GROUP BY just isn't enough. You need to rank rows, compute running totals, or compare a value with the previous row — all without collapsing your result set. That's where window functions come in.

What Are Window Functions?

A window function performs a calculation across a set of rows that are somehow related to the current row. Unlike GROUP BY, window functions don't collapse rows — you keep the full result set while adding computed columns.

SELECT
  employee_name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees

This ranks employees by salary within each department, without removing any rows.

The OVER() Clause

Every window function uses OVER() to define the window. The two main parts are:

  • PARTITION BY — Divides rows into groups (like GROUP BY, but without collapsing)
  • ORDER BY — Determines the order of rows within each partition

ROW_NUMBER() — Unique Sequential Numbers

SELECT
  order_id,
  customer_id,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_sequence
FROM orders

This gives each customer's orders a sequential number (1st order, 2nd order, etc.).

RANK() and DENSE_RANK() — Handling Ties

SELECT
  student_name,
  score,
  RANK() OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM exam_results

RANK() leaves gaps after ties (1, 2, 2, 4). DENSE_RANK() doesn't (1, 2, 2, 3).

LAG() and LEAD() — Accessing Adjacent Rows

SELECT
  report_date,
  revenue,
  LAG(revenue) OVER (ORDER BY report_date) AS prev_revenue,
  revenue - LAG(revenue) OVER (ORDER BY report_date) AS revenue_change
FROM monthly_revenue

LAG() accesses the previous row, LEAD() accesses the next. Perfect for calculating period-over-period changes.

Running Totals with SUM() OVER()

SELECT
  transaction_date,
  amount,
  SUM(amount) OVER (ORDER BY transaction_date) AS running_total
FROM transactions

This creates a cumulative sum that grows with each row.

Moving Averages with ROWS BETWEEN

SELECT
  report_date,
  daily_sales,
  AVG(daily_sales) OVER (
    ORDER BY report_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS seven_day_avg
FROM sales_data

This computes a 7-day moving average — incredibly useful for smoothing noisy metrics.

NTILE() — Bucketing Rows

SELECT
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile
FROM customer_summary

Divides customers into 4 equal buckets by spending. Quartile 1 = top spenders.

When to Use Window Functions

  • Ranking items within groups (top products per category)
  • Calculating running totals and averages
  • Comparing current row to previous/next row
  • Deduplicating rows (ROW_NUMBER + CTE to keep only the latest)
  • Percentile and quartile analysis

Performance Tips

  1. Index the ORDER BY column: Window functions sort data; indexes help.
  2. Limit partitions: Very large partitions are expensive.
  3. Use CTEs: Break complex window function queries into readable Common Table Expressions.

Conclusion

Window functions are the bridge between simple queries and advanced analytics. Once you internalize OVER(PARTITION BY ... ORDER BY ...), you'll find yourself reaching for them constantly. Start with ROW_NUMBER and RANK, then graduate to LAG/LEAD and running totals — your SQL game will never be the same.

SQL Window Functions Explained with Examples - Dezbor Blog