153. Project ROI

easy dropbox

  • ************************************************************
  • *******************
  • *********************************************************
  • ********************************************************************************************************************************************

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

    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


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:

  1. Finding the maximum budget for each project, and
  2. 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