SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY employee_count DESC;

💡 Example 2: Average Salary by Department

SELECT d.department_name, ROUND(AVG(e.salary), 2) AS average_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY average_salary DESC;

💡 Example 3: Identify Highest-Paid Employees

SELECT employee_name, job_title, salary
FROM employees
ORDER BY salary DESC
LIMIT 10;

💡 Example 4: Calculate Attrition Rate

SELECT ROUND(100.0 * SUM(CASE WHEN employment_status = 'Resigned' THEN 1 ELSE 0 END) / COUNT(*), 2) AS attrition_rate
FROM employees;

💡 Example 5: Calculate Attendance Rate

SELECT employee_id, ROUND(100.0 * SUM(CASE WHEN attendance_status = 'Present' THEN 1 ELSE 0 END) / COUNT(*), 2) AS attendance_rate
FROM attendance
GROUP BY employee_id;

💡 Example 6: Rank Employees by Salary

SELECT employee_name, department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

💡 Example 7: Find Employees Who Were Promoted

SELECT e.employee_name, p.old_job_title, p.new_job_title, p.promotion_date
FROM employees e
JOIN promotions p ON e.employee_id = p.employee_id
ORDER BY p.promotion_date;

🎯 Key Insights You Can Derive

🔹 Which departments have the highest headcount?

🔹 Which departments have the highest attrition?

🔹 Which roles have the highest salaries?

🔹 Which employees have been promoted?

🔹 What is the average employee tenure?

🔹 Which departments have attendance problems?

🔹 How quickly is the organization growing?

🔹 Where are the biggest employee retention challenges?

💼 Double Tap ❤️ For More