Promotion Eligibility Matrix

Engineering ladder review. For each employee, assign a promotion-eligibility tag based on tenure (years from hire_date to 2024-06-01) and performance_rating. Branches in priority order:

1. 'Hold' — performance_rating < 3.5 (regardless of tenure — performance gates everything)
2. 'New Hire' — tenure_years < 1
3. 'Fast-Track' — performance_rating >= 4.5 AND tenure_years >= 2
4. 'Eligible' — performance_rating >= 4.0 AND tenure_years >= 3
5. 'On Track' — everyone else

Compute tenure_years as CAST((JULIANDAY('2024-06-01') - JULIANDAY(hire_date)) / 365 AS INTEGER). Show name, department, tenure_years, performance_rating, eligibility. Sort by performance_rating descending, then tenure_years descending.

Two traps stack here. First: branch order — 'New Hire' must come before 'Fast-Track' or new high performers wrongly get fast-tracked at 8 months. Second: SQL has no local variables, so the JULIANDAY tenure expression has to be repeated inside CASE (or wrapped in a subquery / CTE). Forgetting THEN after a long compound WHEN clause is the silent killer — you stare at the query and the parser just says "syntax error" with no clue where.