HR Attrition & Workforce Dashboard

-- HR attrition dataset: raw -> clean (SQLite syntax, minor changes needed for MySQL or PostgreSQL)
-- Source: employees_raw (51 rows, SYNTHETIC data, snapshot date 2026-09-30)

DROP TABLE IF EXISTS employees_clean;

CREATE TABLE employees_clean AS
WITH dedup AS (                                   -- 1) remove exact duplicate employee_id rows
  SELECT *, ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY rowid) AS rn
  FROM employees_raw
)
SELECT
  employee_id,
  CASE                                            -- 2) standardise department labels
    WHEN LOWER(TRIM(department)) IN ('eng','engineering') THEN 'Engineering'
    WHEN LOWER(TRIM(department)) = 'hr'                   THEN 'HR'
    ELSE TRIM(department)
  END AS department,
  job_title,
  job_level,
  CASE LOWER(TRIM(location))                      -- 3) standardise location labels
    WHEN 'bangalore' THEN 'Bengaluru'
    ELSE UPPER(SUBSTR(TRIM(location),1,1)) || LOWER(SUBSTR(TRIM(location),2))
  END AS location,
  work_mode,
  CASE                                            -- 4) one date format (YYYY-MM-DD)
    WHEN hire_date LIKE '__-__-____'
      THEN SUBSTR(hire_date,7,4) || '-' || SUBSTR(hire_date,4,2) || '-' || SUBSTR(hire_date,1,2)
    ELSE hire_date
  END AS hire_date,
  tenure_months,
  age_band,
  CASE WHEN gender IN ('F','Female') THEN 'F'     -- 5) standardise gender codes
       WHEN gender IN ('M','Male')   THEN 'M' END AS gender,
  annual_ctc_inr,
  NULLIF(performance_rating,'') AS performance_rating,  -- 6) blanks -> NULL (not imputed)
  engagement_score,
  promoted_last_24m,
  overtime_flag,
  status,
  NULLIF(exit_date,'')   AS exit_date,
  NULLIF(exit_reason,'') AS exit_reason
FROM dedup
WHERE rn = 1;

-- Validation queries
SELECT COUNT(*) AS rows_after_cleaning FROM employees_clean;                       -- expect 50
SELECT COUNT(*) AS rows_before FROM employees_raw;                                 -- 51
SELECT COUNT(*) AS missing_ratings FROM employees_clean WHERE performance_rating IS NULL;  -- 2
SELECT department, COUNT(*) FROM employees_clean GROUP BY department;

What each step does

• —Dedupe with ROW_NUMBER: keep the first row per employee_id. This dataset has one exact duplicate; conflicting records would need a separate review.
• —Standardise text with CASE and TRIM: reconcile department labels, location names and gender codes.
• —Unify dates: convert DD-MM-YYYY to YYYY-MM-DD, keeping already standard dates.
• —Use NULLIF for blanks: preserve missing ratings and empty exit fields as NULL rather than inventing values.

Validation queries