Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Salary Share Puzzle

In this puzzle you have to find each department’s salary share with respect to the organizations total salary payout

Please check out the sample input and the expected output below-

Sample Input

EmpId EmpName DeptNo EmpSal
1 A 101 1200
2 B 101 1200
3 C 102 1000
4 D 102 7000
5 E 102 2000
6 F 102 6000
7 G 102 6500
8 H 103 2300
9 I 104 3200
10 J 104 2100
11 K 105 3400
12 L 105 2000
13 M 105 3500

Expected Output

DeptNo % Share
101 5.8
102 54.35
103 5.56
104 12.8
105 21.5

Rules/Restrictions

  • The solution should be should use SINGLE “SELECT” statement.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data


--



CREATE TABLE SalaryDistributionAcrossDept
(	
	 EmpId INT
	,EmpName VARCHAR(1)
	,DeptNo INT
	,EmpSal FLOAT
)
GO

INSERT INTO SalaryDistributionAcrossDept VALUES
(1,'A',101,1200),
(2,'B',101,1200),
(3,'C',102,1000),
(4,'D',102,7000),
(5,'E',102,2000),
(6,'F',102,6000),
(7,'G',102,6500),
(8,'H',103,2300),
(9,'I',104,3200),
(10,'J',104,2100),
(11,'K',105,3400),
(12,'L',105,2000),
(13,'M',105,3500)
GO


--

Solution 1 | Ranking Functions


--



SELECT DISTINCT 
		DeptNo
				
		,ROUND ( ( SUM(EmpSal)OVER(PARTITION BY DeptNo) / SUM(EmpSal)OVER(PARTITION BY 1) ) * 100 , 2)  
                "% Share"
FROM 
		SalaryDistributionAcrossDept

ORDER BY DeptNo


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements