Analyze sequence and month-on-month change — Smart Study Module 11

Compare recoveries over time without comparing a single event with an entire month.

Outcomes

• Rank within a partition
• Distinguish RANK from ROW_NUMBER
• Calculate growth after monthly aggregation

Understand

Window functions preserve the input rows while calculating over a partition. ROW_NUMBER gives a unique order; RANK gives ties the same rank and leaves gaps. Supply a deterministic tie-breaker for ROW_NUMBER.

LAG accesses a prior row in the specified order, not necessarily the previous calendar month. Aggregate by month first, and join a calendar if missing months must appear.

Specify a ROWS frame for cumulative amounts when each event should be added one at a time. Guard division by zero; a first period with no prior value should normally be unavailable, not an invented 0% improvement.

Follow along

1. Group payments by substr(payment_date,1,7).
2. Use LAG on monthly totals.
3. Calculate February growth relative to January.
4. Build a per-loan running amount ordered by date and payment_id.

Worked example

WITH m AS (SELECT substr(payment_date,1,7) AS month,SUM(amount) AS collected FROM payments GROUP BY 1),
v AS (SELECT *,LAG(collected) OVER (ORDER BY month) AS prior FROM m)
SELECT month,collected,prior,ROUND(100.0*(collected-prior)/NULLIF(prior,0),4) AS growth_pct FROM v;

January=48000, February=50000; February growth=4.1667%. January prior/growth are NULL.

Practice

Enter February minus January collections in rupees.

Create a free personalized study plan · Download project instructions

Interactive practice runs in your browser.