Today’s HackerRank challenge although seemed easy was quite tricky for me. There were 5 tables: Company, Lead_Manager, Senior_Manager, Manager and Employee. Using these 5 tables or not, I had to find the name of the founder; total number of lead managers, senior managers, managers and employees working at the company.

Sample of the table ‘Company’:

company_code	founder
C1	Monika
C2	Samantha

Sample of the table ‘Lead_Manager’:

lead_manager_code	company_code
LM1	C1
LM2	c2

Sample of the table ‘Senior_Manager’:

senior_manager_code	lead_manager_code	company_code
SM1	LM1	C1
SM2	LM1	C1
SM3	LM2	C2

Sample of the table ‘Manager’:

manager_code	senior_manager_code	lead_manager_code	company_code
M1	SM1	LM1	C1
M2	SM2	LM2	C2
M3	SM3	LM2	C2

Sample of the table ‘Employee’:

employee_code	manager_code	senior_manager_code	lead_manager_code	company_code
E1	M1	SM1	LM1	C1
E2	M1	SM1	LM1	C1
E3	M2	SM3	LM2	C2
E4	M3	SM3	LM2	C2

Sample of the Output

company_code	founder	lead_manager	senior_manager	manager	employee
C1	Monika	1	2	1	2
C2	Samantha	1	1	2	1

I had two solution for the above challenge. One using INNER JOIN and the other using LEFT JOIN.

INNER JOIN Solution:

SELECT COMPANY_CODE, FOUNDER, COUNT(DISTINCT LEAD_MANAGER_CODE), COUNT(DISTINCT SENIOR_MANAGER_CODE), COUNT(DISTINCT MANAGER_CODE), COUNT(DISTINCT EMPLOYEE_CODE)
  FROM(
      SELECT DISTINCT C.COMPANY_CODE, C.FOUNDER, L.LEAD_MANAGER_CODE,
      S.SENIOR_MANAGER_CODE,   M.MANAGER_CODE, E.EMPLOYEE_CODE
      FROM COMPANY C 
      INNER JOIN LEAD_MANAGER L ON 
      C.COMPANY_CODE = L.COMPANY_CODE
      INNER JOIN SENIOR_MANAGER S ON 
      L.LEAD_MANAGER_CODE = S.LEAD_MANAGER_CODE
      INNER JOIN MANAGER M ON 
      S.SENIOR_MANAGER_CODE = M.SENIOR_MANAGER_CODE
      INNER JOIN EMPLOYEE E ON 
      M.MANAGER_CODE = E.MANAGER_CODE)
  GROUP BY COMPANY_CODE, FOUNDER
  ORDER BY COMPANY_CODE;

LEFT JOIN Solution:

SELECT COMPANY_CODE, FOUNDER, COUNT(DISTINCT LEAD_MANAGER_CODE), COUNT(DISTINCT SENIOR_MANAGER_CODE), COUNT(DISTINCT MANAGER_CODE), COUNT(DISTINCT EMPLOYEE_CODE)
 FROM(
     SELECT DISTINCT C.COMPANY_CODE, C.FOUNDER, L.LEAD_MANAGER_CODE,
     S.SENIOR_MANAGER_CODE, M.MANAGER_CODE, E.EMPLOYEE_CODE
     FROM COMPANY C 
     LEFT JOIN LEAD_MANAGER L ON 
     C.COMPANY_CODE = L.COMPANY_CODE
     LEFT JOIN SENIOR_MANAGER S ON 
     L.LEAD_MANAGER_CODE = S.LEAD_MANAGER_CODE
     LEFT JOIN MANAGER M ON 
     S.SENIOR_MANAGER_CODE = M.SENIOR_MANAGER_CODE
     LEFT JOIN EMPLOYEE E ON 
     M.MANAGER_CODE = E.MANAGER_CODE)
 GROUP BY COMPANY_CODE, FOUNDER
 ORDER BY COMPANY_CODE ASC;

