152. Salary analysis

easy dropbox

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

Table 1: department

Dimensional table for each department: sales, data science, hr, etc.

  col_name     | col_type
id             | int
name           | varchar (20)

Table 2: 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

Sample results

  id  |    full_name    | salary | count
 8016 | Tywin Lannister | 150000 |    27
 8009 | Theon Greyjoy   |  10000 |     3

Solution postgres

WITH pm_employees AS (
    SELECT E.id, E.full_name, E.salary, D.id AS department_id
    FROM employee E
    INNER JOIN department D
    ON D.id = E.department_id
    WHERE D.name = 'Product Management'
SELECT P.id, P.full_name, P.salary, COUNT(E.id)
FROM pm_employees P
INNER JOIN employee E
ON P.department_id <> E.department_id
AND P.salary > E.salary + 5000
GROUP BY 1,2, 3


This query retrieves information about employees who work in the Product Management department and have a salary that is greater than $5000 more than other employees in different departments.

The query first creates a temporary table called "pm_employees" that includes the id, full name, salary, and department id of employees who work in the Product Management department.

The main query then selects the id, full name, and salary of these employees from the temporary table, as well as the count of employees from different departments who have a salary that is $5000 less than the Product Management employee's salary.

The main query joins the pm_employees table with the employee table using the department id to ensure that only employees who work in different departments are included. It also includes a condition that the Product Management employee's salary must be greater than the other employee's salary plus $5000.

Finally, the query groups the results by the pm_employees' id, full name, and salary.

Expected results

More Dropbox questions

ID Title Level FTPR
151 Salary report dropbox easy
153 Project ROI dropbox easy