 Hello, welcome to SSUnitex, so see this side and this is continuation of SQL quiz 50 day challenges. So we are in day 5 and today we are going to see one more interesting SQL interview question and answer. So here we are having one of the input data and this table is having total 4 columns like customer ID, customer name, sale, date and amount. And in output we just want to have only 3 columns. The first column will be sale date and then the total amount and after that the average amount. So how we can get the sale date and total amount and average amount. So we are required to write a query to get the month week wise total salary and average. So let's assume for every month the week will be started from 1. So if any transaction has been happened between 1 to 7 it should be going to calculate on that week and we should be going to pick the max of that week. So as we can see the max is 7 between 1 to 7. So here in the output in the sale date we should be seeing 7. Similarly here we can see we have 9 and 10 only these 2 transactions happening in second week. So in the output we have 10. So how we can write the logic for getting this output. So here we have multiple ways by which we can achieve this output. But I am going to write a very simple logic. First we are required to write a case statement and on that case statement we are required to check the sale date. If the sale date value is less than or equals to 7 then that will be week 1. If your sale date value is less than or equals to 14 then it will be your second week. Similarly if your sale date value is less or equals to 21st it should be 3rd week. If 28 then that will be 4th week otherwise that will be 5th week. So this logic we can write for calculating one more additional column here for the week number. And once we will be getting the week number then we can simply take the max of this sale date and then we can take the sum of this amount and the third column will be your average amount. So let me quickly go inside the Secosoab management studio and we will try to write a query for getting this output. So here I have already written the script for creating the table. So let me try to execute and table has been created successfully. Let me try to insert the data on this table. So as we can see data has been inserted total 11 rows are available here. So the first thing that I told you we are required to one case statement here. So on that case statement first we are required to check this sale date. So first we should be going to extract the day part from this sale date. If this value is less or equals to 7 then we can simply pass that as 1 hard code value. In the second statement here we can simply write when your day value of this sale date is less or equals to 14 then this value should be 2. Similarly the third condition we can write for getting the sale date day part here if this value is 21 then that should be third week and then we can write again for getting this sale date value if this value is less or equals to 28 then that should be your fourth week. If all these conditions will not be satisfied at last we should be having 5. Here we can write and and we can pass this as week now. Now let me try to execute this for checking the additional columns that we have added. So here we can see if your date is less or equals to 7 then we are getting the same week number here if we can see the second week is started from 8 so between 8 to 10 it is coming as second week. So this is what you can write for getting this output. So once we are having this output let me put this into one of the CT and here and try to select. So what we want to select here we are required to select the sale date the first column as sale date. So we can simply get the sale date from this CT and this sale date we can take the max. So this max we are simply taking because here we are doing this group by on this week now. So here it is creating the partitions like it is having the first partition week now second is having the second partition. So if we are taking the max of this partition it should be going to get the 7. So in our output we are required to have the 7 so that's why we are doing the partition by or the group by on this week num column that we have originally created and then we are taking the max of this sale date. Here similarly we can also do the sum of this amount. So this could be your total amount and the third column we can simply take the average of this amount column and let me call this as average amount. Now let me try to execute this query and we will try to check the output of this. So here we are getting the slim output as we were expecting. So the catch part is first we are required to create this additional column that is the week num and then after this week num should be going to use in the group by and creating the different partitions on the basis of this week num and then we are taking the max of this sale date sum of this amount and average of this amount. So I hope guys you have understood how we can write this query for getting this output I will provide this script on the description of this video so you can copy from there for your practice purpose. And you can also comment your answers in the comment box. So thank you so much see you in the next video.