leetcode (1)

Q. rising temperature

어제보다 기온이 높은 날 출력

# Write your MySQL query statement below
select weather.id Id
from Weather join Weather w on datediff(weather.recordDate, w.recordDate) = 1 and weather.temperature>w.temperature

 

 

Q. Average Time of Process per Machine

start, end가 라벨링 된 테이블에서 두 값의 차이를 구했어야 하는 문제, self join으로 해결

# Write your MySQL query statement below
select s.machine_id, round(avg(e.timestamp - s.timestamp),3) processing_time
from Activity s join Activity e on s.machine_id=e.machine_id and s.process_id=e.process_id and s.activity_type='start' and e.activity_type='end'
group by s.machine_id

 

 

Q. students and examinations

시험을 안 본 학생이 존재하는 테이블끼리 조인하여 모든 학생의 과목별 시험 횟수를 조회해야 하는 문제

join과 group by 과정에서 sub.subject_name과 count(e.subject_name)으로 0 값을 뽑아낼 수 있다.

# Write your MySQL query statement below
select st.student_id, student_name, sub.subject_name, count(e.subject_name) attended_exams
from Students st join Subjects sub left join Examinations e on st.student_id=e.student_id and sub.subject_name=e.subject_name
group by st.student_id, sub.subject_name
order by student_id, subject_name

 

 

Q. Managers with at Least 5 Direct Reports

아래 테이블은 John이 매니저이고 102~106번 직원을 담당하는 상황을 나타낸다.

 

Employee table:
+-----+-------+------------+-----------+
| id  | name  | department | managerId |
+-----+-------+------------+-----------+
| 101 | John  | A          | null      |
| 102 | Dan   | A          | 101       |
| 103 | James | A          | 101       |
| 104 | Amy   | A          | 101       |
| 105 | Anne  | A          | 101       |
| 106 | Ron   | B          | 101       |
+-----+-------+------------+-----------+

# Write your MySQL query statement below
select name
from Employee
where id in (select managerId from Employee
            group by managerId having count(*) >= 5)