Employees Analytics (SQL Project)

In this notebook we are going to explore Employees database and answer severals questions. The dataset contains information of employees hire from 1985 to 2000.

List of all employees

Queryas

all_employees

variable

SELECT *
FROM employees.employees;

Number of employees

Queryas

number_of_employees

variable

SELECT COUNT(*) AS nb_employees
FROM employees.employees;

Number of employess per gender

DataFrameas

nb_employees_per_gender

variable

SELECT gender, COUNT(*) AS 'nb_employees_per_gender' 
FROM employees.employees 
GROUP BY gender;

New hires per year

Queryas

new_hires_per_year

variable

SELECT	EXTRACT(YEAR FROM hire_date) AS year_hire, 
		COUNT(*) AS new_hires_per_year
FROM employees.employees
GROUP BY EXTRACT(YEAR FROM hire_date)
ORDER BY year_hire ASC;

New hires per month and per year

Queryas

new_hires_per_month_per_year

variable

SELECT	EXTRACT(YEAR FROM hire_date) AS year_hire, 
		EXTRACT(MONTH FROM hire_date) AS month_hire, 
		COUNT(*) AS new_hires_per_year
FROM employees.employees
GROUP BY EXTRACT(YEAR FROM hire_date),EXTRACT(MONTH FROM hire_date)
ORDER BY year_hire DESC,month_hire;

The incomplete data set for the other months of 2000 justifies this discrepancy with other years

Total employees per year (Running total)

Queryas

total_employees_per_year

variable

-- This query does not consider employees who are no longer in the company
SELECT	new_hires_per_year.year_hire,
		SUM(new_hires_per_year.new_hires_per_year) OVER(
				                                     ORDER BY new_hires_per_year.year_hire 
			                                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS total_employees_per_year
FROM new_hires_per_year;

Next, we'll assume that there are employees who are no longer with the company. We'll identify them using the salary table. Those whose "to_date" column is greater than NOW() are still with the company. For the rest, this column corresponds to their leaving date because we assume that if you no longer receive a salary, you're no longer with the company.

‌
‌
‌