The Ultimate Correlated SQL Query

Problem Statement:  There is a table named SkillSet which has employees’ skill details. We need to find out employees (skill wise) who have the highest proficiency rating. If multiple employees have the highest proficiency rating in some skill then the employee having more experience should be selected.

Employee	Skills	Proficiency	Experience
Thor	SQL	9	1
Thor	VBA	8	1
Mike	VBA	9	1
Mike	SQL	6	1
Robert	VBA	9	2
Robert	C#	6	2
Robert	SQL	9	1
Logan	C#	9	1
Logan	SQL	9	2

Solution:Lets do it in steps. Try to find out skill wise employees having maximum proficiency.

SELECT Employee, Skills, Proficiency
   FROM
    Skillset  as T1
WHERE Proficiency = (SELECT MAX(Proficiency) as Prof
                                             FROM
                                                  [Skillset] as T2
                                               WHERE T1.Skills = T2.Skills
                                            Group By Skills)

Above written query will return the following output:

Employee	Skills	Proficiency
Thor	SQL	9
Mike	VBA	9
Robert	VBA	9
Robert	SQL	9
Logan	C#	9
Logan	SQL	9

Here, you can see that for VBA and SQl there are multiple employees with the same (highest) proficiency. Now, to identify who of the highest proficiency employees has higher years of experience,
we need to add another level to the query like below:

SELECT Employee, Skills, Proficiency
   FROM
    Skillset  as T1 
WHERE  Experience = (
                      SELECT MAX(Experience) 
                          FROM SkillSet as T3 
                      Where Proficiency = (SELECT MAX(Proficiency) as Prof 
                                           FROM 
                                            [Skillset] as T2  
                                            WHERE T3.Skills = T2.Skills
                                            Group By Skills) AND T1.Skills = T3.Skills
                                     )

This query will return the output as below:

Employee	Skills	Proficiency
Robert	VBA	9
Logan	C#	9
Logan	SQL	9

For another example of Correlated query, check this out: