I'm struggling with this question...have tried many different approaches but to no avail-CodePudding

I'm prepping for a job interview and got stumped by this question...

-- Take the 5 lowest paid employees who have done at least 10 projects
-- Given Tables:
-- Table 1: Employees
-- ID | Salary

-- Table 2: Projects
-- Employee_id | project_id | Start_dt | End_dt

I was trying to use a sub-query to first select the folks with Projects greater than 10. However, not sure how to incorporate salary into the main query.

CodePudding user response：

I'll try to give you a pseudo SQL to solve this. If you can specify the database I can try to provide you with exact sql.

Left outer join Projects table with Employee both tables and
Create a index/rank/row number order by descending on Salary 
put above query in a subquery with calculated column e.g. rnk
In outer query do a group by on Employee_id and Project_Id
Filter the group by rnk<= 5 and Max(End_dt) is not null and Count(*) >= 10

CodePudding user response：

SELECT e.ID
FROM Employees e
JOIN Products p 
ON e.ID = p.Employee_id
WHERE end_date IS NOT NULL
GROUP BY e.id
HAVING COUNT(p.project_id) > 9
ORDER BY e.Salary
LIMIT 5;