- ********************************************************************************************************************************************************************
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
;
Explanation
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 |
|