Political Science Analysis

Actor Level Comparisons:
├─ Individual Politicians
│  ├─ Voting records (discipline, independence)
│  ├─ Legislative productivity (bills, amendments, questions)
│  ├─ Committee participation (attendance, contributions)
│  └─ Constituency representation (district alignment)
├─ Political Parties
│  ├─ Electoral performance (vote share, seats)
│  ├─ Coalition behavior (agreement rates, stability)
│  ├─ Policy positions (left-right, GAL-TAN)
│  └─ Organizational strength (membership, funding)
└─ Institutions
   ├─ Parliamentary committees (productivity, influence)
   ├─ Government ministries (budget, effectiveness)
   └─ Electoral districts (turnout, competitiveness)

-- Example: Comparative party discipline analysis
SELECT 
    p.party,
    COUNT(DISTINCT vr.ballot_id) as total_votes,
    COUNT(DISTINCT CASE WHEN vr.vote = party_line.vote THEN vr.ballot_id END) as party_line_votes,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN vr.vote = party_line.vote THEN vr.ballot_id END) / 
          NULLIF(COUNT(DISTINCT vr.ballot_id), 0), 2) as discipline_rate,
    -- Comparative metrics
    AVG(discipline_rate) OVER () as avg_discipline,
    discipline_rate - AVG(discipline_rate) OVER () as deviation_from_mean
FROM view_politician_voting_record vr
JOIN politician p ON vr.politician_id = p.id
JOIN (
    -- Determine party line (majority vote within party)
    SELECT ballot_id, party, vote, COUNT(*) as vote_count
    FROM view_politician_voting_record vr2
    JOIN politician p2 ON vr2.politician_id = p2.id
    GROUP BY ballot_id, party, vote
    QUALIFY ROW_NUMBER() OVER (PARTITION BY ballot_id, party ORDER BY vote_count DESC) = 1
) party_line ON vr.ballot_id = party_line.ballot_id AND p.party = party_line.party
WHERE vr.vote_date >= '2022-01-01'
GROUP BY p.party
ORDER BY discipline_rate DESC;

@Service
public class PoliticalBehaviorAnalysisService {
    
    /**
     * Analyze voting independence vs. party loyalty
     */
    public BehaviorProfile analyzeLegislativeBehavior(String politicianId, LocalDate startDate, LocalDate endDate) {
        // Retrieve voting record
        List<VotingRecord> votes = votingRepository.findByPoliticianAndDateRange(politicianId, startDate, endDate);
        
        // Calculate behavioral metrics
        double partyDiscipline = calculatePartyDiscipline(votes);
        double independenceIndex = 1.0 - partyDiscipline;
        double legislativeActivity = calculateActivityLevel(votes);
        double crossPartyCooperation = calculateCrossPartyVoting(votes);
        
        // Contextual interpretation
        String interpretation = interpretBehaviorProfile(
            partyDiscipline, 
            independenceIndex, 
            crossPartyCooperation
        );
        
        return BehaviorProfile.builder()
            .politicianId(politicianId)
            .period(new Period(startDate, endDate))
            .partyDiscipline(partyDiscipline)
            .independenceIndex(independenceIndex)
            .legislativeActivity(legislativeActivity)
            .crossPartyCooperation(crossPartyCooperation)
            .interpretation(interpretation)
            .build();
    }
    
    private String interpretBehaviorProfile(double discipline, double independence, double crossParty) {
        if (discipline > 0.95 && crossParty < 0.05) {
            return "Highly disciplined party loyalist with minimal cross-party cooperation";
        } else if (independence > 0.20 && crossParty > 0.15) {
            return "Independent-minded politician with significant cross-party engagement";
        } else if (discipline > 0.85 && crossParty > 0.10) {
            return "Generally loyal to party but willing to cooperate across party lines";
        } else {
            return "Moderate party loyalty with selective independence";
        }
    }
}

1. Problem Identification
   ├─ Issue salience (media mentions, questions)
   ├─ Stakeholder mobilization (pressure groups)
   └─ Political attention (parliamentary debates)

2. Policy Formulation
   ├─ Committee deliberations
   ├─ Expert consultations
   └─ Draft legislation

3. Decision Making
   ├─ Parliamentary debate quality
   ├─ Voting outcomes
   └─ Coalition agreement

4. Implementation
   ├─ Budget allocation
   ├─ Agency assignment
   └─ Regulatory framework

5. Evaluation
   ├─ Outcome metrics
   ├─ Cost-benefit analysis
   └─ Public satisfaction

