/*
Walmart SQL Interview Question

Write an SQL query to find the projects with the highest budget-per-employee ratio using the two related tables projects and employees.
*/

-- Schema Setup

CREATE TABLE Projects (
 ProjectID INT PRIMARY KEY,
 ProjectName VARCHAR(20),
 Budget DECIMAL(18, 2)
);

CREATE TABLE Employees (
 EmployeeID INT PRIMARY KEY,
 ProjectID INT,
 EmployeeName VARCHAR(10)
);

INSERT INTO Projects (ProjectID, ProjectName, Budget) VALUES
(1, 'Project Alpha', 100000.00),
(2, 'Project Beta', 50000.00),
(3, 'Project Gamma', 150000.00);

INSERT INTO Employees (EmployeeID, ProjectID, EmployeeName) VALUES
(1, 1, 'Alice'),
(2, 1, 'Bob'),
(3, 2, 'Charlie'),
(4, 2, 'David'),
(5, 3, 'Eve'),
(6, 3, 'Frank'),
(7, 3, 'Grace');

-- Solution

SELECT
    ProjectID,
    Budget,
    no_of_employees,
    cost_per_employee
FROM
(
 SELECT
    p.ProjectID,
    p.ProjectName,
    p.Budget,
    COUNT(e.EmployeeID) AS no_of_employees,
    p.Budget / COUNT(e.EmployeeID) AS cost_per_employee,
    DENSE_RANK() OVER(ORDER BY p.Budget / COUNT(e.EmployeeID) DESC) AS rnk
 FROM
    Employees e
 INNER JOIN
    Projects p
    ON e.ProjectID = p.ProjectID
 GROUP BY
    p.ProjectID,
    p.ProjectName,
    p.Budget
) subquery
WHERE rnk = 1;