This is a practice solution for StrataScratch question ID 10304.
This query identifies projects where the prorated employee salary costs exceed the allocated budget, factoring in each project’s actual duration. Catching these overruns early gives leadership a chance to reallocate resources, renegotiate scope, or adjust future budget planning before costs spiral further.
SELECT linkedin_projects.title,
linkedin_projects.budget,
ceiling((linkedin_projects.end_date - linkedin_projects.start_date) * sum(linkedin_employees.salary) / 365) AS prorated_employee_expense
FROM linkedin_emp_projects
INNER JOIN linkedin_projects ON linkedin_emp_projects.project_id = linkedin_projects.id
INNER JOIN linkedin_employees ON linkedin_emp_projects.emp_id = linkedin_employees.id
GROUP BY linkedin_projects.title,
linkedin_projects.budget,
linkedin_projects.start_date,
linkedin_projects.end_date
HAVING ceiling((linkedin_projects.end_date - linkedin_projects.start_date) * sum(linkedin_employees.salary) / 365) > linkedin_projects.budget
ORDER BY linkedin_projects.title;