Project Budget Error
Start Timer
0:00:00
We’re given two tables. One is named projects and the other maps employees to the projects they’re working on.
We want to select the five most expensive projects by budget to employee count ratio. But let’s say that we’ve found a bug where there exist duplicate rows in the employee_projects table.
Write a query to account for the error and select the top five most expensive projects by budget to employee count ratio.
Schema
Input:
projects table
| column | type |
|---|---|
id |
INTEGER |
title |
VARCHAR |
state_date |
DATETIME |
end_date |
DATETIME |
budget |
INTEGER |
employee_projects table
| Column | Type |
|---|---|
project_id |
INTEGER |
employee_id |
INTEGER |
Output:
| Column | Type |
|---|---|
title |
VARCHAR |
budget_per_employee |
FLOAT |
Example
Input:
projects table
| id | title | start_date | end_date | budget |
|---|---|---|---|---|
| 1 | party | 2006-04-17 00:00:00 | 2006-05-19 00:00:00 | 25875 |
| 2 | diversity | 2017-08-09 00:00:00 | 2017-09-18 00:00:00 | 77867 |
| 3 | integration | 2005-06-29 00:00:00 | 2005-07-19 00:00:00 | 75987 |
| 4 | testing | 2009-01-12 00:00:00 | 2009-05-03 00:00:00 | 35946 |
| 5 | launch | 2005-05-02 00:00:00 | 2005-10-16 00:00:00 | 66292 |
| 6 | meet | 2001-09-09 00:00:00 | 2002-04-04 00:00:00 | 71243 |
| 7 | payroll | 2019-09-25 00:00:00 | 2020-05-05 00:00:00 | 97071 |
| 8 | admin | 2000-07-08 00:00:00 | 2000-09-10 00:00:00 | 24000 |
| 9 | petronas | 2016-07-01 00:00:00 | 2016-11-28 00:00:00 | 37088 |
| 10 | bel | 2005-08-03 00:00:00 | 2006-04-29 00:00:00 | 61937 |
employee_projects table
| project_id | employee_id |
|---|---|
| 1 | 7 |
| 1 | 9 |
| 2 | 6 |
| 3 | 1 |
| 3 | 2 |
| 4 | 5 |
| 5 | 3 |
| 5 | 4 |
| 6 | 8 |
| 8 | 10 |
| 9 | 11 |
| 10 | 12 |
| 5 | 5 |
| 9 | 11 |
| 6 | 8 |
| 4 | 5 |
Output:
| title | budget_per_employee |
|---|---|
| diversity | 77867 |
| meet | 71243 |
| bel | 61937 |
| integration | 37993.5 |
| petronas | 37088 |
.
.
.
.
.
.
.
.
.
Comments