 మలి సాంద్చిక్ని ఇని మాచెచికాక్చి ఆదిదికేట్డి ప౔రమరకికి. మారి దికికేది కింద్చిమునింది తీట్టిటిసి మాపిథి. So, today we are going to discuss one more interview question which was asked in autumn. So, in the last video we have discussed three questions which was asked in autumn. So, here consider the following table which represents city-wise sale for the organization in a month. So, as we can see we have input table that is having three columns. First it is then sale amount then the month. So, Bangalore, Mumbai and Hyderabad are considered as Taiwan cities. So, here we just want to write the query to display city-wise sale percentage. All the cities except Taiwan need to added as others and their sale are calculated together. And output should be sorted as decreasing order and only two display places. So, here as we can see in the output we are having cities and sale percentage. But we are getting Bangalore, Mumbai and Hyderabad and others. So, we are getting Bangalore, Mumbai and Hyderabad because they are coming inside Taiwan cities. And other cities except these three will goes into others and sale percentage will be calculated together. So, how we can write the query for getting this output? So, go to our SQL Server Management Studio where we will write the query for getting this output. So, as I am using SQL 2.0.17 you may have another version of SQL Server. But this query will work for all the versions. So, first of all I would like to execute this script to create the table. So, table created successfully. Next I just want to insert a record on this table. So, we can execute this insert statement. So, 7 rows effected. It means data have been loaded successfully into the table. Next I just want to check the data. So, we can write select a stick from our table that is optimum part 2. Now, let me execute this selected statement to check the data. So, here we are having all the data which is available in the slide. So, how we can get the output as we were seen in the slide. So, first of all I would like to declare a variable for total sale. So, we can declare as total sale and it should be on integer. Next I just want to put the data into this variable. So, we just want to put all the sales and this is from optimum part 2 table which is the current table. So, here we are having sales. So, total sale which is available in the table will be in slides in this total sale variable. Next I just want to check for cities. So, for that we can use the case. So, case when cities is not in terrible cities that is Bangalore. Next is Mumbai and last is Hyderabad. Then that cities will move to other. So, we can write as other else cities and then sales. Now, we can execute this query and we can check. So, here as we can see we are getting Bangalore, Mumbai, Hyderabad and others. So, I just want to combine the sale for all these. So, we can use the sum of sale. Then as we can put as sales. Then we have to use group by and in group by we have to use this case statement. So, we can complete this case statement and paste in group by. Now, let me execute this query and we just want to check the output. In case index because this city and end is missing in the group by. So, we can complete this as well and paste it here. Now, let me execute this query and we are getting the total sale according to their cities. So, how we can get the sale percentage? So, for that we can use multiplied by 100.00. Then we just want to divide by this total sale variable. So, now we can use this total sale variable. Now, I just want to execute this query and we just want to check the output. So, here we are getting the sale percentage but we are getting these sale percentage up to many decimal places. Our requirement is we just want these sale percentage up to only 2 decimal places. So, for that we can use cast as then numeric and here we can use 14 comma 2. So, it will be going to get the output up to 2 decimal places. But here we are not getting this output in decreasing order as we can say 29.15 then 20.41 then 27.11. So, our requirement is we just want to get this sale percentage in decreasing order. So, we can use this in order by clause. So, we can copy this and go to order by then decreasing. Now, let me execute this query and we just want to check the output. So, here we can see we are getting Bangalore, Mumbai, other and Hyderabad. 29.15, 27.11, 23.32 and 20.41. Now, go to slide and here we can see we are getting the same metadata. So, I hope you have understand how we can get the sale percentage. And in next video we will discuss few more questions which was asked in Optum. So, thank you so much for watching this video. If you like this video, please subscribe our channel to get many more videos. If you need this script, it will be available on the description of this video. Thank you so much.