-
Notifications
You must be signed in to change notification settings - Fork 0
/
HR Data Questions.sql
109 lines (96 loc) · 2.98 KB
/
HR Data Questions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
-- QUESTIONS
-- 1. What is the gender breakdown of employees in the company?
SELECT gender,count(*) AS count
FROM hr
WHERE age >=18 AND termdate ='0000-00-00'
GROUP BY gender;
-- 2. What is the race/ethnicity breakdown of employees in the company?
SELECT race,count(*) AS count
FROM hr
WHERE age >=18 AND termdate ='0000-00-00'
GROUP BY race
ORDER BY count(*) DESC;
-- 3. What is the age distribution of employees in the company?
SELECT
min(age) AS Youngest,
max(age) AS Oldest
FROM hr
WHERE age >=18 AND termdate ='0000-00-00';
SELECT
CASE
WHEN age>=18 AND age <=24 THEN '18-24'
WHEN age>=25 AND age <=34 THEN '25-34'
WHEN age>=35 AND age <=44 THEN '35-44'
WHEN age>=45 AND age <=54 THEN '45-54'
WHEN age>=55 AND age <=64 THEN '55-64'
ELSE '65+'
END AS age_group,gender,
count(*) AS count
FROM hr
WHERE age >=18 AND termdate ='0000-00-00'
GROUP BY age_group,gender
ORDER BY age_group,gender;
-- 4. How many employees work at headquarters versus remote locations?
SELECT location,count(*) AS count
FROM hr
WHERE age >=18 AND termdate ='0000-00-00'
GROUP BY location;
-- 5. What is the average length of employment for employees who have been terminated?
SELECT
round(avg(datediff(termdate,hire_date))/365,0) AS avg_length_employment
FROM hr
WHERE termdate <= curdate() AND termdate<>'0000-00-00'AND age>=18;
-- 6. How does the gender distribution vary across departments and job titles?
SELECT department,gender,count(*) AS count
FROM hr
WHERE age >=18 AND termdate ='0000-00-00'
GROUP BY department,gender
ORDER BY department;
-- 7. What is the distribution of job titles across the company?
SELECT jobtitle,count(*) AS count
FROM hr
WHERE age >=18 AND termdate ='0000-00-00'
GROUP BY jobtitle,gender
ORDER BY jobtitle DESC;
-- 8. Which department has the highest turnover rate?
SELECT department,
total_count,
terminated_count,
terminated_count/total_count AS termination_rate
FROM(
SELECT department,
count(*) AS total_count,
sum(CASE WHEN termdate <>'0000-00-00' AND termdate <= curdate() THEN 1 ELSE 0 END) AS terminated_count
FROM hr
WHERE age >=18
GROUP BY department
) AS subquery
ORDER BY termination_rate DESC;
-- 9. What is the distribution of employees across locations by city and state?
SELECT location_state, count(*) AS count
FROM hr
WHERE age >=18 AND termdate ='0000-00-00'
GROUP BY location_state
ORDER BY count DESC;
-- 10. How has the company's employee count changed over time based on hire and term dates?
SELECT
year,
hires,
terminations,
hires-terminations AS net_change,
round((hires-terminations)/hires*100,2) AS net_change_percent
FROM(
SELECT
YEAR (hire_date) AS year,
count(*) AS hires,
SUM(CASE WHEN termdate <>'0000-00-00' AND termdate <=curdate() THEN 1 ELSE 0 END) AS terminations
FROM hr
WHERE age >=18
GROUP BY YEAR(hire_date)
)AS subquery
ORDER BY year ASC;
-- 11. What is the tenure distribution for each department?
SELECT department,round(avg(datediff(termdate,hire_date)/365),0) AS avg_tenure
FROM hr
WHERE termdate <=curdate() AND termdate <>'0000-00-00'AND age >=18
GROUP BY department;