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!

 

 

 

SQL JOIN – Duplicate & NULL Handling

CREATE TABLE table1 (
    id INT
);

INSERT ALL
    INTO table1 (id) VALUES (1)
    INTO table1 (id) VALUES (1)
    INTO table1 (id) VALUES (2)
    INTO table1 (id) VALUES (2)
    INTO table1 (id) VALUES (3)
    INTO table1 (id) VALUES (0)
    INTO table1 (id) VALUES (NULL)
SELECT 1 FROM DUAL;

table_1 contains 7 rows:

INSERT ALL

INTO table2 (id) VALUES (0)

INTO table2 (id) VALUES (0)

INTO table2 (id) VALUES (4)

INTO table2 (id) VALUES (5)

INTO table2 (id) VALUES (NULL)

INTO table2 (id) VALUES (NULL)

SELECT 1 FROM DUAL;

table_2 contains 6 rows:

2. Important Rule: Duplicate Values Multiply

For a normal equality join:

ON t1.id = t2.id

if a value occurs M times in Table 1 and N times in Table 2, the join produces:

M × N rows

For example, 0 occurs:

• Table 1 → 1 time
• Table 2 → 2 times

Therefore:

1 × 2 = 2 matching rows

Important: NULL = NULL is not TRUE in SQL. Therefore, NULLs do not match using t1.id = t2.id.

3. JOIN Results

INNER JOIN

SELECT t1.id AS table1_id,
       t2.id AS table2_id
FROM table1 t1
INNER JOIN table2 t2
    ON t1.id = t2.id;

Only matching values are returned.

The only matching value is 0.

Table 1: 0 → 1 occurrence
Table 2: 0 → 2 occurrences

1 × 2 = 2

INNER JOIN = 2 rows

TABLE1_ID	TABLE2_ID
0	0
0	0

LEFT JOIN

SELECT t1.id AS table1_id,
       t2.id AS table2_id
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.id = t2.id;

A LEFT JOIN preserves all rows from Table 1.

Table 1 Value	Table 1 Count	Matches in Table 2	Output
1	2	0	2
2	2	0	2
3	1	0	1
0	1	2	2
NULL	1	0	1
Total	7 - From Left table		8

Therefore:

LEFT OUTER JOIN = 8 rows

TABLE1_ID	TABLE2_ID
1	NULL
1	NULL
2	NULL
2	NULL
3	NULL
0	0
0	0
NULL	NULL

Notice that the NULL from Table 1 is still returned because a LEFT JOIN preserves every row from the left table.

RIGHT JOIN

SELECT t1.id AS table1_id,
       t2.id AS table2_id
FROM table1 t1
RIGHT JOIN table2 t2
    ON t1.id = t2.id;

A RIGHT JOIN preserves all rows from Table 2.

Table_2 Value	Table_2 Count	Matches in Table_1	Output
0	2	1	2
4	1	0	1
5	1	0	1
NULL	2	0	2
Total	6		6

Therefore:

RIGHT JOIN = 6 rows

4. FULL OUTER JOIN

SELECT t1.id AS table1_id,
       t2.id AS table2_id
FROM table1 t1
FULL OUTER JOIN table2 t2
    ON t1.id = t2.id;

A FULL OUTER JOIN returns:

1. Matching rows
2. Unmatched rows from Table 1
3. Unmatched rows from Table 2

Matching rows

0:

1 × 2 = 2 rows

Unmatched table_1 rows

1
1
2
2
3
NULL

6 rows

Unmatched table_2 rows

4
5
NULL
NULL

4 rows

Therefore:

2 matching rows
+ 6 unmatched Table 1 rows
+ 4 unmatched Table 2 rows
--------------------------------
= 12 rows

FULL OUTER JOIN = 12 rows

8. Final Result

JOIN Type	Number of Records
INNER JOIN	2
LEFT JOIN	8
RIGHT JOIN	6
FULL OUTER JOIN	12

---

9. Important Interview Concept – Duplicate Values

A JOIN does not simply return one row for each matching value.

When duplicate values exist, the matching rows are multiplied.

For example:

Table 1       Table 2
0             0
              0

Table 1 has one 0.

Table 2 has two 0s.

Therefore:

1 × 2 = 2 rows

If the data were:

Table 1       Table 2
0             0
0             0
              0

Then:

2 × 3 = 6 rows

This is known as the many-to-many matching effect.

---

10. Important Point About NULL

Another important concept is that:

NULL = NULL

does not evaluate to TRUE in SQL.

Therefore, the NULL value in Table 1 does not match the NULL values in Table 2 when using:

ON t1.id = t2.id

This is why the NULL values are treated as unmatched rows in the LEFT, RIGHT, and FULL OUTER JOIN examples.

---

Summary

The key numbers to remember from this example are:

INNER JOIN       → 2 records
LEFT JOIN        → 8 records
RIGHT JOIN       → 6 records
FULL OUTER JOIN  → 12 records

The two most important interview concepts are:

1. Duplicate matching values multiply the number of JOIN records.

2. NULL does not match NULL using the = operator.