[SQL문제풀기] Risky Projects

문제

StrataScratch - Risky Projects

StrataScratch

https://platform.stratascratch.com/coding/10304-risky-projects?code_type=3

내가 작성한 정답

MySQL, PostgreSQL

select b.title, max(b.budget) budget,ceiling(sum(e.salary*b.p)) prorated_employee_expense
from linkedin_emp_projects p
join linkedin_employees e 
    on p.emp_id = e.id
join (select id, title, datediff(end_date,start_date)*1.00/365 p, budget 
      from linkedin_projects) b
    on b.id = p.project_id
group by b.title
having max(b.budget) < ceiling(sum(e.salary*b.p))
order by 1;

PostgreSQL, Oracle

select b.title, max(b.budget) budget,ceil(sum(e.salary*b.p)) prorated_employee_expense
from linkedin_emp_projects p
join linkedin_employees e 
    on p.emp_id = e.id
join (select id, title, (end_date-start_date)*1.00/365 p, budget 
      from linkedin_projects) b
    on b.id = p.project_id
group by b.title
having max(b.budget) < ceil(sum(e.salary*b.p))
order by 1;