-- Example: Track policy lifecycle from proposal to implementation
CREATE MATERIALIZED VIEW mv_policy_lifecycle AS
SELECT 
    doc.id as proposal_id,
    doc.title as policy_title,
    doc.submitted_date as proposal_date,
    doc.status as current_status,
    
    -- Committee phase
    committee.name as assigned_committee,
    committee.review_duration_days,
    
    -- Voting phase
    ballot.vote_date,
    ballot.yes_votes,
    ballot.no_votes,
    ballot.abstain_votes,
    CASE WHEN ballot.yes_votes > ballot.no_votes THEN 'PASSED' ELSE 'REJECTED' END as outcome,
    
    -- Implementation phase
    budget.allocated_amount,
    ministry.responsible_ministry,
    ministry.implementation_start_date,
    
    -- Policy cycle duration
    (ballot.vote_date - doc.submitted_date) as deliberation_duration,
    (ministry.implementation_start_date - ballot.vote_date) as implementation_lag
FROM riksdagen_document doc
LEFT JOIN committee_review committee ON doc.id = committee.document_id
LEFT JOIN ballot ballot ON doc.ballot_id = ballot.id
LEFT JOIN budget_allocation budget ON doc.id = budget.policy_id
LEFT JOIN ministry_assignment ministry ON doc.id = ministry.policy_id
WHERE doc.type = 'PROPOSITION'
ORDER BY doc.submitted_date DESC;

@Service
public class DemocraticAccountabilityService {
    
    public DemocracyScorecard assessDemocraticQuality(String period) {
        DemocracyScorecard scorecard = new DemocracyScorecard();
        
        // 1. Electoral Accountability
        double turnoutRate = electoralService.calculateTurnoutRate(period);
        double competitivenessIndex = electoralService.calculateCompetitiveness(period);
        scorecard.setElectoralAccountability(
            (turnoutRate * 0.5) + (competitivenessIndex * 0.5)
        );
        
        // 2. Legislative Responsiveness
        double questionActivity = parliamentaryService.calculateQuestionRate(period);
        double constituencyAlignment = parliamentaryService.calculateAlignmentScore(period);
        scorecard.setLegislativeResponsiveness(
            (questionActivity * 0.4) + (constituencyAlignment * 0.6)
        );
        
        // 3. Government Transparency
        double dataCompleteness = platformService.calculateDataCompleteness(period);
        double reportingFrequency = platformService.calculateReportingRate(period);
        scorecard.setGovernmentTransparency(
            (dataCompleteness * 0.6) + (reportingFrequency * 0.4)
        );
        
        // 4. Institutional Effectiveness
        double legislativeProductivity = parliamentaryService.calculateProductivity(period);
        double policyImplementationRate = governmentService.calculateImplementationRate(period);
        scorecard.setInstitutionalEffectiveness(
            (legislativeProductivity * 0.5) + (policyImplementationRate * 0.5)
        );
        
        // 5. Overall Democracy Score (0-100)
        scorecard.setOverallScore(
            (scorecard.getElectoralAccountability() * 0.30) +
            (scorecard.getLegislativeResponsiveness() * 0.25) +
            (scorecard.getGovernmentTransparency() * 0.20) +
            (scorecard.getInstitutionalEffectiveness() * 0.25)
        );
        
        return scorecard;
    }
}

Election Results
    ↓
Speaker Nomination (Talman)
    ↓
Formateur Appointed (Prime Minister Candidate)
    ↓
Coalition Negotiations
    ↓
Government Formation
    ↓
Investiture Vote (Negative Parliamentarism)
    ↓
Government Sworn In

-- Historical coalition analysis
CREATE MATERIALIZED VIEW mv_coalition_history AS
SELECT 
    gov.start_date,
    gov.end_date,
    ARRAY_AGG(party.name ORDER BY party.seat_count DESC) as coalition_parties,
    SUM(party.seat_count) as total_seats,
    ROUND(100.0 * SUM(party.seat_count) / 349, 2) as seat_percentage,
    gov.stability_index,
    gov.duration_months
FROM government gov
JOIN government_party gp ON gov.id = gp.government_id
JOIN party party ON gp.party_id = party.id
GROUP BY gov.id, gov.start_date, gov.end_date, gov.stability_index, gov.duration_months
ORDER BY gov.start_date DESC;

import pandas as pd
import statsmodels.api as sm

# Load voting data
voting_data = pd.read_sql("""
    SELECT 
        politician_id,
        party,
        district_urbanization_rate,
        district_unemployment_rate,
        vote_yes_rate,
        vote_no_rate,
        vote_abstain_rate
    FROM view_politician_voting_summary
""", connection)

# Prepare independent variables
X = voting_data[['district_urbanization_rate', 'district_unemployment_rate']]
X = sm.add_constant(X)

# Dependent variable
y = voting_data['vote_yes_rate']

# Run regression
model = sm.OLS(y, X).fit()
print(model.summary())

# Interpretation: How do district characteristics affect voting patterns?