- ************************************************************
- *******************
- *********************************************************
- ********************************************************************************************************************************************
Table 1: employee
Employee information, department they are in and their annual salary.
col_name | col_type ---------------+------------------- id | bigint full_name | varchar (100) department_id | int salary | float
Table 2: project_detail
Project meta data
col_name | col_type -----------------+--------------------- project_id | bigint title | varchar(200) start_date | date end_date | date budget | float
Table 3: projects
Projects history and employees who are associated with.
col_name | col_type -----------------+--------------------- project_id | bigint employee_id | bigint
Sample results
project_id | roi
------------+---------------------
20001 | 0.15113281250000002
20002 | 0.49863387978142076
20003 | 2.0277777777777777
Solution postgres
SELECT
P.project_id,
MAX(D.budget) *1.0/ SUM((end_date - start_date) * 1.0/365 * E.salary) AS roi
FROM projects P
INNER JOIN project_detail D
ON D.project_id = P.project_id
INNER JOIN employee E
ON E.id = P.employee_id
GROUP BY 1
ORDER BY 1;
Explanation
This query is selecting data from three tables: "projects", "project_detail", and "employee". It is joining these tables together based on their shared columns: "project_id" and "employee_id".
The query is then grouping the data by "project_id", so that it can perform calculations on each project separately.
The two calculations being performed are:
- Finding the maximum budget for each project, and
- Calculating the return on investment (ROI) for each project.
The ROI calculation is taking the maximum project budget and dividing it by the sum of each employee's salary for the duration of the project (calculated by subtracting the start and end dates and dividing by 365).
Finally, the query is ordering the results by project_id.
As a junior data analyst, it is important to understand how to read and write SQL queries, as they are a fundamental skill in data analysis. It is also important to understand how to join tables together and perform calculations on data.
Expected results
More Dropbox questions
| ID | Title | Level | FTPR |
|---|---|---|---|
| 151 | Salary report dropbox | easy |
|
| 152 | Salary analysis dropbox | easy |
|