Branch Deposits Against the Previous Calendar Month

Medium
62.

Branch Deposits Against the Previous Calendar Month

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

ColumnType
deposit_idint
branch_codevarchar
amountint
deposited_ondate

Primary key: deposit_id.

One row per cash or cheque deposit; amount is in rupees.

Example

The rows must come back in the order shown.

Deposit

deposit_idbranch_codeamountdeposited_on
1BLR001500002024-11-04
2BLR001250002024-11-28
3BLR001900002024-12-15
4BLR001400002025-02-03
5MUM0141200002024-12-31
6MUM014300002025-01-01
7MUM014150002025-01-20

Output

branch_codemonthmonth_totalprev_month_totalchange_amount
BLR0012024-1175000NULLNULL
BLR0012024-12900007500015000
BLR0012025-0240000NULLNULL
MUM0142024-12120000NULLNULL
MUM0142025-0145000120000-75000
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
deposit_idint
branch_codevarchar
amountint
deposited_ondate

Primary key: deposit_id.

One row per cash or cheque deposit; amount is in rupees.

Examples

Example 1

Deposit

deposit_idbranch_codeamountdeposited_on
1BLR001500002024-11-04
2BLR001250002024-11-28
3BLR001900002024-12-15
4BLR001400002025-02-03
5MUM0141200002024-12-31
6MUM014300002025-01-01
7MUM014150002025-01-20

Output

branch_codemonthmonth_totalprev_month_totalchange_amount
BLR0012024-1175000NULLNULL
BLR0012024-12900007500015000
BLR0012025-0240000NULLNULL
MUM0142024-12120000NULLNULL
MUM0142025-0145000120000-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.month

Another 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 →