Query to print the cumulative balance at the end of each day, with the total balance reset back to zero at the start of each month

 

💡 "Given all transactions for a merchant account, write a query to print the cumulative balance at the end of each day, with the total balance reset back to zero at the start of each month."
📌 Goal:
-For each day, show cumulative balance up to that day.
-Reset cumulative balance to zero at the start of every month (so no carry forward).

 

 

1. Table Structure

CREATE TABLE transactions (
    transaction_id INTEGER PRIMARY KEY,
    type VARCHAR(20),              -- deposit / withdrawal
    amount DECIMAL(10, 2),
    transaction_date TIMESTAMP
);

2. Sample Data

INSERT INTO transactions
(transaction_id, type, amount, transaction_date)
VALUES
(10001, 'deposit',    500.00, '2022-07-01 09:00:00'),
(10002, 'withdrawal', 150.00, '2022-07-01 15:00:00'),
(10003, 'deposit',    200.00, '2022-07-02 11:00:00'),
(10004, 'withdrawal',  50.00, '2022-07-03 14:00:00'),
(10005, 'deposit',    300.00, '2022-07-03 16:00:00'),
(10006, 'withdrawal', 100.00, '2022-07-04 10:00:00'),
(10007, 'deposit',    400.00, '2022-07-05 12:00:00'),
(10008, 'withdrawal', 200.00, '2022-07-05 18:00:00'),

(10009, 'deposit',    250.00, '2022-08-01 10:00:00'),
(10010, 'withdrawal', 100.00, '2022-08-02 11:00:00'),
(10011, 'deposit',    300.00, '2022-08-03 09:00:00'),
(10012, 'withdrawal',  50.00, '2022-08-03 13:00:00'),
(10013, 'deposit',    150.00, '2022-08-04 15:00:00'),
(10014, 'withdrawal',  75.00, '2022-08-05 16:00:00');

3. Interview Question

Given all transactions for a merchant account, calculate the cumulative daily balance, but reset the cumulative balance to zero at the beginning of every month.

For example:

July

• July 1 → 500 - 150 = 350

• July 2 → 350 + 200 = 550

• July 3 → 550 - 50 + 300 = 800

• July 4 → 800 - 100 = 700

• July 5 → 700 + 400 - 200 = 900

When August starts, the balance does not carry forward 900.

August

• August 1 → 250

• August 2 → 250 - 100 = 150

• August 3 → 150 + 300 - 50 = 400

• August 4 → 400 + 150 = 550

• August 5 → 550 - 75 = 475

4. SQL Solution

WITH daily_transactions AS
(
    SELECT
        DATE(transaction_date) AS transaction_day,
        DATE_FORMAT(transaction_date, '%Y-%m') AS transaction_month,

        SUM(
            CASE
                WHEN type = 'deposit' THEN amount
                WHEN type = 'withdrawal' THEN -amount
                ELSE 0
            END
        ) AS daily_net_amount

    FROM transactions

    GROUP BY
        DATE(transaction_date),
        DATE_FORMAT(transaction_date, '%Y-%m')
)

SELECT
    transaction_day,
    transaction_month,
    daily_net_amount,

    SUM(daily_net_amount) OVER
    (
        PARTITION BY transaction_month
        ORDER BY transaction_day
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_balance

FROM daily_transactions

ORDER BY transaction_day;

5. Expected Output

transaction_day	transaction_month	daily_net_amount	cumulative_balance
2022-07-01	2022-07	350.00	350.00
2022-07-02	2022-07	200.00	550.00
2022-07-03	2022-07	250.00	800.00
2022-07-04	2022-07	-100.00	700.00
2022-07-05	2022-07	200.00	900.00
2022-08-01	2022-08	250.00	250.00
2022-08-02	2022-08	-100.00	150.00
2022-08-03	2022-08	250.00	400.00
2022-08-04	2022-08	150.00	550.00
2022-08-05	2022-08	-75.00	475.00

6. How the SQL Works

Step 1 — Convert withdrawals to negative values

CASE
    WHEN type = 'deposit' THEN amount
    WHEN type = 'withdrawal' THEN -amount
END

So:

deposit     500  → +500
withdrawal  150  → -150

Step 2 — Calculate the daily net amount

SUM(...)
GROUP BY DATE(transaction_date)

For July 3:

Withdrawal = -50
Deposit    = +300

Daily Net = 250

Step 3 — Calculate the running balance

SUM(daily_net_amount) OVER (...)

This produces the cumulative total.

Step 4 — Reset at the beginning of every month

The key part is:

PARTITION BY transaction_month

Conceptually:

July partition
-----------------------------
Jul 1   350
Jul 2   550
Jul 3   800
Jul 4   700
Jul 5   900


August partition
-----------------------------
Aug 1   250    <-- RESET
Aug 2   150
Aug 3   400
Aug 4   550
Aug 5   475

The PARTITION BY creates a separate running-total calculation for each month.

7. PostgreSQL Version

In PostgreSQL, you can simplify the date handling with DATE_TRUNC:

WITH daily_transactions AS
(
    SELECT
        DATE_TRUNC('day', transaction_date)::DATE AS transaction_day,
        DATE_TRUNC('month', transaction_date)::DATE AS transaction_month,

        SUM(
            CASE
                WHEN type = 'deposit' THEN amount
                WHEN type = 'withdrawal' THEN -amount
                ELSE 0
            END
        ) AS daily_net_amount

    FROM transactions

    GROUP BY
        DATE_TRUNC('day', transaction_date),
        DATE_TRUNC('month', transaction_date)
)

SELECT
    transaction_day,
    transaction_month,
    daily_net_amount,

    SUM(daily_net_amount) OVER
    (
        PARTITION BY transaction_month
        ORDER BY transaction_day
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_balance

FROM daily_transactions

ORDER BY transaction_day;

⭐ Interview Takeaway

The important pattern to remember is:

SUM(daily_amount) OVER (
    PARTITION BY month
    ORDER BY day
)

Think of it as:

GROUP BY day → calculate daily net → PARTITION BY month → ORDER BY day → running SUM

This is a very common window-function + aggregation interview problem and is useful for banking, payments, merchant settlements, and financial reporting scenarios.

 

This is one of the classic real-world questions involving cumulative sums and monthly resets, testing both SQL logic and analytical thinking. Let's break it down!

📊 Breaking Down the SQL Solution (Step by Step)
✅ #CTE (WITH): To summarize daily net balances.
✅ #CASE_WHEN: Treat withdrawals as negative, deposits as positive.
✅ #SUM & #GROUP_BY: Aggregate daily net balance.
✅ #DATE & #DATE_FORMAT: Extract day & month for grouping.
✅ Window Function (#SUM_OVER): Cumulative running balance.
✅ #PARTITION_BY: Reset balance per month.
✅ #ORDER_BY: Day-wise accumulation.

🔥 Bonus Tip:
If you're working on PostgreSQL or BigQuery etc. use #DATE_TRUNC('day', transaction_date) or #DATE_TRUNC('month', transaction_date) to achieve similar date grouping!