Politician Career Trajectory Tracking Across Election Cycles

[pethers]

🎯 Objective

Create views tracking individual politician career trajectories across multiple election cycles, including role changes, party switches, performance evolution, and career longevity patterns.

📋 Background

Politicians' careers span multiple election cycles with varying trajectories - some rise to leadership, others leave politics, some switch parties. Understanding these career patterns is critical for predictive models (resignation risk, defection risk) and historical analysis.

📊 Current State (Measured Metrics)

• Politician Views: 5+ performance tracking views
• Career Tracking: Limited to current assignments and recent activity
• Historical Career Data: ❌ MISSING - No systematic career trajectory tracking
• Party Transition History: ❌ MISSING - Cannot track politicians who switched parties
• Gap: Cannot analyze career patterns like "started 2006, peaked 2014, declined 2022"

✅ Acceptance Criteria

• Create view_riksdagen_politician_career_trajectory tracking performance across cycles
• Create view_riksdagen_politician_role_evolution showing role progression (backbencher → committee → minister)
• Create view_riksdagen_politician_longevity_analysis measuring career duration and activity patterns
• Support queries comparing politician performance: "early career vs late career"
• Detect career patterns: ascending, peak_performance, declining, stable
• Integration with Predictive Intelligence Framework (Framework 4) for career predictions

🛠️ Implementation Guidance

Files to Create:

• service.data.impl/src/main/resources/db/migration/politician-career-views.xml
• service.data.impl/sample-data/framework-validation/predictive/test_4_3_career_trajectory.csv

View Structure Example:

CREATE OR REPLACE VIEW view_riksdagen_politician_career_trajectory AS
WITH career_cycles AS (
    SELECT 
        p.person_id,
        p.first_name,
        p.last_name,
        p.party,
        CASE 
            WHEN EXTRACT(YEAR FROM vd.ballot_date) BETWEEN 2002 AND 2005 THEN 2002
            WHEN EXTRACT(YEAR FROM ballot_date) BETWEEN 2006 AND 2009 THEN 2006
            WHEN EXTRACT(YEAR FROM ballot_date) BETWEEN 2010 AND 2013 THEN 2010
            WHEN EXTRACT(YEAR FROM ballot_date) BETWEEN 2014 AND 2017 THEN 2014
            WHEN EXTRACT(YEAR FROM ballot_date) BETWEEN 2018 AND 2021 THEN 2018
            WHEN EXTRACT(YEAR FROM ballot_date) BETWEEN 2022 AND 2025 THEN 2022
            ELSE 2026
        END AS election_year,
        MIN(vd.ballot_date) AS first_vote,
        MAX(vd.ballot_date) AS last_vote,
        COUNT(*) AS ballot_count,
        AVG(CASE WHEN vote != 'Frånvarande' THEN 1 ELSE 0 END) * 100 AS attendance_rate,
        AVG(CASE WHEN won THEN 1 ELSE 0 END) * 100 AS win_rate,
        COUNT(DISTINCT ad.assignment_id) FILTER (WHERE role_code LIKE 'Ledamot%') AS leadership_roles,
        COUNT(DISTINCT dd.document_id) AS documents_authored
    FROM person_data p
    JOIN vote_data vd ON p.person_id = vd.person_id
    LEFT JOIN assignment_data ad ON p.person_id = ad.person_id
    LEFT JOIN document_data dd ON p.person_id = dd.person_id
    GROUP BY p.person_id, p.first_name, p.last_name, p.party, election_year
),
career_metrics AS (
    SELECT 
        person_id, first_name, last_name, party, election_year,
        ballot_count, attendance_rate, win_rate, documents_authored,
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY election_year) AS career_cycle_number,
        COUNT(*) OVER (PARTITION BY person_id) AS total_cycles,
        MIN(election_year) OVER (PARTITION BY person_id) AS career_start_year,
        MAX(election_year) OVER (PARTITION BY person_id) AS career_end_year,
        AVG(attendance_rate) OVER (PARTITION BY person_id) AS avg_career_attendance,
        LAG(attendance_rate) OVER (PARTITION BY person_id ORDER BY election_year) AS prev_attendance_rate
    FROM career_cycles
)
SELECT 
    person_id, first_name, last_name, party, election_year,
    career_cycle_number, total_cycles,
    career_start_year, career_end_year,
    ballot_count, attendance_rate, win_rate, documents_authored,
    avg_career_attendance,
    attendance_rate - avg_career_attendance AS performance_vs_baseline,
    CASE 
        WHEN career_cycle_number = 1 THEN 'EARLY_CAREER'
        WHEN career_cycle_number::FLOAT / total_cycles < 0.5 THEN 'MID_CAREER'
        ELSE 'LATE_CAREER'
    END AS career_stage,
    CASE 
        WHEN attendance_rate > prev_attendance_rate + 5 THEN 'IMPROVING'
        WHEN attendance_rate < prev_attendance_rate - 5 THEN 'DECLINING'
        ELSE 'STABLE'
    END AS performance_trend
FROM career_metrics
ORDER BY person_id, election_year;

Role Evolution Tracking:

CREATE OR REPLACE VIEW view_riksdagen_politician_role_evolution AS
SELECT 
    p.person_id,
    p.first_name,
    p.last_name,
    p.party,
    ad.role_code,
    ad.status,
    MIN(ad.from_date) AS role_start,
    MAX(ad.to_date) AS role_end,
    EXTRACT(YEAR FROM MIN(ad.from_date)) AS role_start_year,
    COUNT(*) AS role_instances,
    CASE 
        WHEN role_code LIKE '%Minister%' OR role_code LIKE '%Statsråd%' THEN 'MINISTER'
        WHEN role_code LIKE '%Ordförande%' THEN 'COMMITTEE_CHAIR'
        WHEN role_code LIKE '%Ledamot%' AND org_code LIKE 'K%' THEN 'COMMITTEE_MEMBER'
        ELSE 'BACKBENCHER'
    END AS role_tier
FROM person_data p
JOIN assignment_data ad ON p.person_id = ad.person_id
GROUP BY p.person_id, p.first_name, p.last_name, p.party, ad.role_code, ad.status
ORDER BY person_id, role_start;

📚 Related Documentation

• DATA_ANALYSIS_INTOP_OSINT.md - Predictive Intelligence
• view_riksdagen_politician_experience_summary

🤖 Recommended Agent

Agent: @hack23-test-specialist
Rationale: Requires complex career trajectory modeling with multi-cycle performance tracking and role evolution analysis.

For implementation, the Test Specialist will:

• Design career stage classification (early/mid/late career detection)
• Create validation test cases for known career patterns (e.g., Anna Kinberg Batra's rise and resignation)
• Implement role progression tracking with proper assignment data integration
• Validate predictive accuracy for career trajectory classifications
• Ensure query performance for 2,500+ politicians × 7 cycles

📊 Intelligence Value

⭐⭐⭐⭐⭐ VERY HIGH - Critical for understanding political career dynamics, predicting resignations, and analyzing talent retention.

🔗 Dependencies

• Depends on Historical Election Cycle Trend Views: Swedish Parliament Context, Meta-View Only, and Explicit All-Framework Analytical Trend Specs #8208 (Election Cycle Views) for election_year dimension
• Integrates with assignment_data and document_data tables