Loading…
Loading…
case studies · real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
Browse Case Studies Interview questions// SELECT A CASE STUDY
SQL case study · Google
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 — each departure costs an estimated ₹25–40L in recruiting, onboarding, and lost productivity. Reactive exit interviews aren't working; they want a forward-looking risk list.
The ask is a single risk-scoring query that fuses four signals per employee: tenure (years since hire), latest performance score, time since last promotion (with special handling for the never-promoted — NULL promotion dates for employees past 2.5 years), and engagement survey scores (plus survey staleness — an 8-month-old 4.2 is a louder alarm than a fresh one).
Points stack into an attrition risk score with tiers — CRITICAL (60+), HIGH (40+), MEDIUM (20+), LOW — each mapped to a prescribed action, from immediate 1:1 with promotion and raise path down to continue monitoring. The score is a triage tool, not a verdict: a 4.5-rated star never promoted in 3 years may be riskier than their total suggests, and money won't fix disengagement.
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)
);Your target
Identify high-risk employees likely to leave using engagement metrics, compensation anomalies, promotion delays, and tenure-based patterns.
Loading the expected output…
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
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.
Computing the reference output…
One immediate retention case: Neha Gupta scores CRITICAL (65) — disengaged, lowest performance rating, and untouched by surveys for 8 months — book the 1:1 with a promotion and raise path this week. Priya Singh (promotion overdue 2.5 years) and Amit Patel (top performer at 4.5, never promoted in 3 years) are MEDIUM risks needing career plans before they become the next resignations.
What the interviewer asks next
“Amit Patel rates 4.5 — your top performer — yet scores only MEDIUM while never promoted in 3 years. Is the model wrong?”
Strong answer covers: Model critique: additive scores underweight single catastrophic signals; a star who has never been promoted is a resignation letter waiting to happen, whatever the total says.
“Neha Gupta is CRITICAL — do you lead with a promotion and raise for a 2.9-rated, disengaged employee?”
Strong answer covers: Money doesn't fix disengagement: diagnose first (manager? role? burnout via the 8-month survey gap?), because rewarding low performance to stop attrition poisons the rest of the team.
“Tenure of 2.5–3.5 years adds risk points — what real-world mechanism makes that window dangerous?”
Strong answer covers: Connecting math to mechanism: vesting cliffs, the post-honeymoon itch, marketability peaks — scores persuade only when each input maps to a human story.
“Amit Patel sits 14.3% below the Engineering average — just above the −15% penalty line, so zero points. Does 0.7% really separate at-risk from safe?”
Strong answer covers: Threshold-cliff skepticism: hard cutoffs manufacture false precision — argue for graduated scoring or sensitivity bands around every magic number in the model.
“Latest performance uses MAX(score) — with one review each that's harmless, but with five reviews it takes the best, not the latest. What breaks?”
Strong answer covers: Implementation vs intent: MAX rewards a single past peak while hiding decline — the fix (latest-by-date) changes Neha's and others' trajectories, and methodology must match its label.
“Rahul Kumar: 5.1 years, 8.2 engagement, zero risk points — the anti-Neha. What keeps him, and is it replicable?”
Strong answer covers: Studying the saves, not just the risks: the engaged veteran reveals what retention looks like — recent promotion, top-quartile pay, short survey cycles — turning one profile into a template.
Questions test whether your SQL runs. Cases test whether you can own a decision. Learn this once, use it everywhere below.
Restate the grain
One output row = one what? Write it down first — every GROUP BY and join must serve that grain.
Explore before you aggregate
Eyeball every table first: NULL join keys, orphan keys, date coverage. Ten minutes here saves an hour of debugging.
Hand-trace one entity end to end
Compute one row's answer by hand, then demand your query agree. Trust the trace, not the query.
Build incrementally, validate each CTE
Run each CTE standalone and reconcile counts before stacking the next one.
Pressure-test boundaries and empties
Probe cutoffs, zero-activity entities, ties, and empty results — interviewers live on boundaries.
Recommend, don't just report
End with a decision, an owner, and what would change your mind. No recommendation, no case study.