 Hello, welcome to SSRU today social this site and this is continuation of SQL Server interview questions and answers. So today we are going to discuss one more interview question that was asked in Essenture. So recently one of my friend has attended interview with Essenture. So this question was asked with that guy. So before jumping to next slide, if you haven't subscribed my channel, so I would request to you all please subscribe my channel and press the bell icon. So you will get the update of my newly uploaded videos. So let's move to next slide. As we can see we are having an input table and it has only one column. So the values on this column is a numeric value that is 123456789 and 10. In the output again we are expecting only one column that would have the values of 3, 6, 9, 12, 15, 18, 21, 24, 27 and 19. So what is the logic? The logic is very simple. As we can see the first row in the output is 3. So it's going to combine the first row and second row in the input that is 1 plus 2. So the output is 3. Now move to second row that is 6. So in the 6 we are going to combine the values of first row, second row and third row. So we can see 1 plus 2 plus 3 is equals to 6. Now move to third row that is 9. So in case of 9 it will be going to combine the second row that is 2, then the third row that is 3, then the next row that is 4. So once we combine then we will get 9. So this is the logic in the last row as we can see we are having only 19. So it will combine the 10 plus 9, so only 2 rows. So in case of first row it will combine first row and second row and in case of last row it will combine the last and second last row. So how we can write the query for getting this output? So first I would request pause this video and think about this how we can write the simplest way to get the output. So let me tell you the logic. So here we can get that value which is on that row like 1, then we are expecting the previous value of this row so that is nothing so we will put 0 and the next row that is 2. So we will combine 1 plus 0 plus 2 to get 3. Once we move to second row then the previous value that is 1 plus 2 plus 3 that is 6. In case of 3 it will combine 2, 3 and 4 and we will get 9. So this is the logic. So we will include that row and previous row and the next row and we will add those rows for getting the output. As you can see in the last row we are having 10 so we don't have the next row. So put that value as 0 and we will combine the previous value that is 9 and current value that is 10 and next value will be 0 so output will be 19. So we can use the previous value plus current value plus next value for getting the output. So let's move to SQL Server Management Studio where we will write the query and get this output. So I am using SQL 2017 you may have another version of SQL Server. So first of all let me execute this query and create the table and insert data on the table. Let me write select a statement to check the output of this table. So we are having 1 to 10 values that is good. So how we can get the previous value and next value as I told you. So for that we can use lead function for the next value and lag function for the previous value. So first let me use the lead function. So this is the lead function and after that it is expecting the column. So we are having this column that is value comma. How many lead values we are expecting? So we are expecting very next value so that will be 1 comma. If next value is not available then what value we want to put? So we want to put 0 and here again we have to use the over clause. So after that here we can use the partition by or order by. So in my case we don't use to partition by just use order by. Order by on this value. Now let me execute this query. So here we are having 1 the next value is 2 and 2 the next value is 3. In case of 10 the next value is not available so we have replaced that value is 0. So this is for the lead function. Now let me use the lag function for the same. So here it is again expecting the column so that is value lag value. So we just want to the previous value so we will put 1 then default value will be 0. After that we have to use the over clause. So over after that we have to use the order by. So order by and here we have to use the column. Now it looks good. Let me execute this. So now we can see here we are having lead value and here we are having the lag values. If we will combine these values like 1 2 plus 0 so we will get the output 2 3 and 1 for the second row and 3 4 2 for the very next row. So once we will combine these values so we will get the output as we are expecting. So let me combine and check the output. So the first column is value so value plus lead value after that plus lag value. So let me execute. So this is the input and this is output. So as we can see this is the same as we are expecting 3 6 9 12 15 18 21 24 27 and 19. So this is the output as we are expecting. So thank you so much for watching this video. I hope you have understand about this question. If you need this script it will be available on the description of this video so you can copy from there. Thank you so much.