 Hello. Welcome to SSU Nitek Social Decide and this is continuation of SQL query 50 day challenges. So we are in day 3. So today we are going to see one more SQL real time interview question. And here the complexity level is easy. So this is the easy question. Going forward we will be going to introduce the medium and high complexity level questions. So let's start with today's video. So let's assume we have one of the input table that we will be having two columns. First is the employee ID and second is the team ID. In the output of this we just want to have two columns. The first column will be employee ID and the second column will be your team size. So how we can calculate the team size? So for calculating the team size if we can see here. So the team ID that we are having is 8. So how many 8s are available here? So 8 is available 3 times. So like 3 employees are working in the same team. So that's why we can see the team size is 3 for these 3 employees. Similarly for ID 5 and ID 6 we are having 2 employees in the department that is 9. So these 2 employees are working in same department. So here the team size we can see 2 in both the places. So we are required to write a query for getting this output. So let's jump to SQL Server Management Studio where we will try to write the query for getting this output. So here I have written this script for creating the table along with the inserting some of the data on this table. So let me try to execute this query for creating the table and inserting the data in this table. Now let me write to write the selected statement for checking the data that is available on this table now. So this is the same data that we have seen in the file. Now here first we are required to take the count of the team ID from this table on the basis of team ID. So the first column we are required to have as a team ID and the second column that we want to have the count whatever the count that we will be getting from here we can simply go and try to write group by with the team ID. So here we can execute this query. So now we can see we are having the team ID and how many employees are working on that team. So here we can see like ID 8 we are having 3 employees ID 9 we are having 2 employees ID 7 we have 1 employee. Now here either we can put this into one of the city and then after we can join that city with the actual table or we can also use this as a subquery and here let me try to write the select from your actual table. So your table is tbl underscore team size let me put an alias with a and here we can simply go and do the inner join with this query. So here in the on condition a dot your team ID should be equals to b dot your team ID and in the select first we are required to get the employee ID from table a and the second table that is your query we just want to have the count. So we can go and use the count here. Let me try to execute this query for getting the output. So here we can see this is not in the proper sorted order. So we can also do the order by on the first column that is your employee ID. Let me execute and we will see the output. So we can see like first 3 employees are working in same department so the count we can see 3 similarly for 4 we have only 1 employee and employee 5 and 6 is working in the same department so we can see count as 2. Thank you so much for watching this video please like this video subscribe our channel and see you in the next video.