Top 6 rows of the solution:

C1	Angela	1	2	5	13
C10	Earl	1	1	2	3
C100	Aaron	1	2	4	10
C11	Robert	1	1	1	1
C12	Amy	1	2	6	14
C13	Pamela	1	2	5	14

Out of the two solution, I would stick to the LEFT JOIN solution because there could be founders that are by themselves or there could be lead manager/senior managers/managers without any subordinates. Using INNER JOIN would not include these in the count.

My first trial was a LEFT JOIN joining on company_code.

SELECT COMPANY_CODE, COUNT(FOUNDER), COUNT(LEAD_MANAGER_CODE), COUNT(SENIOR_MANAGER_CODE), COUNT(MANAGER_CODE), COUNT(EMPLOYEE_CODE)
 FROM(
     SELECT DISTINCT C.COMPANY_CODE, C.FOUNDER, L.LEAD_MANAGER_CODE, 
     S.SENIOR_MANAGER_CODE, M.MANAGER_CODE, E.EMPLOYEE_CODE
     FROM COMPANY C 
     LEFT JOIN LEAD_MANAGER L ON 
     C.COMPANY_CODE = L.COMPANY_CODE
     LEFT JOIN SENIOR_MANAGER S 
     ON C.COMPANY_CODE = S.COMPANY_CODE
     LEFT JOIN MANAGER M ON 
     C.COMPANY_CODE = M.COMPANY_CODE
     LEFT JOIN EMPLOYEE E ON 
     C.COMPANY_CODE = E.COMPANY_CODE) 
 AS K GROUP BY COMPANY_CODE;

However, I realised that the join on company_code would result in more rows than with join on other fields. Therefore, I forego trying to debug this code.

There were other submissions that I had a look at. Two of them are below:

Other Solution 1:

SELECT DISTINCT C.COMPANY_CODE,C.FOUNDER,COUNT(DISTINCT LEAD_MANAGER_CODE),COUNT(DISTINCT SENIOR_MANAGER_CODE),COUNT(DISTINCT MANAGER_CODE),COUNT(DISTINCT EMPLOYEE_CODE) 
FROM COMPANY C 
INNER JOIN EMPLOYEE E 
ON C.COMPANY_CODE = E.COMPANY_CODE 
GROUP BY C.COMPANY_CODE,C.FOUNDER 
ORDER BY C.COMPANY_CODE ASC; 

This solution gave the right answer. However, I am not comfortable with this solution since this solution has a presumption that all the lead managers, senior managers, lead managers and managers alike have subordinates. Well, that may have been the case for the challenge but it does not simulate the real world scenario where all managers may not have anyone directly reporting to them.

Other Solution 2:

SELECT C.COMPANY_CODE, C.FOUNDER, COUNT(DISTINCT L.LEAD_MANAGER_CODE), COUNT(DISTINCT S.SENIOR_MANAGER_CODE), COUNT(DISTINCT M.MANAGER_CODE), COUNT(DISTINCT E.EMPLOYEE_CODE)
FROM COMPANY C,LEAD_MANAGER L,SENIOR_MANAGER S, MANAGER M, EMPLOYEE E
WHERE C.COMPANY_CODE = L.COMPANY_CODE
AND L.LEAD_MANAGER_CODE = S.LEAD_MANAGER_CODE
AND S.SENIOR_MANAGER_CODE = M.SENIOR_MANAGER_CODE
AND M.MANAGER_CODE = E.MANAGER_CODE
GROUP BY C.COMPANY_CODE, C.FOUNDER
ORDER BY C.COMPANY_CODE ASC;

This challenge could also be solved by using an advanced select as the above. Again this solution would be similar to my solution of ‘INNER JOIN’ and that would leave out lead managers/senior managers/managers without any sub-ordinates.

Connect with me via my social media

• Facebook
• Instagram
• LinkedIn
• GitHub