Window Functions — ROW_NUMBER, RANK, LAG, LEAD

Window functions — analytics-এর প্রাণ

Read: ~40 min Advanced 20 practice problems Live SQLite runner

1. The Problem `GROUP BY` Cannot Solve

Every student in the CSE department wants to know their rank by CGPA, and they want to see their own row alongside that rank. With plain GROUP BY, you cannot do this — grouping collapses rows. The 30 individual students disappear into one summary row.

GROUP BY ব্যবহার করলে individual row হারিয়ে যায়; কেবল প্রতি গ্রুপের সারাংশ থাকে। কিন্তু আমরা প্রায়ই চাই — প্রত্যেকটি row রেখেই, পাশে পাশে aggregate বা rank দেখাতে। এই সমস্যার সমাধান window function।

A window function computes an aggregate over a "window" of rows without collapsing them. It returns one value per input row, not one per group. This single feature is why modern SQL is taken seriously as an analytics language.

SQLite support Window functions have been supported in SQLite since version 3.25.0 (Sep 2018). Every example below runs in your browser via sql.js.

2. The Shape of a Window Call

The general syntax is:

function_name(args) OVER (PARTITION BY ... ORDER BY ... <frame>)

function_name — any aggregate (SUM, AVG, COUNT...) or a window-only function (ROW_NUMBER, RANK, LAG, LEAD, NTILE, FIRST_VALUE, ...).
OVER (...) — turns the call into a windowed call. Always required.
PARTITION BY — splits rows into independent windows. Optional; if omitted the whole result is one window.
ORDER BY — orders rows inside each window. Required by ranking and offset functions.
frame — restricts the window to a sliding range of rows around the current row (more in §6).

OVER-ই হলো magic keyword। যেই function-এর পরে OVER (...) লিখা হয় সেটি window mode-এ চলে যায়। PARTITION BY = কোন কলামের ভিত্তিতে গ্রুপ; ORDER BY = সেই গ্রুপে কীভাবে সাজাবে।

3. Ranking — `ROW_NUMBER`, `RANK`, `DENSE_RANK`

All three assign integers to rows in window order. They differ only in how they handle ties.

Function	Behaviour on ties	Example: 90, 90, 80
`ROW_NUMBER()`	Always unique — ties broken arbitrarily.	1, 2, 3
`RANK()`	Same rank for ties; skips the next.	1, 1, 3
`DENSE_RANK()`	Same rank for ties; does not skip.	1, 1, 2

ranking.sql

-- Three ranking functions side-by-side, partitioned by department.
SELECT
    name, dept, cgpa,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY cgpa DESC) AS rn,
    RANK()       OVER (PARTITION BY dept ORDER BY cgpa DESC) AS rk,
    DENSE_RANK() OVER (PARTITION BY dept ORDER BY cgpa DESC) AS drk
FROM student
ORDER BY dept, cgpa DESC;

Top-N per group "Top 3 students of every department" is the canonical use of ROW_NUMBER(): rank within partition, then keep rn <= 3 in an outer query / CTE.

4. Offset Functions — `LAG` & `LEAD`

LAG(col, n) returns the value of col from n rows before the current row in the window order. LEAD(col, n) goes forward. Indispensable for time-series analysis (sales today vs yesterday, status transitions, etc.).

lag_lead.sql

-- Day-on-day change with LAG.
SELECT
    d,
    revenue,
    LAG(revenue) OVER (ORDER BY d)            AS prev_day,
    revenue - LAG(revenue) OVER (ORDER BY d)  AS day_diff,
    LEAD(revenue) OVER (ORDER BY d)           AS next_day
FROM daily_sales
ORDER BY d;

LAG দিয়ে গতকালের value, LEAD দিয়ে আগামীকালের value পাওয়া যায়। এর সাহায্যে সহজেই "day-over-day", "week-over-week" বা status transition বিশ্লেষণ করা যায়।

5. Aggregates as Windows — Running Totals & Group Shares

Any aggregate (SUM, AVG, COUNT, MAX...) becomes a window function the moment you add OVER (...). You no longer collapse rows; you spread the aggregate across each row of the window.

running_total.sql

-- Running revenue total + share of weekly total per day.
SELECT
    d,
    revenue,
    SUM(revenue) OVER (ORDER BY d)                       AS running_total,
    ROUND(100.0 * revenue / SUM(revenue) OVER (), 2)        AS pct_of_week
FROM daily_sales
ORDER BY d;

Empty OVER () Writing SUM(revenue) OVER () with no PARTITION BY and no ORDER BY means "the whole result is one window" — perfect for "total of everything" alongside each row.

6. Frame Clauses — `ROWS BETWEEN ...`

By default, when you use ORDER BY inside OVER, the frame is "from the start of the partition to the current row". That gives you running totals. If you want a moving window instead — like a 3-day average — write the frame explicitly.

moving_avg.sql

