577. Employee Bonus

Question:

Solution:

``````select
e.name,
b.bonus
from employee e
left join (
select *
from bonus
) b
on e.empid = b.empid
where bonus < 1000
or bonus is null``````

578. Get Highest Answer Rate Question

Question:

Solution:

``````select
question_id as survey_log
from (
select
question_id,
sum(case when action="show" then 1 else 0 end) as num_show
from survey_log
group by 1
) s
order by (num_answer / num_show) desc
limit 1``````

579. Find Cumulative Salary of an Emplyee

Question:

Solution:

``````select
e.id,
e.month,
e.cum_sum as salary
from(
select
id,
month,
salary,
/* calculate cumulative salary */
sum(salary) over(partition by id order by month) as cum_sum
from employee
order by 1,2
) e
join (
select
id,
max(month) as max_month
from employee
group by 1
) m
on e.id = m.id
and e.month < m.max_month /* excluding the recent month */
order by 1, 2 desc``````

580. Count Student Number in Departments

Question:

Solution:

``````select
d.dept_name,
count(distinct student_id) as student_number
from department d
left join student s
on d.dept_id = s.dept_id
group by 1
order by 2 desc, 1``````