https://www.hackerrank.com/challenges/the-company/problem

📔문제

Amber's conglomerate corporation just acquired some new companies. Each of the companies follows this

Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.

The tables may contain duplicate records.
The company_code is string, so the sorting should not be numeric. For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.

The following tables contain company data:

Company: The company_code is the code of the company and founder is the founder of the company.

Lead_Manager: The lead_manager_code is the code of the lead manager, and the company_code is the code of the working company.

Senior_Manager: The senior_manager_code is the code of the senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company.

Manager: The manager_code is the code of the manager, the senior_manager_code is the code of its senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company.

Employee: The employee_code is the code of the employee, the manager_code is the code of its manager, the senior_manager_code is the code of its senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company

---

📝예시

Company Table:

Lead_Manager Table:

Senior_Manager Table:

Manager Table:

Employee Table:

C1 Monika 1 2 1 2
C2 Samantha 1 1 2 2

In company C1, the only lead manager is LM1. There are two senior managers, SM1 and SM2, under LM1. There is one manager, M1, under senior manager SM1. There are two employees, E1 and E2, under manager M1.

In company C2, the only lead manager is LM2. There is one senior manager, SM3, under LM2. There are two managers, M2 and M3, under senior manager SM3. There is one employee, E3, under manager M2, and another employee, E4, under manager, M3.

---

🧮분야

• SELECT

---

📃SQL 코드

select c.company_code, 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 left outer join lead_manager l on l.company_code=c.company_code
    left outer join senior_manager s on c.company_code=s.company_code
    left outer join manager m on s.company_code=m.company_code
    left outer join employee e on m.company_code=e.company_code
group by c.company_code, founder
order by c.company_code;

---

📰출력 결과

---

📂고찰

select c.company_code, founder ,l.lead ,s.senior, m.man, e.empl
from company c,
    (select company_code,count(*) as lead
    from lead_manager
    group by company_code) l,
    (select company_code,count(*) as senior
    from senior_manager
    group by company_code) s,
    (select company_code,count(*) as man
    from manager
    group by company_code) m,
    (select company_code,count(company_code) as empl
    from employee 
    group by company_code) e
where l.company_code=c.company_code
and l.company_code=s.company_code
and s.company_code=m.company_code
and m.company_code=e.company_code
order by c.company_code;

처음에 작성했던 sql쿼리이다. 지금 결과랑 비교해보면 아웃풋이 다르게 나온 걸 확실히 알 수 있다.

해결하기 힘들어서, 인터넷에 찾아보았는데
left outer join을 통해서 c로 조인을 한 다음 각각의 수를 distinct해서 계산해주면 되는 것이었다.