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 

DECLARE @TAB TABLE (ID TINYINT,AMOUNT SMALLINT)INSERT INTO @TABSELECT 1,110UNION ALLSELECT 2,-120UNION ALLSELECT 3,40UNION ALLSELECT 4,-175UNION ALLSELECT 5,-55

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

SELECT * ,(SELECT SUM(AMOUNTFROM @TAB T2 WHERE T2.ID <= T1.ID )  AS BALANCEFROM @TAB T1

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 

SELECT T1.ID,T1.AMOUNT,SUM(T2.AMOUNTAS AMOUNTFROM @TAB T1INNER JOIN @TAB T2ON T1.ID >= T2.IDGROUP BY T1.ID,T1.AMOUNTORDER BY ID

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

1 comment:

bloggerwidgets