Get mutual settlements from records using SQL

How to Generate Mutual Settlements for a Specific Employee in SQL?

I have a table where I track mutual settlements for employees. The table contains the following columns:

• employee_id: The ID of the employee
• amount: The transaction amount (can be positive or negative, based on the reason)
• reason: A description of the transaction
• created_at: The timestamp when the transaction occurred

I need to generate a report showing how an employee's balance changes over time, like this:

My SQL Query (Not Working as Expected)

I tried using a window function to calculate the previous balance, change, and final balance, but the results are incorrect:

SELECT 
    id,
    employee_id,
    COALESCE(LAG(SUM(amount)) OVER (ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS prev_amount,
    amount AS change,
    SUM(amount) OVER (ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS final_amount
FROM settlement_settlement
GROUP BY id
ORDER BY created_at;

LAG(SUM(amount)) does not work as expected because SUM(amount) is an aggregate function, and LAG() operates on individual rows. Instead, you should use LAG(final_balance) OVER (...) to get the previous row’s cumulative sum.

CREATE TABLE employee_settlement ( employee_id int, amount int, created_at date);


insert into employee_settlement values(1,950,'2024-12-01');
insert into employee_settlement values(1,1000,'2025-01-01');
insert into employee_settlement values(1,1050,'2025-02-01');

The actual query :

WITH balance_cte AS (
    SELECT 
        employee_id,
        created_at,
        amount AS change,
        SUM(amount) OVER (PARTITION BY employee_id ORDER BY created_at) AS final_balance
    FROM employee_settlement
    WHERE employee_id = 1 -- Replace with a specific employee_id
)
SELECT 
    employee_id,
    COALESCE(LAG(final_balance) OVER (PARTITION BY employee_id ORDER BY created_at), 0) AS start_balance,
    change,
    final_balance
FROM balance_cte
ORDER BY created_at;

Output: