SQL Portfolio — HR Analytics

Portfolio SQL untuk analisis data HR menggunakan IBM HR Analytics Employee Attrition & Performance dataset (fiksi, dibuat oleh IBM Data Scientists).

Tools: SQLite (browser: sqliteonline.com) | Compatible PostgreSQL / MySQL

---

Struktur Repo

sql-portfolio/
├── README.md
├── hr_employee_attrition.csv   # dataset sumber
├── load_data.py                 # import CSV → SQLite
├── hr_analytics.db             # database (generate via load_data.py)
├── schema/
│   ├── 01_create_tables.sql    # DDL (3 tabel + FK + index)
│   └── 02_seed_lookups.sql     # seed departments & job_roles
└── queries/
    ├── 01_basic_stats.sql      # COUNT, AVG, MIN, MAX
    ├── 02_attrition_rate.sql   # attrition per departemen
    ├── 03_salary_analysis.sql  # gaji vs perf/komposisi
    ├── 04_overtime_attrition.sql # overtime vs keluar
    ├── 05_department_paygap.sql  # pay gap gender per dept (CTE)
    ├── 06_attrition_profile.sql  # CASE + subquery
    ├── 07_retention.sql        # tenure analysis
    ├── 08_performance.sql      # GROUP BY + HAVING
    ├── 09_satisfaction.sql     # multi-join satisfaction
    └── 10_window_functions.sql # RANK, LAG, LEAD, running total

---

Quick Start

1. Generate Database

python3 load_data.py hr_employee_attrition.csv hr_analytics.db

2. Buka di Browser (Tanpa Install)

1. Buka sqliteonline.com
2. Pilih dialect: SQLite
3. Paste isi schema/01_create_tables.sql → Run
4. Paste isi schema/02_seed_lookups.sql → Run
5. Paste isi load_data.py (bagian INSERT) — atau jalankan:

   python3 load_data.py

6. Jalankan query dari folder queries/

---

Dataset Overview

Atribut	Detail
Rows	1,470 employees
Columns	35 attributes
Domain	Attrition, Compensation, Satisfaction, Performance
License	ODBL (Open Database)

Key Tables

• employees — fact table (1,470 rows)
• departments — R&D, Sales, HR (3 rows)
• job_roles — 9 jabatan (lookup)

---

Skill SQL yang Ditampilkan

Level	Topik
Basic	SELECT, WHERE, GROUP BY, ORDER BY, HAVING, AGG (COUNT/SUM/AVG/MIN/MAX)
Intermediate	JOIN (INNER/LEFT), CASE, Subquery, CTE (WITH)
Advanced	Window Functions (RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD, SUM OVER)

---

Referensi

• Dataset: Kaggle — IBM HR Analytics (Open Database License)
• Original: IBM HR Analytics Employee Attrition & Performance (fictional data)