Expensive Projects

Given a list of projects and employees mapped to each project, calculate by the amount of project budget allocated to each employee . The output should include the project title and the project budget rounded to the closest integer. Order your list by projects with the highest budget per employee first.

table: ms_projects

table: ms_emp_projects

Solution:

select p.title,
ceiling(1.00*p.budget/count(e.emp_id)) as perEmpBug
from ms_projects as p
join ms_emp_projects as e on p.id = e.project_id
group by p.id,p.title,p.budget
order by perEmpBug desc

Output:

SQL Script:

USE [StrataScratch]
CREATE TABLE [dbo].[ms_emp_projects](
	[emp_id] [int] NOT NULL,
	[project_id] [int] NULL,
 CONSTRAINT [PK_ms_emp_projects] PRIMARY KEY CLUSTERED 
(
	[emp_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ms_projects](
	[id] [int] NOT NULL,
	[title] [varchar](50) NULL,
	[budget] [int] NULL,
 CONSTRAINT [PK_ms_projects] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10592, 1)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10593, 2)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10594, 3)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10595, 4)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10596, 5)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10597, 6)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10598, 7)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10599, 8)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10600, 9)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10601, 10)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10602, 11)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10603, 12)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10604, 13)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10605, 14)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10606, 15)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10607, 16)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10608, 17)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10609, 18)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10610, 19)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10611, 20)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10612, 21)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10613, 22)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10614, 23)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10615, 24)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10616, 25)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10617, 26)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10618, 27)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10619, 28)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10620, 29)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10621, 30)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10622, 31)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10623, 32)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10624, 33)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10625, 34)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10626, 35)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10627, 36)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10628, 37)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10629, 38)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10630, 39)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10631, 40)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10632, 41)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10633, 42)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10634, 43)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10635, 44)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10636, 45)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10637, 46)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10638, 47)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10639, 48)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10640, 49)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10641, 50)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10642, 1)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10643, 2)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10644, 3)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10645, 4)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10646, 5)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10647, 6)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10648, 7)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10649, 8)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10650, 9)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10651, 10)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10652, 11)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10653, 12)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10654, 13)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10655, 14)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10656, 15)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10657, 16)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10658, 17)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10659, 18)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10660, 19)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10661, 20)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10662, 21)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10663, 22)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10664, 23)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10665, 24)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10666, 25)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10667, 26)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10668, 27)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10669, 28)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10670, 29)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10671, 30)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10672, 31)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10673, 32)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10674, 33)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10675, 34)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10676, 35)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10677, 36)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10678, 37)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10679, 38)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10680, 39)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10681, 40)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10682, 41)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10683, 42)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10684, 43)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10685, 44)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10686, 45)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10687, 46)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10688, 47)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10689, 48)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10690, 49)
GO
INSERT [dbo].[ms_emp_projects] ([emp_id], [project_id]) VALUES (10691, 50)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (1, N'Project1', 29498)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (2, N'Project2', 32487)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (3, N'Project3', 43909)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (4, N'Project4', 15776)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (5, N'Project5', 36268)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (6, N'Project6', 41611)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (7, N'Project7', 34003)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (8, N'Project8', 49284)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (9, N'Project9', 32341)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (10, N'Project10', 47587)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (11, N'Project11', 11705)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (12, N'Project12', 10468)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (13, N'Project13', 43238)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (14, N'Project14', 30014)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (15, N'Project15', 48116)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (16, N'Project16', 19922)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (17, N'Project17', 19061)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (18, N'Project18', 10302)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (19, N'Project19', 44986)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (20, N'Project20', 19497)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (21, N'Project21', 24330)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (22, N'Project22', 18590)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (23, N'Project23', 41615)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (24, N'Project24', 11918)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (25, N'Project25', 38909)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (26, N'Project26', 36190)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (27, N'Project27', 12615)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (28, N'Project28', 43656)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (29, N'Project29', 10935)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (30, N'Project30', 24011)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (31, N'Project31', 41980)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (32, N'Project32', 12356)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (33, N'Project33', 30110)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (34, N'Project34', 16344)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (35, N'Project35', 23931)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (36, N'Project36', 4676)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (37, N'Project37', 8806)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (38, N'Project38', 21644)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (39, N'Project39', 25942)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (40, N'Project40', 42941)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (41, N'Project41', 25387)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (42, N'Project42', 24934)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (43, N'Project43', 25745)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (44, N'Project44', 22885)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (45, N'Project45', 44531)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (46, N'Project46', 9824)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (47, N'Project47', 44773)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (48, N'Project48', 41628)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (49, N'Project49', 48774)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (50, N'Project50', 18915)
GO
INSERT [dbo].[ms_projects] ([id], [title], [budget]) VALUES (51, N'Project51', 22346)
GO