softwaretips4u Tips that Transform
← Back to Homepage ← Back to Articles

Window Functions in SQL

🔹 What Are Window Functions?

Window functions perform calculations across a set of rows related to the current row—without collapsing the results the way GROUP BY does. They are ideal for rankings, running totals, moving averages, and comparing the current row to previous or next rows.

✅ Supported in modern databases including PostgreSQL, SQL Server, Oracle, and MySQL 8+.

⚙️ Basic Syntax

function_name(expression) OVER (
  [PARTITION BY column]
  [ORDER BY column]
  [ROWS | RANGE frame]
);

🧠 Common Window Functions

Function Purpose Typical Use
ROW_NUMBER() Assigns a unique sequential number Pagination or deterministic ordering
RANK() / DENSE_RANK() Assigns ranks with or without gaps Leaderboards, competition results
NTILE(n) Splits rows into n buckets Quartiles, percentiles
SUM(), AVG(), COUNT() Calculates running totals or moving averages Sales analysis, time-based metrics
LAG() / LEAD() Accesses previous or next row values Trend and variance comparisons

🧩 Example 1 – Ranking Salaries

SELECT
  employee_id,
  department_id,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
  ) AS rank_in_dept
FROM employees;
📘 Each department gets its own numbering based on descending salary.

🧩 Example 2 – Running Total per Customer

SELECT
  order_id,
  customer_id,
  order_date,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders;
📘 Reveals the cumulative amount each customer has spent over time.

🧩 Example 3 – Comparing Rows with LAG

SELECT
  product_id,
  month,
  sales,
  LAG(sales) OVER (
    PARTITION BY product_id
    ORDER BY month
  ) AS prev_month_sales,
  sales - LAG(sales) OVER (
    PARTITION BY product_id
    ORDER BY month
  ) AS sales_change
FROM sales_data;
📘 Highlights month-over-month changes without additional joins or subqueries.

🧩 Example 4 – Ranking Without Collapsing Rows

SELECT
  name,
  department,
  salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS rank_in_dept
FROM employees;
💡 Unlike GROUP BY, window functions keep all detail rows visible.

⚖️ Key Benefits

💡 Interview Soundbite

“Window functions analyze rows in context—supporting ranking, running totals, and row-by-row comparisons without collapsing data.”
← Previous Next →
← Back to Articles | 🏠 Back to Homepage