Day: September 6, 2019

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

select 
question_id as survey_log
from (
	select 
    question_id,
    sum(case when action="answer" then 1 else 0 end) as num_answer,
    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

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

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