 Hello, welcome back to this side and this is continuation of 50 days SQL query challenges. So thanks for your support in the first video. I would request to you all can you please also support in this video as well. So please like this video, do subscribe our channel and share to others. It will always motivate me to record more and more videos. So let's start with today's video. So this is day 2 of 50 days SQL query challenges. So here we can see we have one of the input data and this table is having total 4 columns. First is the machine ID, second is the process ID, third is the activity type and fourth is the timestamp. In the output we just want to have only 2 columns. First will be your machine ID and second will be your average processing time. So the question is can you please write a query and for getting average processing time from input table that you can see here. So for getting the average processing time first here we can analyze about this table. So it is having the machine ID and let's focus on machine ID 0. So in machine ID 0 this machine was started twice. Here the start and end along with the process ID is indicating how many times this machine is started. So process ID 0 is indicating it was started the first time. Process ID 1 is indicating it is starting in second time. Here we can see the start time and end time. So we can simply do the subtract between end time to start time and then we can take the average on the machine ID for getting this output. So you can pause this video and you can think how we can write this SQL query and what will be the logic that we can apply for getting this output. So let's start. So here first what we can do we can create two columns on the basis of this activity type. First column will be your start time and second column will be your end time. So the start time we can simply check if the activity type is the start then whatever the time stamp for those will be your start time. In the end time if the activity type is end then whatever the time stamp value that will be your end time. So these two columns we can create. And after that we can take the max on this start time and end time those two columns that we have created on the basis of machine ID and the process ID. And then after we can simply do the subtract with end time to start time because end time and start time will be coming in a single line. So we can do the subtract and then we can take the average. So let's jump to SQL Server Management Studio where we will write the query for getting this output. So here I have written this script for saving our time so let's try to execute this query. So here table has created and it is also having the data. So we can verify so we can write the selected statement with this fact machine table. So let me execute this selected statement and it is having the same data that we have seen in the slide. Now next what we can do we can simply create two columns that we were talking about. So what we can create we can simply use the case statement. So for using the case statement we can simply write the case when your activity type if your activity type is start. So we can simply write start. If the activity type is start then whatever the timestamp value that will be your start time. So we can simply call this as start time. We can do the same thing for the end time. So we can copy this case statement and here this could be your end time. And here in the activity type that could be end. So if the activity type is end then whatever the time available in the timestamp that will go in the end time. So here we can also verify the same. So here we can see the activity type which is start. So timestamp whatever the timestamp is available that is coming as start time. Here the activity type is end. So here it is coming under the end time. Next what we can do we can simply take the max on the basis of the machine ID and the process ID. So by using these two columns we can simply take the max of the start time and end time. So it will be coming in a single row. So when the machine was started when the machine was end will be coming a single row. So here we can simply write the machine ID in the selected statement along with the process ID. And in the group by we can also do the group by on these two columns only. So let me try to execute for checking the output. So it is saying like you have specified these two columns in the group by but you have not used the aggregate function max yet on these two columns. So let me try to use the max function in these two columns that we have created for the start time and end time. Now let me try to execute and we will see the output. So here we can see we are having the same output in a single row for the start time and end time. Now let me put this into one of the CTE. So for that we can simply use with CTE name as and then whatever the query is there. Now here we can do your selected statement this CTE and here we can also see we can simply take the difference in start time with the end time. So whatever the difference is there first column in the output we want a machine ID. The second column we want to take the average. So how we can get we can simply do the end time minus start time. So what were the difference that we are getting? We can simply take the average of the difference that we are getting. So this will be your average processing time. And here we can simply use the group by on this machine ID. So we can simply use the machine ID here. Let me try to execute this query. So it should be going to get the same output that we are expecting. So I hope guys you have understood how we can achieve this output. So thank you so much for watching this video. Please like this video, subscribe our channel. See you in the next video with another very interesting interview question.