// case studies · real world impact
Explore how companies solve business‑critical problems with SQL – full schema, solution, and insight.
// SELECT A CASE STUDY
Identify high-risk employees likely to leave using engagement metrics, compensation anomalies, promotion delays, and tenure-based patterns.
Google's HR analytics team is under pressure to get ahead of attrition after losing several senior engineers last quarter — each departure costs an estimated ₹25-40L in recruiting, onboarding, and lost productivity. HR wants a single risk-scoring query that combines tenure, latest performance review score, time since last promotion, and recent engagement survey scores into one attrition risk score per employee, then classifies each person into a risk tier (Low/Medium/High) with a recommended intervention (raise, promotion review, manager check-in) attached. Performance, promotion, and engagement data live in separate tables at different grains, and some employees have gaps in their history that should not silently break the score.
Employee data with employee_id, department, salary, hire_date; performance data with employee_id, performance_score, review_date; promotion data with employee_id, promotion_date; and engagement data with employee_id, engagement_score, months_since_last_engagement.
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
employee_name TEXT,
department TEXT,
salary DECIMAL(10,2),
hire_date DATE,
employment_status TEXT
);
CREATE TABLE performance (
review_id INTEGER PRIMARY KEY,
employee_id INTEGER,
performance_score DECIMAL(3,1),
review_date DATE,
FOREIGN KEY(employee_id) REFERENCES employees(employee_id)
);
CREATE TABLE promotions (
promotion_id INTEGER PRIMARY KEY,
employee_id INTEGER,
promotion_date DATE,
FOREIGN KEY(employee_id) REFERENCES employees(employee_id)
);
CREATE TABLE engagement (
employee_id INTEGER PRIMARY KEY,
engagement_score DECIMAL(3,1),
months_since_last_engagement INTEGER,
FOREIGN KEY(employee_id) REFERENCES employees(employee_id)
);WITH tenure_analysis AS (
SELECT
e.employee_id,
e.employee_name,
e.department,
e.salary,
ROUND((julianday('2024-03-15') - julianday(e.hire_date)) / 365.25, 1) as tenure_years,
MAX(p.performance_score) as latest_performance,
COUNT(pr.promotion_id) as promotion_count,
(julianday('2024-03-15') - julianday(MAX(pr.promotion_date))) / 365.25 as years_since_promotion,
en.engagement_score,
en.months_since_last_engagement
FROM employees e
LEFT JOIN performance p ON e.employee_id = p.employee_id
LEFT JOIN promotions pr ON e.employee_id = pr.employee_id
LEFT JOIN engagement en ON e.employee_id = en.employee_id
WHERE e.employment_status = 'Active'
GROUP BY e.employee_id
),
department_salary_benchmark AS (
SELECT
department,
AVG(salary) as avg_salary,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) as p75_salary
FROM employees
GROUP BY department
),
attrition_signals AS (
SELECT
ta.employee_id,
ta.employee_name,
ta.department,
ta.salary,
ta.tenure_years,
ta.latest_performance,
ta.promotion_count,
ROUND(ta.years_since_promotion, 1) as years_since_promotion,
ta.engagement_score,
ta.months_since_last_engagement,
dsb.avg_salary,
ROUND(((ta.salary - dsb.avg_salary) / dsb.avg_salary * 100), 1) as salary_percentile_to_avg,
CASE
WHEN ta.engagement_score < 5 THEN 'DISENGAGED'
WHEN ta.engagement_score BETWEEN 5 AND 7 THEN 'NEUTRAL'
ELSE 'ENGAGED'
END as engagement_level,
CASE
WHEN ta.years_since_promotion IS NULL AND ta.tenure_years > 2.5 THEN 'NEVER_PROMOTED'
WHEN ta.years_since_promotion > 2.0 THEN 'OVERDUE_FOR_PROMOTION'
ELSE 'ON_TRACK'
END as promotion_status
FROM tenure_analysis ta
LEFT JOIN department_salary_benchmark dsb ON ta.department = dsb.department
),
risk_scoring AS (
SELECT
employee_id,
employee_name,
department,
salary,
tenure_years,
latest_performance,
years_since_promotion,
engagement_score,
months_since_last_engagement,
engagement_level,
promotion_status,
(
CASE WHEN engagement_score < 5 THEN 25 ELSE 0 END +
CASE WHEN months_since_last_engagement > 6 THEN 15 ELSE 0 END +
CASE WHEN latest_performance < 3.5 THEN 20 ELSE 0 END +
CASE WHEN promotion_status = 'OVERDUE_FOR_PROMOTION' THEN 20 ELSE 0 END +
CASE WHEN promotion_status = 'NEVER_PROMOTED' THEN 15 ELSE 0 END +
CASE WHEN years_since_promotion > 3 THEN 10 ELSE 0 END +
CASE WHEN salary_percentile_to_avg < -15 THEN 10 ELSE 0 END +
CASE WHEN tenure_years BETWEEN 2.5 AND 3.5 THEN 5 ELSE 0 END
) as attrition_risk_score,
CASE
WHEN (CASE WHEN engagement_score < 5 THEN 25 ELSE 0 END +
CASE WHEN months_since_last_engagement > 6 THEN 15 ELSE 0 END +
CASE WHEN latest_performance < 3.5 THEN 20 ELSE 0 END +
CASE WHEN promotion_status = 'OVERDUE_FOR_PROMOTION' THEN 20 ELSE 0 END +
CASE WHEN promotion_status = 'NEVER_PROMOTED' THEN 15 ELSE 0 END +
CASE WHEN years_since_promotion > 3 THEN 10 ELSE 0 END +
CASE WHEN salary_percentile_to_avg < -15 THEN 10 ELSE 0 END +
CASE WHEN tenure_years BETWEEN 2.5 AND 3.5 THEN 5 ELSE 0 END) >= 60 THEN 'CRITICAL'
WHEN (CASE WHEN engagement_score < 5 THEN 25 ELSE 0 END +
CASE WHEN months_since_last_engagement > 6 THEN 15 ELSE 0 END +
CASE WHEN latest_performance < 3.5 THEN 20 ELSE 0 END +
CASE WHEN promotion_status = 'OVERDUE_FOR_PROMOTION' THEN 20 ELSE 0 END +
CASE WHEN promotion_status = 'NEVER_PROMOTED' THEN 15 ELSE 0 END +
CASE WHEN years_since_promotion > 3 THEN 10 ELSE 0 END +
CASE WHEN salary_percentile_to_avg < -15 THEN 10 ELSE 0 END +
CASE WHEN tenure_years BETWEEN 2.5 AND 3.5 THEN 5 ELSE 0 END) >= 40 THEN 'HIGH'
WHEN (CASE WHEN engagement_score < 5 THEN 25 ELSE 0 END +
CASE WHEN months_since_last_engagement > 6 THEN 15 ELSE 0 END +
CASE WHEN latest_performance < 3.5 THEN 20 ELSE 0 END +
CASE WHEN promotion_status = 'OVERDUE_FOR_PROMOTION' THEN 20 ELSE 0 END +
CASE WHEN promotion_status = 'NEVER_PROMOTED' THEN 15 ELSE 0 END +
CASE WHEN years_since_promotion > 3 THEN 10 ELSE 0 END +
CASE WHEN salary_percentile_to_avg < -15 THEN 10 ELSE 0 END +
CASE WHEN tenure_years BETWEEN 2.5 AND 3.5 THEN 5 ELSE 0 END) >= 20 THEN 'MEDIUM'
ELSE 'LOW'
END as risk_level
FROM attrition_signals
)
SELECT
employee_id,
employee_name,
department,
salary,
tenure_years,
latest_performance,
years_since_promotion,
engagement_score,
engagement_level,
promotion_status,
attrition_risk_score,
risk_level,
CASE
WHEN risk_level = 'CRITICAL' THEN 'Immediate: Offer promotion + raise + 1:1 with manager'
WHEN risk_level = 'HIGH' THEN 'Urgent: Career development plan + compensation review'
WHEN risk_level = 'MEDIUM' THEN 'Schedule: Engagement survey + development opportunities'
ELSE 'Monitor: Continue engagement tracking'
END as recommended_action
FROM risk_scoring
ORDER BY attrition_risk_score DESC;Step-by-step Solution:
Build tenure_analysis CTE to aggregate key metrics: tenure in years, latest performance score, promotion count, and years since last promotion. Join employees with performance, promotions, and engagement tables.
Create department_salary_benchmark to calculate average and 75th percentile salaries by department for competitive analysis.
In attrition_signals, calculate salary percentile relative to department average and classify engagement level and promotion status.
Build risk_scoring CTE with weighted attrition risk scoring: +25 for low engagement (<5/10), +15 for no engagement in 6+ months, +20 for low performance (<3.5), +20 for overdue promotion, +15 for never promoted, +10 for >3 years since promotion, +10 for underpaid vs peers, +5 for 2.5-3.5 year tenure (critical attrition window).
Score tiers: CRITICAL (>=60), HIGH (>=40), MEDIUM (>=20), LOW (<20).
Recommend specific interventions based on risk level and contributing factors. This enables targeted retention and prevents costly departures.