Skip to content

Data Exploration

Visualisations

https://popsql.com/dashboards/cJOmP1TR/employees-dashboard?access_token=ce9e8c57c3e84f3fa2a1c21d660ac732

Spinner
DataFrameas
df1
variable
# Explore the data in the table
SELECT *
FROM employees
LIMIT 5

Basic Workforca Analytics

Gender Distribution

Spinner
DataFrameas
df2
variable
-- Gender distribution

SELECT gender, COUNT(*) as count
FROM employees
GROUP BY gender;

Age Distribution

-- Age distribution
SELECT 
    FLOOR(DATEDIFF(CURRENT_DATE, birth_date)/365) as age,
    COUNT(*) as count
FROM employees
GROUP BY age
ORDER BY age;

Tenure Analysis

Run cancelled
SELECT 
	  FLOOR(datediff(CURRENT_DATE, hire_date)/365) AS years_of_service,
	  COUNT(*) AS employee_count
FROM employees
GROUP BY years_of_service
ORDER BY years_of_service

Departmental Headcount

Run cancelled
SELECT d.dept_name, COUNT(de.emp_no) AS employee_count
FROM employees.departments d
JOIN employees.dept_emp de ON d.dept_no = de.dept_no
WHERE de.to_date > CURRENT_DATE
GROUP BY d.dept_name
ORDER BY employee_count DESC;

Salary Analysis

Average Salary by department

Spinner
DataFrameas
df3
variable
Run cancelled
SELECT d.dept_name, ROUND(AVG(s.salary), 2) AS avg_salary
FROM employees.departments d
JOIN employees.dept_emp de ON d.dept_no = de.dept_no
JOIN employees.salaries s ON de.emp_no = s.emp_no
WHERE s.to_date > CURRENT_DATE
  AND de.to_date > CURRENT_DATE
GROUP BY d.dept_name
ORDER BY avg_salary DESC;

Salary range by department

‌
‌
‌