HR Analytics SQL Case Study 👨‍💼👩‍💼💼📊

Overview

This project is a SQL-based analytical case study focused on Human Resources (HR) data, designed to answer real-world business questions related to workforce structure, compensation, and employee behavior.

Using BigQuery SQL, the analysis explores how organizations can leverage data to make informed decisions about hiring, compensation, team structure, and workforce distribution.

---

Dataset Description

The dataset follows a relational structure similar to enterprise HR systems and includes the following tables:

Core Tables

• employees → Employee details (salary, manager, department, job)
• departments → Department information
• jobs → Job roles and titles
• job_history → Employee role transitions

Location Tables

• locations → Office locations
• countries → Country-level mapping
• regions → Region classification

---

Business Problems Solved

This case study addresses 9 real-world HR analytics problems:

1. Department performance (headcount & salary)
2. Departments paying above company average
3. Manager effectiveness (team size)
4. Role-wise salary distribution
5. Workforce distribution by geography
6. Identifying inactive departments
7. Career mobility analysis (job changes)
8. Department × role salary benchmarking
9. Top-paying department by region

---

Key Insights

• Higher headcount departments do not always offer higher salaries
• Certain departments consistently pay above company average, indicating cost concentration
• Managers with large teams may face workload imbalance
• Workforce distribution varies significantly across regions
• Some departments exist without employees, indicating structural inefficiencies
• Employees with multiple job changes may represent high performers or role mismatches

---

SQL Concepts Demonstrated

• Aggregations (COUNT, AVG)
• Filtering using HAVING
• Multi-table joins (complex relational queries)
• Subqueries and Common Table Expressions (CTEs)
• Window functions (RANK())
• Conditional business logic
• Hierarchical relationships (manager → employee)

---

Repository Structure

• schema/ → Table definitions, data model and ER Diagram.
• dataset/ → HR dataset available to download, Individual Tables in .CSV format and all Tables as sheets within a EXCEL file
• queries/ → SQL solutions for each business problem
• insights/ → Key analytical findings
• results/ → Screenshots of query runs, Sample outputs and validation

---

How to Use This Repository

1. Navigate to dataset/, Download all the .CSV files, go to your prferred SQL platform, create a DATASET named HR,upload all the table files to it.
2. Navigate to the queries/ folder
3. Each file contains:
   • Problem statement
   • Business context
   • SQL solution
4. Run queries in BigQuery or MySQL or your desired SQL platform from step 1 or adapt for other SQL environments
5. Refer to insights/ for business interpretations
6. You can use the following document for quick reference of Problem statements and SQL Queries: HR Mini Case Study.docx

---

Tools Used

• SQL (BigQuery)
• Relational Data Modeling

---

Author

Shivaling Battarki MIS Executive as Apprentice Exp. | Data Analyst | SQL

Email: shivalingb09@gmail.com LinkedIn: https://www.linkedin.com/in/shivaling-93000/ HackerRank: https://www.hackerrank.com/profile/shivalingb09 LeetCode: https://leetcode.com/u/shivaling09000330/ Scaler: https://www.scaler.com/academy/profile/e2eb7e7a852d/

---

Project Link

GitHub Repository:
https://github.com/Hazardous9hub/HR-ANALYTICS-SQL-CASE-STUDY