Skip to content

Analyzing Employees Data With SQL

Spinner
DataFrameas
df
variable
SHOW TABLES

1. Total employees (past and present) per job title

Spinner
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

Spinner
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)

Spinner
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)

Spinner
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

Spinner
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

Spinner
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

‌
‌
‌