 Hey guys welcome to SS Unitex, so we will decide and this is continuation of SQL Server interview questions and answers. So today we are going to discuss one more interview question. So this is basically like average of differences between two dates. So here let's assume we have input table and that would have three columns as you can see in this slide. So the first column that is the customer ID, second is the order date and third is the sales amount. So what we need to do, we need to take the average of the sales between two days. Let's assume as you can see the first row and the second row. So the first row that is the first of Jan 2020 and second row will be the second of Jan 2020. So we need to take the difference between these two order dates and after that go to on the sales and we need to take the difference of these two. And what difference we will get that we need to add one more column and after that we need to take the average of those. So let's understand the logic inside the Excel. So go to on the Excel. So as you can see inside this Excel we have the same metadata. The first is the customer ID, order date and sale. So these three columns are coming from the SQL Server Table. So first we need to calculate the previous row of these dates. So here we need to see the previous date of this date. So that is not available. So that's why here you can see the null. The next we need to get the previous row. So that is the 5800 so that is available over here. Going forward we need to do the same for every row. And after that we need to take the difference between the current row and the previous row. So as you can see it is not available. So by default we are going to treat this as 0. Other rows we are having all these values. So first we need to create this previous row column and after that we need to create this difference column. And then we can do the average of this difference column on the basis of this customer ID. So as you can see for customer ID 1 it is having 1075. For customer ID 2 it is 553 and for customer ID 3 it is 728. So like this is what we need to do. First we need to calculate the previous row then difference then take the average and we need to get this average in your output. So how we can achieve this? Go to the SQL Server and let's try to write the SQL statement. So first of all here you can see we are having this table. So we need to add a column that will be going to have the previous row of data. So for that we can use the lag function. So we can use the lag and after that what column we want? So we want the sales column then it is asking the offset. So offset value is 1 so like the previous row we want the data for that. Then default value we can put this as 0 and we can close the bracket or we can skip the last parameter. After that we can use the over clause then we can start the bracket and end the bracket. In between this bracket we can do the partition by as per the customer ID. So we can have this partition by on the customer ID and after that we can use the order by clause. Here we can use the order date in the descending order and we can call this as previous row. Now let me execute. So here we have the error like underscore should be here now we can execute. So as we can see we have created this previous row so it looks good. Now we need to take the difference between the current row and this previous row. So how we can do that the same thing we can do like the sales minus the previous row now we can execute it. So as we can see we are having the null then all these values are available. So here the values are coming in the negative because we are going to subtract the current one with the previous one and the previous which will be like the next row it is on the descending order. So instead of subtracting from this sales to the lag we can use lag minus the sales. Now we can see so it should have the data as per our expectation. So it looks good. Now what next we need to do? We need to use the customer ID and after that we can take the average then start the bracket and go to the last we can close the bracket. So that looks good. Here we can use the group by with this customer ID but it will have the problem. Let me try to execute and we will see. So as we can see it is saying we are going to execute this either it is not our labor or here but this is not the problem problem with the second one like windows function cannot use the context another windows function or aggregate. So like here we are going to use the lag so inside the lag we cannot use the average function. So we can remove that and here let me call this as difference. Now we can put this inside a bracket and let me call this as a here we can call this select customer ID put comma then average here we can use the difference and we can call this as average say after that we can put from. Now in the group by we have to use this so the last statement it should be available outside this bracket like here now we can execute it. So as we can see we are having the value that is 1075552727 if we can compare with the axle. So here like we are having little bit difference let me try to copy this go to here and try to paste it here. So for 1 it is 1075 that is fine but here we can see 553 here we have 552 this is just because of decimal places if we can see the decimal places so it is having 552.5 and in case of 3 it is 727.5 inside the sico server we are going to use as integer so that's why we cannot see the decimal places over there. So like this is the output as we were expecting I hope guys you have understand how we can use it. So thank you so much for watching this video if you like this video please subscribe our channel to get many more videos don't forget to press the bell I can to get the notification of our newly uploaded videos thank you so much see you in the next video.