SQL Query Question Asked During Microsoft Interview

 If you are someone who likes to brian storm on SQL query solutions, you would like this post. 

Some time back, one of my friend reached out to me to get some help on a SQL query question that was asked to him during an interview with Microsoft (if you know what I mean :-) ).

Keep reading.

There are 2 tables, one Accounts and the other Transactions with entries as shown below. 

Here D is for Deposit and W for Withdrawal

Expected Output

The accounts table reflect the balance before these transactions happened. So, essentially we have to generate an account statement that would look something like the above.

At first glance, it looks pretty straightforward, especially the Credit and Debit part. However, the real challenge is that you need to calculate account balance as a running calculation. 

Pseudo-Code 

You first have to calculate the balance from the first transaction.
Then just add or subtract the respective transaction amounts based on if it's a withdrawal or deposit. 

Going into technical details 

1. Add a row number based on the transaction date. This would help us identify the first transaction.
2. For the first transaction calculate the net balance.
3. Use the net balance from the first transaction and add or subtract the transaction amount based on debit or credit in a rolling fashion. 

Source - Code 

WITH CTE

     AS (SELECT b.AccountNo, 

b.TransactionDate, 

a.Balance, 

b.TransactionType, 

b.TransactionAmount, 

ROW_NUMBER() OVER(

ORDER BY b.TransactionDate ASC) rn

         FROM Account a

              JOIN Transactions b ON a.AccountNo = b.AccountNo),

FinalCTE

AS (SELECT AccountNo, 

TransactionDate,

CASE

WHEN TransactionType = 'D'

THEN TransactionAmount

ELSE NULL

END AS Credit,

CASE

WHEN TransactionType = 'W'

THEN TransactionAmount

ELSE NULL

END AS Debit,

CASE

WHEN TransactionType = 'D'

THEN Balance + TransactionAmount

ELSE Balance - TransactionAmount

END AS Balance

FROM cte

WHERE rn = 1

UNION

SELECT AccountNo, 

TransactionDate,

CASE

WHEN TransactionType = 'D'

THEN TransactionAmount

ELSE NULL

END AS Credit,

CASE

WHEN TransactionType = 'W'

THEN TransactionAmount

ELSE NULL

END AS Debit,

CASE

WHEN TransactionType = 'D'

THEN+TransactionAmount

ELSE-TransactionAmount

END AS Balance

FROM cte

WHERE rn > 1)

SELECT AccountNo, 

TransactionDate, 

Credit,

Debit,

SUM(Balance) OVER(

ORDER BY TransactionDate) as Balance

FROM FinalCTE;

Please do suggest if there is a simpler way to do this!

Thank you

Source-Code for simulation

CREATE TABLE Account(AccountNo INT, [Name] VARCHAR(10), Balance INT)

INSERT INTO Account VALUES (101,'Venkat', 1000)

CREATE TABLE Transactions(AccountNo INT, TransactionID INT, TransactionDate Date, TransactionType CHAR(1), TransactionAmount INT)

INSERT INTO Transactions VALUES (101,1,'4/1/2011','D', 300)

INSERT INTO Transactions VALUES (101,2,'4/2/2011','W', 500)

INSERT INTO Transactions VALUES (101,3,'4/3/2011','W', 200)

INSERT INTO Transactions VALUES (101,4,'4/4/2011','D', 44)