Branch Deposits Against the Previous Calendar Month
Branch managers review their monthly deposit inflow against the immediately preceding calendar month.
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #62
Problem statement
Branch managers review their monthly deposit inflow against the immediately preceding calendar month. A branch may have no deposits in some months; then the next month has no previous figure to compare with.
For every branch and month with at least one deposit, return branch_code, month ('YYYY-MM'), month_total, prev_month_total (the branch's total in the calendar month just before, or NULL if it had no deposits then) and change_amount (month_total − prev_month_total, NULL when there is no previous total). Order the rows by branch_code, then by month.
Tables
Table: Deposit
| Column | Type |
|---|---|
| deposit_id | int |
| branch_code | varchar |
| amount | int |
| deposited_on | date |
Primary key: deposit_id.
One row per cash or cheque deposit; amount is in rupees.
Examples
Example 1
Deposit
| deposit_id | branch_code | amount | deposited_on |
|---|---|---|---|
| 1 | BLR001 | 50000 | 2024-11-04 |
| 2 | BLR001 | 25000 | 2024-11-28 |
| 3 | BLR001 | 90000 | 2024-12-15 |
| 4 | BLR001 | 40000 | 2025-02-03 |
| 5 | MUM014 | 120000 | 2024-12-31 |
| 6 | MUM014 | 30000 | 2025-01-01 |
| 7 | MUM014 | 15000 | 2025-01-20 |
Output
| branch_code | month | month_total | prev_month_total | change_amount |
|---|---|---|---|---|
| BLR001 | 2024-11 | 75000 | NULL | NULL |
| BLR001 | 2024-12 | 90000 | 75000 | 15000 |
| BLR001 | 2025-02 | 40000 | NULL | NULL |
| MUM014 | 2024-12 | 120000 | NULL | NULL |
| MUM014 | 2025-01 | 45000 | 120000 | -75000 |
How to solve Branch Deposits Against the Previous Calendar Month
Start by aggregating: one row per branch and DATE_FORMAT(deposited_on, '%Y-%m') month with the summed amount. The comparison then needs, for each of those rows, the row for the previous calendar month of the same branch, if it exists.
LAG(total) OVER (PARTITION BY branch_code ORDER BY month) is the obvious tool, but it returns the previous row. If a branch had nothing in December, January's lag would be November's total — the wrong comparison. The window alternative fixes that by also lagging a month index (YEAR × 12 + MONTH) and only using the lagged total when the index is exactly one less, which also handles the January/December year boundary.
The reference solution sidesteps the problem by joining the monthly table to itself on the label of the previous month, computed with DATE_SUB(CONCAT(month, '-01'), INTERVAL 1 MONTH). A LEFT JOIN leaves prev_month_total NULL when there is no such month, and total − NULL is NULL, as required. Both approaches are one aggregation plus a small join or sort.
Reference solution (MySQL)
WITH monthly AS (
SELECT branch_code, DATE_FORMAT(deposited_on, '%Y-%m') AS month, SUM(amount) AS total
FROM Deposit
GROUP BY branch_code, DATE_FORMAT(deposited_on, '%Y-%m')
)
SELECT c.branch_code, c.month, c.total AS month_total, p.total AS prev_month_total, c.total - p.total AS change_amount
FROM monthly c
LEFT JOIN monthly p
ON p.branch_code = c.branch_code
AND p.month = DATE_FORMAT(DATE_SUB(CONCAT(c.month, '-01'), INTERVAL 1 MONTH), '%Y-%m')
ORDER BY c.branch_code, c.monthAnother way
WITH monthly AS (
SELECT branch_code, DATE_FORMAT(deposited_on, '%Y-%m') AS month, YEAR(deposited_on) * 12 + MONTH(deposited_on) AS mi, SUM(amount) AS total
FROM Deposit GROUP BY branch_code, DATE_FORMAT(deposited_on, '%Y-%m'), YEAR(deposited_on) * 12 + MONTH(deposited_on)
), lagged AS (
SELECT branch_code, month, mi, total,
LAG(mi) OVER (PARTITION BY branch_code ORDER BY mi) AS prev_mi,
LAG(total) OVER (PARTITION BY branch_code ORDER BY mi) AS prev_total
FROM monthly
)
SELECT branch_code, month, total AS month_total,
CASE WHEN prev_mi = mi - 1 THEN prev_total END AS prev_month_total,
CASE WHEN prev_mi = mi - 1 THEN total - prev_total END AS change_amount
FROM lagged ORDER BY branch_code, month← Salary Credits by Employer From Statement Narrations · Inoperative Accounts Under the Two-Year Rule →