Skip to content
Analyzing Employees Data with SQL
Analyzing Employees Data With SQL
DataFrameas
df
variable
SHOW TABLES
1. Total employees (past and present) per job title
DataFrameas
df
variable
SELECT B.title,
count(A.emp_no) AS number_of_employees
FROM employees A
JOIN titles B ON A.emp_no = B.emp_no
GROUP BY B.title
ORDER BY number_of_employees DESC
2. Total current employees per job title
DataFrameas
df
variable
SELECT B.title,
count(A.emp_no) AS number_of_employees
FROM salaries A
JOIN titles B ON A.emp_no = B.emp_no
WHERE A.to_date = (SELECT max(to_date) FROM salaries)
GROUP BY B.title
ORDER BY number_of_employees DESC
3. Total employees (past and present) per gender (percentage)
DataFrameas
df
variable
SELECT gender,
round(count(emp_no) / (SELECT count(emp_no) FROM employees),3) AS percentage
FROM employees
GROUP BY gender
ORDER BY percentage DESC
4. Total current employees per gender (percentage)
DataFrameas
df
variable
SELECT A.gender,
round(count(A.emp_no) / (SELECT count(emp_no) FROM employees),3) AS percentage
FROM employees A
JOIN salaries B on B.emp_no = A.emp_no
WHERE B.to_date = (SELECT max(to_date) FROM salaries)
GROUP BY A.gender
ORDER BY percentage DESC
5. Mean salary in company
DataFrameas
df
variable
SELECT avg(salary) AS mean_salary
FROM salaries
WHERE to_date = (SELECT max(to_date) FROM salaries)
6. Mean salary by title and difference from average company salary
DataFrameas
df
variable
SELECT B.title,
avg(A.salary) AS mean_salary,
avg(A.salary) - ( SELECT avg(salary)
FROM salaries
WHERE to_date = (SELECT max(to_date) FROM salaries)) AS difference_from_company_avg_salary
FROM salaries A
JOIN titles B ON A.emp_no = B.emp_no
WHERE A.to_date = (SELECT max(to_date) FROM salaries)
GROUP BY B.title
ORDER BY mean_salary DESC
7. Mean salary by title, gender and difference from average company salary