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: Start Balance Change Final Balance 0 +50 50 50 -15 35 35 +10 45 45 -5 40 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; Continue reading...
