Monday, 27 April 2015

Interview Question - Write a query to produce required output

Recently, a friend of mine faced an interesting question in an interview which from the look of it looked worth sharing

Firstly, interviewer asked him about his comfort level in SQL scripting to which he replied as he is Alright but there is a lot of scope for an improvement. Then interviewer presented him with the following table 

and asked him to write a query to produce the following output within 3 minutes

I liked the time constraint put on to write the answer. This gives an idea to an interviewer how soon interviewee finds the pattern in the output to write a query. In such questions it is all about finding the pattern in the expected output.. 

Here it seems BALANCE for the ID is related to amount of the NEXT IDs in the table. This looks like a case of running total

Let us try write a query for it ... Firstly let us design the table with desired data 


Now that table is ready let us write the query for it 


This query is a nightmare when it comes to performance of the query but gets the job done. And interviewer gets his/her answer.

Another way of getting to the answer is using self join 


Can you guys try an write recursive CTE to get to the answer for the question ?

1 comment: