[leetcode] 570 ~ 586

570. Managers with at Least 5 Direct Reports

select a.name
from Employee a join Employee b
on a.id = b.managerId
group by a.id
having count(*)>=5

 

571.

 

 

574. Winning Candidate

select A.name
from(
    select name, count(*) as cnt
    from Vote join Candidate
    on Vote.candidateId = Candidate.id
    group by Vote.candidateId) A
order by A.cnt desc
limit 1

 

577. Employee Bonus

select name, bonus
from Employee left join Bonus
on Employee.empId = Bonus.empId
where bonus < 1000 or bonus is null

left outer join과 left join은 같다.

where절 전까지 했을 때:

 

578. Get Highest Answer Rate Question

# 내 코드
select t.question_id as survey_log
from (
    select A.question_id, (A.a_cnt/B.s_cnt) as answer_rate
    from (
        select action, question_id, count(*) as a_cnt
        from SurveyLog
        where action='answer'
        group by question_id) as A join
        (select action, question_id, count(*) s_cnt
        from SurveyLog
        where action='show'
        group by question_id) as B
        on A.question_id = B.question_id
    order by answer_rate desc, A.question_id asc) as t
limit 1

# best selected
SELECT question_id AS survey_log
FROM SurveyLog
GROUP BY question_id
ORDER BY SUM(IF(action='answer',1,0))/SUM(IF(action='show',1,0)) DESC
LIMIT 1;

# best efficient
select question_id as survey_log 
from SurveyLog 
group by question_id 
order by sum(case when action="answer" then 1 else 0 end) desc limit 1

p.s)

select id, action, question_id, count(*) as cnt
from SurveyLog
group by question_id
having action='show'
#이렇게 그룹별 전체 나온 것(having 적용X) 중에 having이 적용되니깐 그룹별 having 적용 이후의 그룹별 개수가 나오지 않는다.

#이렇게 해야된다.
select action, question_id, count(*)
from SurveyLog
where action='show'
group by question_id

 

580. Count Student Number in Departments

select dept_name, count(student_id) as student_number
from Student right join Department
on Student.dept_id = Department.dept_id
group by dept_name
order by student_number desc, dept_name

 

584. Find Customer Referee

select name
from Customer
where referee_id != 2 or referee_id is null

# solution
SELECT name FROM customer WHERE referee_id = NULL OR referee_id <> 2;

 

585. Investments in 2016

select round(sum(tiv_2016), 2) as tiv_2016
from Insurance
where (lat, lon) in (select lat, lon
                    from Insurance
                    group by lat, lon
                    having count(*) = 1) and pid not in (select pid
                                                        from Insurance
                                                        group by tiv_2015
                                                        having count(*)=1)

p.s) lat, lon부분을 concat으로도 할 수 있다.

CONCAT(LAT, LON) IN
    (
      SELECT
        CONCAT(LAT, LON)
      FROM
        insurance
      GROUP BY LAT , LON
      HAVING COUNT(*) = 1
    )

 

586. Customer Placing the Largest Number of Of Orders

select customer_number
from (select customer_number, count(*)
    from Orders
    group by customer_number
    order by count(*) desc) a
limit 1

#best
SELECT
    customer_number
FROM
    orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1
;