How to find an account balance in SQL?

How to find an account balance in SQL?

SQL interview questions and answers #2

Question:

You have a transaction_detail table with a record for each transaction. The transaction type could be debit, credit or refund. You need to add an additional column to track account balance.

Write a SQL query to achieve the expected output.

image.png

Answer:

Let us create the test data first.

-- create test data
USE demo;
GO
CREATE TABLE dbo.transaction_detail (
    date date,
    transaction_type varchar(50),
    amount money
    )
GO

INSERT INTO dbo.transaction_detail VALUES
('2022-01-01','credit',1000),
('2022-01-16','debit',300),
('2022-01-31','credit',150),
('2022-02-04','debit',50),
('2022-02-13','refund',50),
('2022-02-28','credit',400),
('2022-03-21','debit',500)
GO

To get the desired result, you can use aggregate function sum with window function to calculate the running total for each row in a window. But, we have not defined the window (i.e, partition by is not defined to define the window) here. Had we have to calculate the account balance for each account, we would have defined the window as account. So this query just calculates the running total for all the records in the table.

-- solution
SELECT 
date,
transaction_type,
amount,
balance = SUM(CASE WHEN transaction_type = 'debit' then amount * -1 
              else amount end) 
          OVER(ORDER BY date)
FROM dbo.transaction_detail


-- clean up
-- USE demo;
-- DROP TABLE dbo.transaction_detail

This is the solution I have come up with. If you have achieved the output in any other way, let me know in comments. Thank you.

Did you find this article valuable?

Support Rajanand Ilangovan by becoming a sponsor. Any amount is appreciated!