Database.Middle.What is window function?

window functions (also called analytic functions) are a powerful feature in SQL that let you perform calculations across rows related to the current row, without collapsing them into a single result like GROUP BY does.

🧠 Simple Definition

A window function performs a calculation over a “window” (subset) of rows that are related to the current row, while still returning one row per input row.

🧾 Common Window Functions

Function	Purpose
ROW_NUMBER()	Assigns a unique row number per group
RANK() / DENSE_RANK()	Ranking with ties
SUM(), AVG(), MAX()	Aggregates across a window
LEAD() / LAG()	Access next/previous row values
NTILE(n)	Divides rows into n equal buckets
FIRST_VALUE() / LAST_VALUE()	Finds boundary values

🔍 Basic Syntax

<function>() OVER (
  PARTITION BY <column>
  ORDER BY <column>
  ROWS BETWEEN ... AND ...
)

PARTITION BY: Like GROUP BY, but keeps rows separate

ORDER BY: Defines the order within each partition

ROWS BETWEEN: Optional frame (default is “range to current row”)

📌 Example 1: ROW_NUMBER()

🧾 Get top 3 most recent orders per customer:

SELECT
  customer_id,
  order_id,
  order_date,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_date DESC
  ) AS row_num
FROM orders;

Then filter:

SELECT * FROM (
  -- use above query as subquery
) AS numbered
WHERE row_num <= 3;

📌 Example 2: Running Total

SELECT
  user_id,
  created_at,
  SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM transactions;

This gives a running total per user by date.

📌 Example 3: Difference Between Rows (LAG, LEAD)

SELECT
  user_id,
  amount,
  LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_amount,
  amount - LAG(amount, 1) OVER (...) AS diff
FROM transactions;

Useful for time-series, trends, deltas.

🔍 Window Functions vs. GROUP BY

Feature	GROUP BY	WINDOW FUNCTION
Collapses rows	✅ Yes	❌ No (returns all rows)
Can show aggregates + row data	❌ No	✅ Yes
Good for	Summarizing tables	Ranking, trends, comparisons

---

🧠 Summary

• Window functions let you calculate aggregates, rankings, and comparisons over a “sliding window” of rows
• They’re perfect for:
  • Leaderboards
  • Pagination
  • Running totals
  • Trend analysis