-- 3-day moving average — the current row + the 2 before it.
SELECT
    d,
    revenue,
    ROUND(
        AVG(revenue) OVER (
            ORDER BY d
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 1
    ) AS moving_avg_3d
FROM daily_sales
ORDER BY d;

Frame	Meaning
`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	Default with `ORDER BY`: running total.
`ROWS BETWEEN N PRECEDING AND CURRENT ROW`	Last N rows + current — moving average / sum.
`ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING`	Prev + current + next — 3-row smoother.
`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`	The whole partition — no `ORDER BY` effect.

7. `GROUP BY` vs Window Functions

GROUP BY (সংক্ষেপ)

Collapses rows to one per group.
Output has fewer rows than input.
Cannot show row-level detail.
Used for summary reports.

Window Function (প্রতিটি row রক্ষা)

Keeps every row in the input.
Output has same number of rows.
Computes per-row analytics.
Used for ranking, running totals, deltas.

8. Glossary (শব্দকোষ)

Term	Meaning	বাংলায়
Window	Set of rows visible to the function for the current row.	বর্তমান row-এর জন্য দৃশ্যমান row-এর সেট।
PARTITION BY	Splits rows into independent windows.	আলাদা আলাদা window-এ row ভাগ করে।
Frame	Subset of the window relative to the current row.	বর্তমান row-এর চারপাশে window-এর একটি অংশ।
ROW_NUMBER	Unique sequential integer per window order.	প্রতিটি row-এর জন্য আলাদা ক্রমিক সংখ্যা।
LAG / LEAD	Value from a previous / next row in the window.	পূর্ববর্তী / পরবর্তী row-এর value।

9. Practice Problems

Show every student with their global rank by CGPA (1 = best). Use RANK().

প্রতিটি ছাত্রের সাথে CGPA অনুযায়ী global rank দেখাও।
✨ Show Answer
ans1.sql
```
SELECT name, dept, cgpa,
       RANK() OVER (ORDER BY cgpa DESC) AS overall_rank
FROM student
ORDER BY overall_rank;
```

Get the top 2 students per department by CGPA.

প্রতিটি বিভাগের শীর্ষ 2 জন ছাত্র।

✨ Show Answer

ans2.sql

WITH ranked AS (
    SELECT name, dept, cgpa,
           ROW_NUMBER() OVER (PARTITION BY dept ORDER BY cgpa DESC) AS rn
    FROM student
)
SELECT name, dept, cgpa
FROM ranked
WHERE rn <= 2
ORDER BY dept, cgpa DESC;

For the daily_sales table, show each day's revenue, the previous day's revenue, and the day-over-day percentage change.

প্রতিটি দিনের revenue, আগের দিনের revenue, এবং day-over-day পরিবর্তন শতকরা।
✨ Show Answer
ans3.sql
```
SELECT
    d, revenue,
    LAG(revenue) OVER (ORDER BY d) AS prev,
    ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY d))
            / LAG(revenue) OVER (ORDER BY d), 2) AS pct_change
FROM daily_sales
ORDER BY d;
```

Compute a 3-day moving average of revenue for daily_sales.

3-day moving average revenue।

✨ Show Answer

ans4.sql

SELECT d, revenue,
       ROUND(AVG(revenue) OVER (
           ORDER BY d
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS ma3
FROM daily_sales
ORDER BY d;

For each student, show the difference between their CGPA and the average CGPA of their department.

প্রতিটি ছাত্রের CGPA এবং তার বিভাগের গড় CGPA-র পার্থক্য।
✨ Show Answer
ans5.sql
```
SELECT name, dept, cgpa,
       ROUND(AVG(cgpa) OVER (PARTITION BY dept), 3) AS dept_avg,
       ROUND(cgpa - AVG(cgpa) OVER (PARTITION BY dept), 3) AS diff
FROM student
ORDER BY dept, diff DESC;
```
In one sentence, explain when you'd reach for a window function instead of GROUP BY.

এক বাক্যে বলুন — কখন GROUP BY-এর পরিবর্তে window function ব্যবহার করবেন?

✨ Show Answer

Answer: Whenever the report needs to keep every input row visible while still attaching aggregate / ranking / offset values to each row.

যখন প্রতিটি input row বাঁচিয়ে রেখেই তার পাশে aggregate / rank / offset value দেখাতে হবে — তখনই window function।

Summary — Module 20

Window functions are aggregates that do not collapse rows. The shape is fn(...) OVER (PARTITION BY ... ORDER BY ... <frame>). Use ranking (ROW_NUMBER, RANK, DENSE_RANK) for top-N reports, offset (LAG, LEAD) for time-series deltas, and aggregates (SUM, AVG) with frames for running totals and moving averages.

Window function = row হারানো ছাড়াই aggregate; modern SQL-এর সবচেয়ে শক্তিশালী feature। OVER ()-ই magic — বাকি সব এর variation।

Next Module → Stored Procedures, Functions & Triggers — logic that lives in the database.

Prev: Views & CTEs Next: Procedures & Triggers

1. The Problem GROUP BY Cannot Solve

2. The Shape of a Window Call

3. Ranking — ROW_NUMBER, RANK, DENSE_RANK

4. Offset Functions — LAG & LEAD

5. Aggregates as Windows — Running Totals & Group Shares

6. Frame Clauses — ROWS BETWEEN ...

7. GROUP BY vs Window Functions

GROUP BY (সংক্ষেপ)

Window Function (প্রতিটি row রক্ষা)

8. Glossary (শব্দকোষ)

9. Practice Problems

Summary — Module 20

1. The Problem `GROUP BY` Cannot Solve

3. Ranking — `ROW_NUMBER`, `RANK`, `DENSE_RANK`

4. Offset Functions — `LAG` & `LEAD`

6. Frame Clauses — `ROWS BETWEEN ...`

7. `GROUP BY` vs Window Functions