Database View Intelligence Catalog

PoliticianLazy (P-01): Provides politician identification
PoliticianIneffectiveVoting (P-02): Links to voting analysis
All Politician Rules: Base identification layer

Intelligence Applications

This view supports multiple analytical frameworks from DATA_ANALYSIS_INTOP_OSINT.md:

Analysis Framework	Use Case	Example Application	Link
Temporal Analysis	Track career duration and entry cohorts	Monitor politician career trajectories over time	Framework Docs
Comparative Analysis	Party composition and experience levels	Compare experience distribution across parties	Framework Docs
Pattern Recognition	Demographic clustering analysis	Identify patterns in gender, age, regional representation	Framework Docs
Predictive Intelligence	Career trajectory modeling	Predict re-election likelihood based on profile	Framework Docs
Network Analysis	Politician identification for network construction	Base layer for building collaboration and voting networks	Framework Docs

Intelligence Products Generated:

🏆 Political Scorecards - Basic identification and demographic information
📊 Party Composition Reports - Gender balance, age distribution, regional representation
🎯 Recruitment Analysis - New politician tracking and onboarding patterns

Data Flow: See Intelligence Data Flow Map - View to Analysis Mapping for complete data pipeline.

Intelligence Frameworks Applicable

Demographic Analysis: Gender, age, regional representation
Comparative Analysis: Party composition, experience levels
Temporal Analysis: Career duration, entry cohorts

Integration with Product Features

Politician Dashboard (Product Line 1): Core profile data
Search & Discovery (Product Line 1): Politician lookup
Comparative Analytics (Product Line 2): Party composition

view_riksdagen_politician_experience_summary ⭐⭐⭐⭐⭐

Category: Politician Intelligence Views (v1.28)
Type: Standard View
Intelligence Value: VERY HIGH - Experience Scoring & Classification
Changelog: v1.28 OSINT Experience Tracking

Purpose

Sophisticated experience scoring system that calculates weighted experience based on assignment types, durations, and roles. Provides standardized experience classification from NOVICE to VETERAN, enabling experience-based analysis and comparative assessments.

⚠️ Data Quality Note: This view was affected by a missing role pattern issue that has been resolved.
Issue: Missing 'Förste vice talman' in talmansuppdrag scoring (affected 8 records)
Status: ✅ Fixed in Liquibase changeset db-changelog-1.44.xml
Details: See Data Quality Analysis in README-SCHEMA-MAINTENANCE.md

Key Columns

Column	Type	Description	Example
`person_id`	VARCHAR(255)	Unique politician identifier	'0532213467925'
`first_name`	VARCHAR(255)	Politician first name	'Anna'
`last_name`	VARCHAR(255)	Politician last name	'Andersson'
`party`	VARCHAR(50)	Current party affiliation	'S'
`total_assignments`	INTEGER	Total number of assignments	15
`total_days`	INTEGER	Total days in active assignments	2847
`total_years`	NUMERIC(10,2)	Total years experience	7.79
`ministerial_days`	INTEGER	Days in government positions	1095
`committee_chair_days`	INTEGER	Days as committee chair	547
`committee_member_days`	INTEGER	Days as committee member	1825
`parliamentary_member_days`	INTEGER	Days as MP without other roles	380
`total_weighted_exp`	NUMERIC(15,2)	Weighted experience score	4783.50
`experience_level`	VARCHAR(50)	Classification	'EXPERIENCED', 'VETERAN', 'NOVICE'
`ministerial_experience`	BOOLEAN	Has held government position	TRUE
`committee_leadership_exp`	BOOLEAN	Has been committee chair	TRUE
`avg_assignment_duration`	NUMERIC(10,2)	Average assignment length (days)	189.80

Weighting System

Experience is weighted by assignment importance:

Assignment Type	Weight Multiplier	Rationale
Ministerial	2.0x	Government executive responsibility
Committee Chair	1.5x	Leadership and organizational authority
Committee Member	1.2x	Specialized legislative work
Parliamentary Member	1.0x	Base legislative responsibility

Weighted Experience Formula:

total_weighted_exp = (ministerial_days × 2.0) + 
                     (committee_chair_days × 1.5) + 
                     (committee_member_days × 1.2) + 
                     (parliamentary_member_days × 1.0)

Experience Level Classification

Level	Criteria	Description
NOVICE	< 365 days	First year, learning parliamentary procedures
DEVELOPING	365-1095 days (1-3 years)	Building expertise, establishing networks
EXPERIENCED	1095-2190 days (3-6 years)	Established politician, strong influence
VETERAN	2190-3650 days (6-10 years)	Senior parliamentarian, institutional knowledge
ELDER_STATESMAN	> 3650 days (10+ years)	Parliamentary elder, mentor, historical perspective

Example Queries

1. Experience Distribution by Party

SELECT
    party,
    experience_level,
    COUNT(*) AS member_count,
    ROUND(AVG(total_years), 1) AS avg_years,
    ROUND(AVG(total_weighted_exp), 0) AS avg_weighted_exp
FROM view_riksdagen_politician_experience_summary
WHERE total_days > 0  -- Active or has history
GROUP BY party, experience_level
ORDER BY party, 
    CASE experience_level
        WHEN 'ELDER_STATESMAN' THEN 5
        WHEN 'VETERAN' THEN 4
        WHEN 'EXPERIENCED' THEN 3
        WHEN 'DEVELOPING' THEN 2
        WHEN 'NOVICE' THEN 1
    END DESC;

2. Top 20 Most Experienced Politicians (Weighted Score)

SELECT
    first_name,
    last_name,
    party,
    total_years,
    total_weighted_exp,
    experience_level,
    ministerial_experience,
    committee_leadership_exp
FROM view_riksdagen_politician_experience_summary
ORDER BY total_weighted_exp DESC
LIMIT 20;

3. Former Ministers Currently in Parliament

SELECT
    first_name,
    last_name,
    party,
    ministerial_days,
    ROUND(ministerial_days / 365.25, 1) AS ministerial_years,
    total_years AS total_parliamentary_years
FROM view_riksdagen_politician_experience_summary
WHERE ministerial_experience = TRUE
    AND ministerial_days > 0
ORDER BY ministerial_days DESC;

4. Committee Leadership Experience by Party

SELECT
    party,
    COUNT(*) FILTER (WHERE committee_leadership_exp = TRUE) AS leaders_count,
    COUNT(*) AS total_count,
    ROUND(100.0 * COUNT(*) FILTER (WHERE committee_leadership_exp = TRUE) / COUNT(*), 1) AS leadership_pct,
    ROUND(AVG(committee_chair_days) FILTER (WHERE committee_chair_days > 0), 0) AS avg_chair_days
FROM view_riksdagen_politician_experience_summary
WHERE total_days > 0
GROUP BY party
ORDER BY leadership_pct DESC;

5. Experience Gap Analysis (Novices vs Veterans)

WITH experience_extremes AS (
    SELECT
        party,
        COUNT(*) FILTER (WHERE experience_level IN ('NOVICE', 'DEVELOPING')) AS junior_count,
        COUNT(*) FILTER (WHERE experience_level IN ('VETERAN', 'ELDER_STATESMAN')) AS senior_count,
        COUNT(*) AS total
    FROM view_riksdagen_politician_experience_summary
    WHERE total_days > 0
    GROUP BY party
)
SELECT
    party,
    junior_count,
    senior_count,
    total,
    ROUND(100.0 * junior_count / total, 1) AS junior_pct,
    ROUND(100.0 * senior_count / total, 1) AS senior_pct,
    CASE
        WHEN junior_count > senior_count * 2 THEN 'HIGH_RENEWAL'
        WHEN senior_count > junior_count * 2 THEN 'VETERAN_DOMINATED'
        ELSE 'BALANCED'
    END AS composition_assessment
FROM experience_extremes
ORDER BY party;

Performance Characteristics

Query Time: 50-100ms (complex aggregation)
Indexes Used: idx_assignment_data_person, idx_assignment_data_role_code
Data Volume: ~2,000 rows (all politicians with assignments)
Refresh Frequency: Real-time (standard view with complex calculations)
Sample Data: view_riksdagen_politician_experience_summary_sample.csv (2,093 rows with JSON columns for detailed experience breakdown)

Data Sources

Primary Table: assignment_data (all assignments with date ranges)
Joined Tables: person_data (for identification)

Dependencies

No view dependencies (built directly on source tables)
Future: May be materialized in v1.32 for performance

Risk Rules Supported

PoliticianLackExperience (P-10): Identifies novice politicians
PoliticianOverexperiencedDisengaged (P-11): Tracks elder statesmen
Experience context for all politician risk rules

Intelligence Frameworks Applicable

Comparative Analysis: Experience benchmarking across parties
Pattern Recognition: Experience clustering and composition
Predictive Intelligence: Experience-performance correlation

Integration with Product Features

Politician Dashboard (Product Line 1): Experience metrics
Advanced Analytics (Product Line 2): Experience distribution
Comparative Tools (Product Line 2): Experience benchmarking

view_politician_behavioral_trends ⭐⭐⭐⭐⭐

Category: Intelligence Views (v1.30)
Type: Standard View
Intelligence Value: VERY HIGH - Behavioral Pattern Analysis
Changelog: v1.30 OSINT Performance Tracking

Purpose

Tracks individual politician behavioral metrics (absence, effectiveness, rebellion) over time with monthly granularity over a 3-year rolling window. Includes automated classification and trend detection for early warning of performance degradation and behavioral anomalies.

Key Columns

Column	Type	Description	Example
`person_id`	VARCHAR(255)	Unique politician identifier	'Q123456'
`first_name`	VARCHAR(255)	Politician first name	'Anna'
`last_name`	VARCHAR(255)	Politician last name	'Andersson'
`party`	VARCHAR(50)	Current party affiliation	'S'
`year_month`	DATE	Month of aggregation (first day)	'2024-10-01'
`ballot_count`	INTEGER	Number of ballots in period	45
`avg_absence_rate`	NUMERIC(5,2)	Percentage absent	12.50
`avg_win_rate`	NUMERIC(5,2)	Percentage on winning side	67.80
`avg_rebel_rate`	NUMERIC(5,2)	Percentage against party	8.30
`absence_trend`	NUMERIC(5,2)	Change from previous month	+2.50
`effectiveness_trend`	NUMERIC(5,2)	Win rate change	-3.20
`rebellion_trend`	NUMERIC(5,2)	Rebel rate change	+1.10
`ma_3month_absence`	NUMERIC(5,2)	3-month moving average absence	11.20
`ma_3month_win`	NUMERIC(5,2)	3-month moving average win rate	69.00
`ma_3month_rebel`	NUMERIC(5,2)	3-month moving average rebel rate	7.50
`attendance_status`	VARCHAR(50)	Classification	'MODERATE_ABSENTEEISM'
`effectiveness_status`	VARCHAR(50)	Classification	'EFFECTIVE'
`discipline_status`	VARCHAR(50)	Classification	'LOW_INDEPENDENCE'
`behavioral_assessment`	VARCHAR(50)	Overall assessment	'STANDARD_BEHAVIOR'

Classification Thresholds

Attendance Status:

EXCELLENT_ATTENDANCE: < 5% absence
GOOD_ATTENDANCE: 5-10% absence
MODERATE_ABSENTEEISM: 10-20% absence
HIGH_ABSENTEEISM: 20-30% absence
CRITICAL_ABSENTEEISM: > 30% absence

Effectiveness Status:

HIGHLY_EFFECTIVE: > 70% win rate
EFFECTIVE: 55-70% win rate
MODERATE_EFFECTIVENESS: 45-55% win rate
INEFFECTIVE: < 45% win rate

Discipline Status:

HIGH_INDEPENDENCE: > 15% rebellion (frequent dissent)
MODERATE_INDEPENDENCE: 10-15% rebellion
LOW_INDEPENDENCE: 5-10% rebellion
PARTY_LINE: < 5% rebellion (high discipline)

Behavioral Assessment (Combined):

EXCELLENT_BEHAVIOR: Excellent attendance + high effectiveness + low rebellion
STANDARD_BEHAVIOR: Normal patterns across all metrics
MODERATE_RISK: One metric concerning, others normal
ELEVATED_RISK: Multiple concerning metrics
CRITICAL_CONCERN: All metrics problematic

Example Queries

1. Top 10 Politicians with Declining Attendance (Last 6 Months)

SELECT
    first_name,
    last_name,
    party,
    AVG(avg_absence_rate) AS avg_absence,
    AVG(absence_trend) AS avg_trend,
    COUNT(*) AS months_tracked
FROM view_politician_behavioral_trends
WHERE year_month >= CURRENT_DATE - INTERVAL '6 months'
    AND ballot_count >= 10  -- Minimum sample size
GROUP BY person_id, first_name, last_name, party
HAVING AVG(absence_trend) > 0  -- Declining attendance
ORDER BY AVG(absence_trend) DESC
LIMIT 10;

2. High-Risk Politicians (Moderate Risk or Worse)

SELECT
    first_name,
    last_name,
    party,
    year_month,
    avg_absence_rate,
    avg_win_rate,
    avg_rebel_rate,
    behavioral_assessment
FROM view_politician_behavioral_trends
WHERE year_month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
    AND behavioral_assessment IN ('MODERATE_RISK', 'ELEVATED_RISK', 'CRITICAL_CONCERN')
ORDER BY 
    CASE behavioral_assessment
        WHEN 'CRITICAL_CONCERN' THEN 3
        WHEN 'ELEVATED_RISK' THEN 2
        WHEN 'MODERATE_RISK' THEN 1
    END DESC,
    party, last_name;

3. Party Average Comparison (Individual vs. Party Benchmark)

WITH party_averages AS (
    SELECT
        party,
        AVG(avg_absence_rate) AS party_avg_absence,
        AVG(avg_win_rate) AS party_avg_win,
        AVG(avg_rebel_rate) AS party_avg_rebel
    FROM view_politician_behavioral_trends
    WHERE year_month >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY party
)
SELECT
    pbt.first_name,
    pbt.last_name,
    pbt.party,
    ROUND(AVG(pbt.avg_absence_rate), 2) AS individual_absence,
    ROUND(pa.party_avg_absence, 2) AS party_avg_absence,
    ROUND(AVG(pbt.avg_absence_rate) - pa.party_avg_absence, 2) AS absence_vs_party,
    CASE
        WHEN AVG(pbt.avg_absence_rate) > pa.party_avg_absence + 10 THEN 'OUTLIER_HIGH'
        WHEN AVG(pbt.avg_absence_rate) < pa.party_avg_absence - 10 THEN 'OUTLIER_LOW'
        ELSE 'NORMAL'
    END AS classification
FROM view_politician_behavioral_trends pbt
JOIN party_averages pa ON pa.party = pbt.party
WHERE pbt.year_month >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY pbt.person_id, pbt.first_name, pbt.last_name, pbt.party, pa.party_avg_absence
ORDER BY absence_vs_party DESC
LIMIT 30;

4. Behavioral Trend Analysis (3-Month Moving Average)

SELECT
    person_id,
    first_name,
    last_name,
    party,
    year_month,
    avg_absence_rate,
    ma_3month_absence,
    avg_absence_rate - ma_3month_absence AS deviation_from_ma,
    attendance_status
FROM view_politician_behavioral_trends
WHERE year_month >= CURRENT_DATE - INTERVAL '12 months'
    AND ballot_count >= 10
    AND ABS(avg_absence_rate - ma_3month_absence) > 5  -- Significant deviation
ORDER BY ABS(avg_absence_rate - ma_3month_absence) DESC
LIMIT 20;

5. Excellence Report (Top Performers)

SELECT
    first_name,
    last_name,
    party,
    ROUND(AVG(avg_absence_rate), 2) AS avg_absence,
    ROUND(AVG(avg_win_rate), 2) AS avg_win,
    ROUND(AVG(avg_rebel_rate), 2) AS avg_rebel,
    COUNT(DISTINCT year_month) AS months_tracked
FROM view_politician_behavioral_trends
WHERE year_month >= CURRENT_DATE - INTERVAL '12 months'
    AND ballot_count >= 10
GROUP BY person_id, first_name, last_name, party
HAVING AVG(avg_absence_rate) < 5  -- Excellent attendance
    AND AVG(avg_win_rate) > 65     -- High effectiveness
ORDER BY avg_absence ASC, avg_win DESC
LIMIT 20;

Performance Characteristics

Query Time: 100-200ms (complex aggregation over time series)
Indexes Used: idx_vote_summary_daily_date_person
Data Volume: ~15,000 rows (350 politicians × 36 months rolling window)
Refresh Frequency: Real-time (recalculated on query)
Optimization Note: Candidate for materialization in v1.32

Data Sources

Primary View: view_riksdagen_vote_data_ballot_politician_summary_daily
Aggregation: Monthly rollup with LAG() for trend calculation
Moving Averages: 3-month window using window functions

Dependencies

Depends on: view_riksdagen_vote_data_ballot_politician_summary_daily
Used by: view_risk_score_evolution, intelligence dashboards

Risk Rules Supported

PoliticianLazy (P-01): avg_absence_rate, attendance_status
PoliticianIneffectiveVoting (P-02): avg_win_rate, effectiveness_status
PoliticianHighRebelRate (P-03): avg_rebel_rate, discipline_status
PoliticianDecliningEngagement (P-04): All trend metrics
PoliticianCombinedRisk (P-05): behavioral_assessment

Intelligence Frameworks Applicable

Temporal Analysis: Monthly time-series with moving averages
Comparative Analysis: Individual vs. party benchmarking
Pattern Recognition: Behavioral classification and clustering
Predictive Intelligence: Trend extrapolation for forecasting

view_riksdagen_politician_document ⭐⭐⭐⭐

Category: Document Views (v1.1, enhanced v1.24)
Type: Standard View
Intelligence Value: HIGH - Legislative Productivity Tracking

Purpose

Tracks all documents (motions, interpellations, written questions, proposals) authored or co-authored by individual politicians. Provides productivity metrics and policy focus analysis based on document types and submission patterns.

Key Columns

Column	Type	Description	Example
`person_id`	VARCHAR(255)	Politician identifier	'0532213467925'
`first_name`	VARCHAR(255)	Politician first name	'Anna'
`last_name`	VARCHAR(255)	Politician last name	'Andersson'
`party`	VARCHAR(50)	Party at document submission	'S'
`document_id`	VARCHAR(255)	Document identifier	'H801234'
`document_type`	VARCHAR(50)	Type of document	'Interpellation', 'Motion'
`title`	TEXT	Document title	'Svar på fråga om...'
`sub_title`	TEXT	Document subtitle	Additional context
`made_public_date`	DATE	Publication date	'2024-10-15'
`document_status`	VARCHAR(50)	Current status	'Besvarad', 'Bordlagd'
`org_code`	VARCHAR(20)	Originating organization	'au' (Labor committee)
`label`	VARCHAR(100)	Document category/label	'Arbetsmarknad'

Document Types

Type	Swedish Term	Description	Frequency
Motion	Motion	Legislative proposal by MP	Common
Interpellation	Interpellation	Question to minister requiring debate	Moderate
Written Question	Skriftlig fråga	Written question to minister	Common
Simple Question	Fråga	Simple parliamentary question	Very Common
EU Document	EU-dokument	EU-related document	Rare
Committee Proposal	Utskottsförslag	Committee recommendation	Common

Example Queries

1. Top 20 Most Productive Politicians (Last 12 Months)

SELECT
    first_name,
    last_name,
    party,
    COUNT(*) AS total_documents,
    COUNT(*) FILTER (WHERE document_type = 'Motion') AS motions,
    COUNT(*) FILTER (WHERE document_type = 'Interpellation') AS interpellations,
    COUNT(*) FILTER (WHERE document_type = 'Skriftlig fråga') AS written_questions,
    MIN(made_public_date) AS first_document,
    MAX(made_public_date) AS latest_document
FROM view_riksdagen_politician_document
WHERE made_public_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY person_id, first_name, last_name, party
ORDER BY total_documents DESC
LIMIT 20;

2. Policy Focus Analysis by Politician

SELECT
    first_name,
    last_name,
    party,
    org_code AS policy_area,
    COUNT(*) AS document_count,
    STRING_AGG(DISTINCT document_type, ', ') AS document_types,
    MIN(made_public_date) AS first_activity,
    MAX(made_public_date) AS latest_activity
FROM view_riksdagen_politician_document
WHERE made_public_date >= CURRENT_DATE - INTERVAL '24 months'
GROUP BY person_id, first_name, last_name, party, org_code
HAVING COUNT(*) >= 5  -- Minimum activity threshold
ORDER BY person_id, document_count DESC;

3. Interpellation Activity (Parliamentary Accountability)

SELECT
    first_name,
    last_name,
    party,
    COUNT(*) AS interpellation_count,
    ARRAY_AGG(DISTINCT org_code ORDER BY org_code) AS ministries_questioned,
    MIN(made_public_date) AS first_interpellation,
    MAX(made_public_date) AS latest_interpellation
FROM view_riksdagen_politician_document
WHERE document_type = 'Interpellation'
    AND made_public_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY person_id, first_name, last_name, party
ORDER BY interpellation_count DESC
LIMIT 30;

4. Productivity Trends (Quarterly Analysis)

WITH quarterly_productivity AS (
    SELECT
        person_id,
        first_name,
        last_name,
        party,
        DATE_TRUNC('quarter', made_public_date) AS quarter,
        COUNT(*) AS documents_per_quarter
    FROM view_riksdagen_politician_document
    WHERE made_public_date >= CURRENT_DATE - INTERVAL '24 months'
    GROUP BY person_id, first_name, last_name, party, DATE_TRUNC('quarter', made_public_date)
)
SELECT
    first_name,
    last_name,
    party,
    AVG(documents_per_quarter) AS avg_quarterly_documents,
    MAX(documents_per_quarter) AS peak_quarter_documents,
    MIN(documents_per_quarter) AS min_quarter_documents,
    COUNT(DISTINCT quarter) AS quarters_active
FROM quarterly_productivity
GROUP BY person_id, first_name, last_name, party
HAVING COUNT(DISTINCT quarter) >= 4  -- At least 4 quarters of activity
ORDER BY avg_quarterly_documents DESC
LIMIT 20;

5. Cross-Party Collaboration (Co-Authored Documents)

-- This would require joining with a co-author mapping table
-- Simplified version showing single-party vs cross-party documents
SELECT
    pd1.first_name,
    pd1.last_name,
    pd1.party,
    COUNT(DISTINCT pd1.document_id) AS total_documents,
    COUNT(DISTINCT CASE 
        WHEN pd2.party IS NOT NULL AND pd2.party != pd1.party 
        THEN pd1.document_id 
    END) AS cross_party_documents,
    ROUND(100.0 * COUNT(DISTINCT CASE 
        WHEN pd2.party IS NOT NULL AND pd2.party != pd1.party 
        THEN pd1.document_id 
    END) / NULLIF(COUNT(DISTINCT pd1.document_id), 0), 1) AS cross_party_pct
FROM view_riksdagen_politician_document pd1
LEFT JOIN view_riksdagen_politician_document pd2 
    ON pd2.document_id = pd1.document_id 
    AND pd2.person_id != pd1.person_id
WHERE pd1.made_public_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY pd1.person_id, pd1.first_name, pd1.last_name, pd1.party
HAVING COUNT(DISTINCT pd1.document_id) >= 10
ORDER BY cross_party_pct DESC
LIMIT 20;

Performance Characteristics

Query Time: 50-100ms (indexed on date and person_id)
Indexes Used: idx_politician_document_date_person, idx_politician_document_type
Data Volume: ~500,000 rows (all historical documents)
Refresh Frequency: Real-time (standard view)

Data Sources

Primary Table: document_data (all parliamentary documents)
Joined Tables: person_data (for politician identification)

Dependencies

No view dependencies
Used by: view_riksdagen_politician_document_daily_summary

Risk Rules Supported

PoliticianLowProductivity (P-06): Document count metric
PoliticianNoDocumentsRecent (P-07): Recent activity tracking

Intelligence Frameworks Applicable

Productivity Analysis: Document volume and type distribution
Policy Focus Analysis: Topic clustering and specialization
Temporal Analysis: Activity patterns and trends

view_riksdagen_politician_ballot_summary ⭐⭐⭐⭐⭐

Category: Politician Voting Views (v1.25)
Type: Standard View
Intelligence Value: VERY HIGH - Comprehensive Voting Record Analysis

Purpose

Aggregates complete voting record for each politician across all ballots, providing win/loss percentages, absence statistics, rebellion rates, and party discipline metrics. Essential for politician performance scorecards and voting behavior analysis.

Key Metrics

Total Votes: Lifetime ballot participation count
Win Percentage: Percentage of votes on winning side
Absence Percentage: Rate of missed votes
Rebellion Rate: Votes against party line percentage
Party Loyalty Score: Votes with party percentage

Sample Query: Top Performers

SELECT first_name, last_name, party, total_votes, win_percentage, absence_percentage
FROM view_riksdagen_politician_ballot_summary
WHERE total_votes >= 100
ORDER BY win_percentage DESC, absence_percentage ASC
LIMIT 20;

Intelligence Applications

Performance scorecards and politician rankings
Party discipline analysis
Absence problem identification
Career voting statistics

view_riksdagen_politician_influence_metrics ⭐⭐⭐⭐⭐

Category: Intelligence Views (v1.30)
Type: Standard View
Intelligence Value: VERY HIGH - Network Analysis & Power Mapping

Purpose

Calculates politician influence and network centrality metrics including degree centrality, betweenness, cross-party collaboration scores. Identifies power brokers and bridge politicians facilitating coalition formation.

Key Metrics

Broker Score: Power broker metric (0-1 scale)
Cross-Party Ratio: % connections outside own party
Connectivity Level: Network position classification
Influence Rank: Relative influence ranking

Sample Query: Power Brokers

SELECT first_name, last_name, party, broker_score, cross_party_connections, broker_classification
FROM view_riksdagen_politician_influence_metrics
WHERE broker_classification IN ('STRONG_BROKER', 'MODERATE_BROKER')
ORDER BY broker_score DESC
LIMIT 15;

Intelligence Applications

Coalition facilitator identification
Network power structure mapping
Cross-bloc bridge analysis
Influence hierarchy visualization

view_politician_risk_summary ⭐⭐⭐⭐⭐

Category: Intelligence Views (v1.30)
Type: Standard View
Intelligence Value: VERY HIGH - Current Risk State Assessment

Purpose

Provides current-state risk summary for all politicians with latest risk scores, violation counts, and risk classifications. Optimized for dashboard displays and real-time risk monitoring.

Key Metrics

Current Risk Score: Latest aggregate risk points
Risk Severity: CRITICAL, MAJOR, MINOR classification
Active Violations: Current rule violation count
Risk Trajectory: ESCALATING, STABLE, IMPROVING

Sample Query: High-Risk Dashboard

SELECT first_name, last_name, party, current_risk_score, risk_severity, active_violations
FROM view_politician_risk_summary
WHERE risk_severity IN ('CRITICAL', 'MAJOR')
ORDER BY current_risk_score DESC;

Intelligence Applications

Real-time risk monitoring dashboards
Alert systems for high-risk politicians
Party risk distribution analysis
Risk intervention prioritization

view_riksdagen_politician_decision_pattern ⭐⭐⭐⭐⭐

Category: Politician Intelligence Views (v1.35)
Type: Standard View
Intelligence Value: VERY HIGH - Decision Effectiveness & Committee Specialization
Changelog: v1.35 Politician Decision Pattern Tracking

Purpose

Tracks individual politician decision patterns from DOCUMENT_PROPOSAL_DATA, enabling analysis of politician-level proposal success rates, committee work effectiveness, and legislative productivity. Complements view_riksdagen_party_decision_flow by providing individual politician decision analytics.

Key Columns

Column	Type	Description	Example
`person_id`	VARCHAR(255)	Unique politician identifier	'0532213467925'
`first_name`	VARCHAR(255)	Politician first name	'Anna'
`last_name`	VARCHAR(255)	Politician last name	'Andersson'
`party`	VARCHAR(50)	Party affiliation at decision time	'S'
`committee`	VARCHAR(255)	Committee handling decision	'UU' (Foreign Affairs)
`committee_org`	VARCHAR(20)	Committee organization code	'uu'
`decision_month`	DATE	Month of aggregation (first day)	'2024-10-01'
`decision_year`	INTEGER	Year of decision	2024
`decision_month_num`	INTEGER	Month number (1-12)	10
`total_decisions`	BIGINT	Total decisions in period	45
`approved_decisions`	BIGINT	Decisions approved (bifall)	32
`rejected_decisions`	BIGINT	Decisions rejected (avslag)	8
`referred_back_decisions`	BIGINT	Decisions referred back	3
`other_decisions`	BIGINT	Other decision outcomes	2
`approval_rate`	NUMERIC(5,2)	Percentage approved	71.11
`rejection_rate`	NUMERIC(5,2)	Percentage rejected	17.78
`earliest_decision_date`	DATE	First decision in period	'2024-10-01'
`latest_decision_date`	DATE	Last decision in period	'2024-10-31'

Note: This view aggregates all decision types together. It focuses on politician-committee-month granularity for tracking decision effectiveness.

Example Queries

1. Top 10 Most Effective Politicians by Approval Rate (Last Year)

SELECT 
    first_name,
    last_name,
    party,
    SUM(total_decisions) AS total_decisions,
    ROUND(
        100.0 * SUM(approved_decisions) / NULLIF(SUM(total_decisions), 0), 
        2
    ) AS overall_approval_rate,
    COUNT(DISTINCT committee) AS committees_worked,
    STRING_AGG(DISTINCT committee, ', ' ORDER BY committee) AS committees
FROM view_riksdagen_politician_decision_pattern
WHERE decision_year = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY person_id, first_name, last_name, party
HAVING SUM(total_decisions) >= 5  -- Minimum sample size
ORDER BY overall_approval_rate DESC, total_decisions DESC
LIMIT 10;

2. Committee Specialist Identification

-- Identify politicians with high activity in specific committees
WITH committee_activity AS (
    SELECT
        person_id,
        first_name,
        last_name,
        party,
        committee,
        SUM(total_decisions) AS decisions_in_committee,
        ROUND(AVG(approval_rate), 2) AS avg_approval_rate
    FROM view_riksdagen_politician_decision_pattern
    WHERE decision_year >= EXTRACT(YEAR FROM CURRENT_DATE) - 1
    GROUP BY person_id, first_name, last_name, party, committee
),
total_activity AS (
    SELECT
        person_id,
        SUM(decisions_in_committee) AS total_decisions_all_committees
    FROM committee_activity
    GROUP BY person_id
)
SELECT
    ca.first_name,
    ca.last_name,
    ca.party,
    ca.committee,
    ca.decisions_in_committee,
    ROUND(
        100.0 * ca.decisions_in_committee / ta.total_decisions_all_committees,
        1
    ) AS concentration_pct,
    ca.avg_approval_rate,
    CASE
        WHEN 100.0 * ca.decisions_in_committee / ta.total_decisions_all_committees >= 60 THEN 'SPECIALIST'
        WHEN 100.0 * ca.decisions_in_committee / ta.total_decisions_all_committees >= 40 THEN 'FOCUSED'
        ELSE 'DIVERSIFIED'
    END AS specialization_level
FROM committee_activity ca
JOIN total_activity ta ON ta.person_id = ca.person_id
WHERE ta.total_decisions_all_committees >= 10  -- Minimum activity threshold
ORDER BY concentration_pct DESC
LIMIT 20;

3. Monthly Decision Productivity Trend

SELECT
    person_id,
    first_name,
    last_name,
    party,
    decision_month,
    SUM(total_decisions) AS monthly_decisions,
    ROUND(AVG(approval_rate), 2) AS monthly_approval_rate,
    COUNT(DISTINCT committee) AS committees_active
FROM view_riksdagen_politician_decision_pattern
WHERE decision_year = EXTRACT(YEAR FROM CURRENT_DATE)
    AND person_id = '0532213467925'  -- Specific politician
GROUP BY person_id, first_name, last_name, party, decision_month
ORDER BY decision_month DESC;

4. Ministry Proposal Support Patterns

-- Which politicians are most supportive of ministry proposals
SELECT
    first_name,
    last_name,
    party,
    committee_org AS ministry,
    SUM(total_decisions) AS ministry_decisions,
    ROUND(AVG(approval_rate), 2) AS avg_approval_rate,
    SUM(approved_decisions) AS total_approved,
    SUM(rejected_decisions) AS total_rejected
FROM view_riksdagen_politician_decision_pattern
WHERE decision_year >= EXTRACT(YEAR FROM CURRENT_DATE) - 1
GROUP BY person_id, first_name, last_name, party, committee_org
HAVING SUM(total_decisions) >= 3
ORDER BY avg_approval_rate DESC, ministry_decisions DESC
LIMIT 30;

5. Cross-Party Collaboration on Decisions

-- Identify decisions with broad cross-party approval
WITH decision_aggregates AS (
    SELECT
        committee,
        decision_month,
        COUNT(DISTINCT party) AS parties_involved,
        ROUND(AVG(approval_rate), 2) AS avg_approval_across_parties,
        SUM(total_decisions) AS total_decisions
    FROM view_riksdagen_politician_decision_pattern
    WHERE decision_year = EXTRACT(YEAR FROM CURRENT_DATE)
    GROUP BY committee, decision_month
)
SELECT
    committee,
    decision_month,
    parties_involved,
    avg_approval_across_parties,
    total_decisions,
    CASE
        WHEN parties_involved >= 6 AND avg_approval_across_parties >= 70 THEN 'STRONG_CONSENSUS'
        WHEN parties_involved >= 4 AND avg_approval_across_parties >= 60 THEN 'MODERATE_CONSENSUS'
        ELSE 'LIMITED_CONSENSUS'
    END AS consensus_level
FROM decision_aggregates
WHERE total_decisions >= 5
ORDER BY avg_approval_across_parties DESC, parties_involved DESC
LIMIT 20;

Performance Characteristics

Query Time: 100-200ms (complex aggregation with joins)
Indexes Used: idx_person_ref_person_id, idx_doc_proposal_committee, idx_doc_data_made_public_date
Data Volume: ~50,000 rows (depends on proposal data volume)
Refresh Frequency: Real-time (standard view)
Optimization: Candidate for materialization in future version

Data Sources

Primary Table: document_proposal_data (all proposal decisions)
Joined Tables:
- document_proposal_container (proposal linkage)
- document_status_container (status linkage)
- document_data (dates and metadata)
- document_person_reference_co_0/da_0 (person/party info)
- person_data (politician identification)

Dependencies

No view dependencies (built directly on source tables)
Complements: view_riksdagen_party_decision_flow (party-level view)
Used by: Politician scorecards, committee analysis dashboards

Risk Rules Supported

PoliticianLowProductivity (P-06): Decision count metrics
PoliticianIneffectiveVoting (P-02): Approval rate context
Committee Effectiveness: Specialist identification

Intelligence Applications

This view supports multiple analytical frameworks from DATA_ANALYSIS_INTOP_OSINT.md:

Analysis Framework	Use Case	Example Application	Link
Temporal Analysis	Track decision effectiveness over time	Monitor politician approval rate trends monthly	Framework Docs
Comparative Analysis	Politician vs. party/committee benchmarks	Compare individual approval rates to committee averages	Framework Docs
Pattern Recognition	Committee specialization clustering	Identify specialists vs. generalists	Framework Docs
Predictive Intelligence	Forecast decision success probability	Predict proposal outcomes based on politician history	Framework Docs

Intelligence Products Generated:

🏆 Politician Scorecards - Decision effectiveness metrics
📊 Committee Specialist Reports - Expertise area identification
🎯 Ministry Support Analysis - Government proposal alignment
🤝 Cross-Party Collaboration Tracking - Consensus-building identification

Data Flow: See Intelligence Data Flow Map - View to Analysis Mapping for complete data pipeline.

Intelligence Frameworks Applicable

Productivity Analysis: Decision volume and success metrics
Specialization Analysis: Committee focus and expertise
Comparative Analysis: Individual vs. party/committee benchmarks
Temporal Analysis: Monthly decision patterns and trends

Integration with Product Features

Politician Dashboard (Product Line 1): Decision effectiveness metrics
Committee Analysis (Product Line 2): Specialist identification
Comparative Analytics (Product Line 2): Decision success benchmarking

view_riksdagen_politician_document_daily_summary ⭐⭐⭐⭐

Category: Document Views (v1.24)
Type: Materialized View
Intelligence Value: HIGH - Daily Document Productivity Tracking

Purpose

Daily aggregation of document submissions by politician, document type, and party. Enables temporal productivity analysis and tracks daily legislative activity patterns.

Key Columns

public_date: Document publication date
person_id: Politician identifier
party: Party affiliation at time of submission
document_type: Type of document (motion, interpellation, etc.)
total_documents: Count for that day

Sample Query: Recent Activity

SELECT person_id, first_name, last_name, party, SUM(total_documents) as docs_last_month
FROM view_riksdagen_politician_document_daily_summary
WHERE public_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY person_id, first_name, last_name, party
ORDER BY docs_last_month DESC
LIMIT 20;

Performance Notes

Materialized: Refreshed daily at 02:00 UTC
Query Time: <20ms (indexed)
Use Case: Time-series analysis, daily productivity tracking

view_riksdagen_politician_document_summary ⭐⭐⭐⭐

Category: Document Views (v1.24)
Type: Materialized View
Intelligence Value: HIGH - Career Document Statistics

Purpose

Lifetime document productivity summary per politician, categorizing by motion types (party, individual, committee, multi-party), propositions, and calculating productivity metrics.

Key Metrics

Total Documents: Career document count
Party Motions: Documents as party representative
Individual Motions: Personal legislative proposals
Activity Profile: Classification (Party-focused, Committee-focused, Individual-focused)
Documents Per Year: Average annual productivity

Sample Query: Productivity Profiles

SELECT first_name, last_name, party, total_documents, activity_profile, docs_per_year
FROM view_riksdagen_politician_document_summary
WHERE total_documents >= 50
ORDER BY docs_per_year DESC
LIMIT 30;

Intelligence Applications

Career productivity benchmarking
Legislative style classification
Long-term productivity trends
Politician specialization analysis

Intelligence & Risk Views (Additional Documentation)

view_riksdagen_crisis_resilience_indicators ⭐⭐⭐⭐⭐

Category: Intelligence Views (v1.30)
Type: Standard View
Intelligence Value: VERY HIGH - Crisis Performance Assessment

Purpose

Evaluates politician performance during crisis periods (economic downturns, pandemics, political scandals) by analyzing voting consistency, attendance under pressure, and effectiveness during high-stakes periods.

Key Metrics

Crisis Attendance Rate: Presence during critical periods
Crisis Effectiveness: Win rate during high-stakes votes
Stability Under Pressure: Consistency vs. normal periods
Resilience Classification: HIGHLY_RESILIENT, RESILIENT, LOW_RESILIENCE

Sample Query: Crisis-Ready Politicians

SELECT person_id, first_name, last_name, party, 
       crisis_attendance_rate, crisis_effectiveness, resilience_classification
FROM view_riksdagen_crisis_resilience_indicators
WHERE resilience_classification IN ('HIGHLY_RESILIENT', 'RESILIENT')
ORDER BY crisis_effectiveness DESC
LIMIT 25;

Intelligence Applications

Government crisis team selection
Resilience forecasting
Leadership assessment under stress
Crisis management capability evaluation

view_riksdagen_voting_anomaly_detection ⭐⭐⭐⭐⭐

Category: Intelligence Views (v1.29)
Type: Standard View
Intelligence Value: VERY HIGH - Defection Risk & Anomaly Detection

Purpose

Identifies abnormal voting patterns, defection risks, and party discipline breakdowns using statistical anomaly detection. Flags politicians with unusual vote switching, cross-party alignment, or erratic behavior.

Key Metrics

Anomaly Score: Statistical deviation from expected behavior (0-1 scale)
Defection Risk Assessment: HIGH_DEFECTION_RISK, MODERATE_RISK, LOW_RISK
Party Alignment Deviation: % votes deviating from party norm
Discipline Classification: LOW_DISCIPLINE, MODERATE_DISCIPLINE, HIGH_DISCIPLINE
Recent Anomalies: Count of recent unusual voting patterns

Sample Query: High Defection Risks

SELECT first_name, last_name, party, anomaly_score, 
       defection_risk_assessment, party_alignment_deviation, recent_anomalies
FROM view_riksdagen_voting_anomaly_detection
WHERE defection_risk_assessment = 'HIGH_DEFECTION_RISK'
   OR recent_anomalies >= 3
ORDER BY anomaly_score DESC;

Intelligence Applications

Party defection early warning
Coalition stability monitoring
Swing voter identification
Discipline problem detection

Decision Flow Views (v1.35)

Total Views: 5
Intelligence Value: ⭐⭐⭐⭐⭐ VERY HIGH
Primary Use Cases: Legislative effectiveness tracking, proposal outcome prediction, coalition analysis, government performance monitoring
Changelog: v1.35 Decision Intelligence Layer

Overview

The Decision Flow Views (introduced in v1.35) provide comprehensive analysis of legislative decision patterns, tracking proposal creation, processing, and outcomes across parties, politicians, ministries, and committees. These views enable intelligence assessment of:

Party Legislative Effectiveness: Which parties achieve highest proposal approval rates
Politician Proposal Success: Individual effectiveness in advancing legislative initiatives
Ministry Policy Impact: Government ministry success in implementing policy agenda
Decision Velocity: Legislative processing efficiency and bottleneck identification
Coalition Decision Alignment: Cross-party agreement patterns for stability assessment

View Inventory

View Name	Type	Intelligence Value	Description
view_riksdagen_party_decision_flow	Standard	⭐⭐⭐⭐⭐	Party-level decision approval rates and patterns
view_riksdagen_politician_decision_pattern	Standard	⭐⭐⭐⭐⭐	Individual politician proposal success tracking
view_ministry_decision_impact	Standard	⭐⭐⭐⭐⭐	Ministry legislative effectiveness analysis
view_decision_temporal_trends	Standard	⭐⭐⭐⭐⭐	Time-series decision patterns with moving averages
view_decision_outcome_kpi_dashboard	Standard	⭐⭐⭐⭐⭐	Consolidated decision KPIs across all dimensions

view_riksdagen_party_decision_flow ⭐⭐⭐⭐⭐

Category: Decision Intelligence (v1.35)
Type: Standard View
Intelligence Value: VERY HIGH - Party Legislative Effectiveness

Purpose

Tracks party-level proposal decision patterns from DOCUMENT_PROPOSAL_DATA, aggregating monthly approval rates, rejection rates, and committee activity. Enables comparative analysis of party legislative effectiveness and coalition decision alignment.

Key Metrics

Approval Rate: Percentage of party proposals approved (bifall) vs. rejected (avslag)
Decision Volume: Total decisions per party per month/year
Committee Breadth: Number of distinct committees where party is active
Temporal Trends: Month-over-month approval rate changes
Coalition Alignment: Cross-party decision agreement patterns

Schema

Column Name	Type	Description	Example
`party`	VARCHAR(50)	Political party code	'S' (Social Democrats)
`committee`	VARCHAR(255)	Committee handling decisions	'UU' (Foreign Affairs)
`decision_month`	DATE	Month of aggregation (first day)	'2024-10-01'
`decision_year`	INTEGER	Year of decision	2024
`decision_month_num`	INTEGER	Month number (1-12)	10
`total_decisions`	BIGINT	Total decisions in period	45
`approved_decisions`	BIGINT	Decisions approved (bifall)	32
`rejected_decisions`	BIGINT	Decisions rejected (avslag)	8
`other_decisions`	BIGINT	Other decision outcomes	5
`approval_rate`	NUMERIC	Approval percentage	71.11

Sample Query: Party Effectiveness Ranking (Current Year)

SELECT 
    party,
    COUNT(*) AS total_months,
    ROUND(AVG(approval_rate), 2) AS avg_approval_rate,
    COUNT(DISTINCT committee) AS committees_active,
    SUM(total_decisions) AS total_decisions,
    RANK() OVER (ORDER BY AVG(approval_rate) DESC) AS effectiveness_rank
FROM view_riksdagen_party_decision_flow
WHERE decision_year = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY party
ORDER BY avg_approval_rate DESC;

Intelligence Applications

Coalition Formation Analysis: Identify party combinations with highest joint approval rates
Opposition Effectiveness: Measure which opposition parties achieve legislative success
Government Strength: Track ruling coalition proposal success over time
Committee Specialization: Determine which parties dominate specific policy areas
Trend Detection: Early warning for declining party legislative effectiveness

D-01: Party Low Approval Rate - Triggers when party approval rate <30% for 3+ months
D-05: Coalition Decision Misalignment - Detects coalition partner alignment <60%

Cross-References

view_riksdagen_politician_decision_pattern ⭐⭐⭐⭐⭐

Category: Decision Intelligence (v1.35)
Type: Standard View
Intelligence Value: VERY HIGH - Decision Effectiveness & Committee Specialization

Purpose

Key Metrics

Individual Approval Rate: Politician's proposal success rate
Committee Specialization: Primary committees where politician is most active
Legislative Productivity: Total proposals submitted per period
Career Trajectory: Approval rate trends over time (improving vs. declining)
Cross-Party Influence: Success rate compared to party average

Schema

Column Name	Type	Description	Example
`person_id`	VARCHAR(255)	Unique politician identifier	'0279865129018'
`first_name`	VARCHAR(255)	Politician first name	'Magdalena'
`last_name`	VARCHAR(255)	Politician last name	'Andersson'
`party`	VARCHAR(50)	Party affiliation at decision time	'S'
`committee`	VARCHAR(255)	Committee handling decision	'UU' (Foreign Affairs)
`decision_month`	DATE	Month of aggregation (first day)	'2024-10-01'
`decision_year`	INTEGER	Year of decision	2024
`decision_month_num`	INTEGER	Month number (1-12)	10
`total_decisions`	BIGINT	Total decisions in period	45
`approved_decisions`	BIGINT	Decisions approved (bifall)	32
`rejected_decisions`	BIGINT	Decisions rejected (avslag)	8
`other_decisions`	BIGINT	Other decision outcomes	5
`approval_rate`	NUMERIC	Approval percentage	71.11

Sample Query: Top Performing Politicians (Minimum 10 Proposals)

SELECT 
    person_id,
    first_name,
    last_name,
    party,
    COUNT(DISTINCT committee) AS committees_active,
    SUM(total_decisions) AS total_proposals,
    ROUND(AVG(approval_rate), 2) AS avg_approval_rate,
    RANK() OVER (ORDER BY AVG(approval_rate) DESC) AS effectiveness_rank
FROM view_riksdagen_politician_decision_pattern
WHERE decision_year = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY person_id, first_name, last_name, party
HAVING SUM(total_decisions) >= 10
ORDER BY avg_approval_rate DESC
LIMIT 50;

Intelligence Applications

Rising Political Stars: Identify high-performing politicians for advancement
Committee Chair Effectiveness: Assess committee leadership performance
Shadow Cabinet Analysis: Evaluate opposition leadership proposal success
Legislative Mentorship: Target low-performing members for support
Resignation Prediction: Detect declining effectiveness patterns (early warning)

D-02: Politician Proposal Ineffectiveness - Triggers when approval rate <20% with 10+ proposals

Cross-References

view_ministry_decision_impact ⭐⭐⭐⭐⭐

Category: Decision Intelligence (v1.35)
Type: Standard View
Intelligence Value: VERY HIGH - Government Policy Effectiveness & Coalition Stability

Purpose

Evaluates ministry-level legislative performance by tracking government proposal outcomes, approval rates, and processing times. Critical for assessing executive branch effectiveness, minister performance, and coalition stability.

⚠️ Data Quality Note: This view was affected by missing pattern matching for committee referrals.
Issue: Decision chamber pattern matching - 7,049 records were misclassified as "other"
Status: ✅ Fixed in Liquibase changeset db-changelog-1.45.xml (added committee referral tracking)
Details: See Data Quality Analysis in README-SCHEMA-MAINTENANCE.md

Key Metrics

Ministry Approval Rate: Success rate of ministry proposals
Proposal Volume: Legislative activity by ministry
Processing Efficiency: Average days from proposal to decision
Quarterly Performance: Trend analysis for ministry effectiveness
Policy Domain Success: Which policy areas achieve highest approval

Schema

Column Name	Type	Description	Example
`ministry_code`	VARCHAR(10)	Ministry abbreviation	'FI' (Finance)
`ministry_name`	VARCHAR(255)	Full ministry name	'Finance Ministry'
`decision_year`	INTEGER	Year of decision	2024
`decision_quarter`	INTEGER	Quarter (1-4)	3
`total_proposals`	BIGINT	Total ministry proposals	78
`approved_proposals`	BIGINT	Approved proposals	65
`rejected_proposals`	BIGINT	Rejected proposals	8
`other_outcomes`	BIGINT	Other decision outcomes	5
`approval_rate`	NUMERIC	Approval percentage	83.33
`avg_processing_days`	NUMERIC	Average days to decision	45.2

Sample Query: Ministry Performance Ranking (Last 4 Quarters)

SELECT 
    ministry_code,
    ministry_name,
    decision_year,
    decision_quarter,
    total_proposals,
    approved_proposals,
    ROUND(approval_rate, 2) AS approval_rate,
    ROUND(avg_processing_days, 1) AS avg_processing_days,
    CASE 
        WHEN approval_rate >= 85 THEN '🟢 Excellent'
        WHEN approval_rate >= 70 THEN '🟡 Good'
        WHEN approval_rate >= 50 THEN '🟠 Moderate'
        ELSE '🔴 Concerning'
    END AS performance_rating
FROM view_ministry_decision_impact
WHERE decision_year >= EXTRACT(YEAR FROM CURRENT_DATE) - 1
ORDER BY decision_year DESC, decision_quarter DESC, approval_rate DESC;

Intelligence Applications

Minister Performance Evaluation: Objective assessment of minister effectiveness
Coalition Friction Detection: Low approval rates indicate coalition disagreement
Budget Effectiveness: Track which ministries deliver on policy promises
Government Reshuffle: Identify underperforming ministries for cabinet changes
Policy Priority Tracking: Monitor success of government's top policy areas

D-03: Ministry Declining Success Rate - Triggers when approval rate declines >20% QoQ

Cross-References

view_decision_temporal_trends ⭐⭐⭐⭐⭐

Category: Decision Intelligence (v1.35)
Type: Standard View
Intelligence Value: VERY HIGH - Temporal Decision Pattern Analysis

Purpose

Provides time-series analysis of legislative decision patterns with moving averages for trend detection. Enables identification of seasonal patterns, decision volume anomalies, and processing efficiency trends.

⚠️ Data Quality Note: This view was affected by missing pattern matching for committee referrals.
Issue: Decision chamber pattern matching - 7,049 records were misclassified as "other"
Status: ✅ Fixed in Liquibase changeset db-changelog-1.45.xml (added committee referral tracking)
Details: See Data Quality Analysis in README-SCHEMA-MAINTENANCE.md

Key Metrics

Daily Decision Volume: Number of decisions per day
Moving Averages: 7-day and 30-day smoothed trends
Volume Anomalies: Statistically significant spikes/drops
Seasonal Patterns: Day-of-week and month effects
Processing Momentum: Acceleration/deceleration in legislative activity

Schema

Column Name	Type	Description	Example
`decision_day`	DATE	Date of decisions	'2024-10-15'
`daily_decisions`	BIGINT	Total decisions on date	127
`moving_avg_7d`	NUMERIC	7-day moving average	98.43
`moving_avg_30d`	NUMERIC	30-day moving average	85.67
`day_of_week`	INTEGER	Day of week (0=Sunday)	2 (Tuesday)
`week_of_year`	INTEGER	Week number	42
`month`	INTEGER	Month number	10

Sample Query: Decision Volume Anomaly Detection (Z-Score > 2)

WITH volume_stats AS (
    SELECT 
        AVG(daily_decisions) AS avg_volume,
        STDDEV(daily_decisions) AS stddev_volume
    FROM view_decision_temporal_trends
    WHERE decision_day >= CURRENT_DATE - INTERVAL '90 days'
),
anomaly_calc AS (
    SELECT 
        vdt.decision_day,
        vdt.daily_decisions,
        vdt.moving_avg_7d,
        ROUND(vs.avg_volume, 2) AS baseline_avg,
        ROUND(COALESCE((vdt.daily_decisions - vs.avg_volume) / NULLIF(vs.stddev_volume, 0), 0), 2) AS z_score
    FROM view_decision_temporal_trends vdt
    CROSS JOIN volume_stats vs
    WHERE vdt.decision_day >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT 
    decision_day,
    daily_decisions,
    moving_avg_7d,
    baseline_avg,
    z_score,
    CASE 
        WHEN z_score > 2 THEN '⚠️ HIGH ANOMALY'
        WHEN z_score < -2 THEN '⚠️ LOW ANOMALY'
        ELSE '✅ Normal'
    END AS anomaly_status
FROM anomaly_calc
ORDER BY ABS(z_score) DESC;

Intelligence Applications

Crisis Response Monitoring: Detect emergency legislative activity spikes
Process Bottleneck Detection: Identify unusual decision processing delays
Seasonal Planning: Resource allocation based on historical patterns
Media Monitoring: Validate claims of legislative "gridlock" or "rush"
Pre-Recess Activity: Track decision surge before parliamentary breaks

D-04: Decision Volume Anomaly - Triggers when z-score > 2 or < -2

Cross-References

Decision Flow Views: Common Usage Patterns

Pattern 1: Coalition Stability Assessment

Combine party decision alignment with ministry approval rates to assess coalition health.

-- Coalition Stability Score (0-100)
SELECT 
    'Coalition Stability' AS metric,
    ROUND(AVG(pdf.approval_rate) * 0.6 + AVG(mdi.approval_rate) * 0.4, 2) AS stability_score
FROM view_riksdagen_party_decision_flow pdf
CROSS JOIN view_ministry_decision_impact mdi
WHERE pdf.party IN ('S', 'C', 'V', 'MP')  -- Current coalition
  AND pdf.decision_year = EXTRACT(YEAR FROM CURRENT_DATE)
  AND mdi.decision_year = EXTRACT(YEAR FROM CURRENT_DATE);

Pattern 2: Politician Career Trajectory Analysis

Track individual politician approval rate trends to predict career advancement or decline.

-- Politician Career Momentum (Improving vs. Declining)
WITH quarterly_performance AS (
    SELECT 
        person_id,
        first_name,
        last_name,
        decision_year,
        EXTRACT(QUARTER FROM decision_month) AS quarter,
        AVG(approval_rate) AS quarterly_approval_rate
    FROM view_riksdagen_politician_decision_pattern
    WHERE decision_year >= EXTRACT(YEAR FROM CURRENT_DATE) - 1
    GROUP BY person_id, first_name, last_name, decision_year, quarter
)
SELECT 
    person_id,
    first_name,
    last_name,
    COUNT(*) AS quarters_tracked,
    ROUND(AVG(quarterly_approval_rate), 2) AS avg_approval_rate,
    ROUND(REGR_SLOPE(quarterly_approval_rate, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY decision_year, quarter))::NUMERIC, 4) AS trend_slope,
    CASE 
        WHEN REGR_SLOPE(quarterly_approval_rate, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY decision_year, quarter)) > 2 THEN '📈 Rising Star'
        WHEN REGR_SLOPE(quarterly_approval_rate, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY decision_year, quarter)) < -2 THEN '📉 Declining'
        ELSE '➡️ Stable'
    END AS career_trajectory
FROM quarterly_performance
GROUP BY person_id, first_name, last_name
HAVING COUNT(*) >= 4
ORDER BY trend_slope DESC;

Pattern 3: Ministry Performance Benchmarking

Compare ministry approval rates to identify high/low performers for cabinet assessment.

-- Ministry Performance Scorecard with Peer Comparison
WITH ministry_stats AS (
    SELECT 
        AVG(approval_rate) AS avg_approval_rate,
        STDDEV(approval_rate) AS stddev_approval_rate
    FROM view_ministry_decision_impact
    WHERE decision_year = EXTRACT(YEAR FROM CURRENT_DATE)
)
SELECT 
    mdi.ministry_code,
    mdi.ministry_name,
    ROUND(mdi.approval_rate, 2) AS approval_rate,
    ROUND(ms.avg_approval_rate, 2) AS ministry_average,
    ROUND((mdi.approval_rate - ms.avg_approval_rate) / NULLIF(ms.stddev_approval_rate, 0), 2) AS z_score,
    CASE 
        WHEN (mdi.approval_rate - ms.avg_approval_rate) / NULLIF(ms.stddev_approval_rate, 0) > 1 THEN '⭐⭐⭐⭐⭐ Excellent'
        WHEN (mdi.approval_rate - ms.avg_approval_rate) / NULLIF(ms.stddev_approval_rate, 0) > 0 THEN '⭐⭐⭐⭐ Above Average'
        WHEN (mdi.approval_rate - ms.avg_approval_rate) / NULLIF(ms.stddev_approval_rate, 0) > -1 THEN '⭐⭐⭐ Average'
        ELSE '⭐⭐ Below Average'
    END AS performance_tier
FROM view_ministry_decision_impact mdi
CROSS JOIN ministry_stats ms
WHERE mdi.decision_year = EXTRACT(YEAR FROM CURRENT_DATE)
ORDER BY z_score DESC;

Ministry & Government Views (Complete Documentation)

view_ministry_effectiveness_trends ⭐⭐⭐⭐⭐

Category: Ministry Performance (v1.29)
Type: Standard View
Intelligence Value: VERY HIGH - Government Executive Performance

Purpose

Tracks quarterly ministry productivity including legislative output (propositions, government bills), staffing levels, and performance trends. Identifies declining ministries requiring intervention.

Key Metrics

Documents Produced: Quarterly document output
Legislative Documents: Props + government bills count
Active Members: Ministry staff count
Documents Per Member: Productivity ratio
Productivity Level: HIGHLY_PRODUCTIVE, PRODUCTIVE, MODERATE, LOW
Stagnation Indicator: SEVERE_DECLINE, IMPROVING, STABLE
Effectiveness Assessment: Narrative performance evaluation

Sample Query: Ministry Performance Dashboard

SELECT name, period_start, documents_produced, legislative_documents,
       active_members, documents_per_member, productivity_level, effectiveness_assessment
FROM view_ministry_effectiveness_trends
WHERE period_start >= DATE_TRUNC('year', CURRENT_DATE)
ORDER BY documents_produced DESC;

Intelligence Applications

Government productivity monitoring
Ministry intervention prioritization
Executive performance scorecards
Cabinet effectiveness evaluation

view_ministry_productivity_matrix ⭐⭐⭐⭐⭐

Category: Ministry Performance (v1.29)
Type: Standard View
Intelligence Value: VERY HIGH - Comparative Ministry Analysis

Purpose

Matrix comparison of all ministries across productivity metrics, enabling cross-ministry benchmarking and relative performance assessment.

Key Metrics

Relative Productivity: Ministry rank by output
Productivity Percentile: Position in distribution
Benchmark Comparison: vs. ministry average
Efficiency Rating: Output per staff member
Performance Tier: TIER_1 (top 25%), TIER_2, TIER_3, TIER_4 (bottom 25%)

Sample Query: Ministry Rankings

SELECT ministry_name, total_documents_ytd, staff_count, 
       efficiency_rating, performance_tier, productivity_percentile
FROM view_ministry_productivity_matrix
ORDER BY productivity_percentile DESC;

Intelligence Applications

Ministry benchmarking
Resource allocation decisions
Performance tier identification
Cabinet reshuffle intelligence

view_ministry_risk_evolution ⭐⭐⭐⭐⭐

Category: Ministry Risk (v1.30)
Type: Standard View
Intelligence Value: VERY HIGH - Ministry Risk Tracking

Purpose

Temporal tracking of ministry risk indicators including output decline, understaffing, legislative inactivity, and trend deterioration. Early warning system for ministry performance problems.

Key Metrics

Risk Score: Aggregate ministry risk points
Risk Severity: CRITICAL, MAJOR, MINOR
Output Risk: Document production concerns
Staffing Risk: Understaffing indicators
Trend Risk: Negative performance trajectory
Risk Trajectory: ESCALATING, STABLE, IMPROVING

Sample Query: High-Risk Ministries

SELECT ministry_name, quarter, risk_score, risk_severity,
       output_risk, staffing_risk, trend_risk, risk_trajectory
FROM view_ministry_risk_evolution
WHERE risk_severity IN ('CRITICAL', 'MAJOR')
   OR risk_trajectory = 'ESCALATING'
ORDER BY risk_score DESC;

Intelligence Applications

Ministry crisis detection
Intervention prioritization
Government stability assessment
Cabinet risk monitoring

view_riksdagen_goverment ⭐⭐⭐⭐

Category: Government Structure (v1.1)
Type: Standard View
Intelligence Value: HIGH - Government Composition Tracking

Purpose

Tracks current government structure, cabinet composition, party representation in government, and government formation dates. Core reference for executive branch analysis.

Key Columns

government_id: Unique government identifier
formed_date: Government formation date
dissolved_date: End date (NULL if current)
prime_minister: PM name
coalition_parties: Parties in government (array)
cabinet_size: Number of ministers
majority_status: MAJORITY, MINORITY, COALITION

Sample Query: Current Government

SELECT *
FROM view_riksdagen_goverment
WHERE dissolved_date IS NULL;

view_riksdagen_goverment_proposals ⭐⭐⭐⭐

Category: Government Activity (v1.23)
Type: Standard View
Intelligence Value: HIGH - Executive Legislative Initiative

Purpose

Tracks government legislative proposals (propositions), passage rates, committee assignments, and approval status. Measures government legislative effectiveness.

Key Metrics

Total Proposals: Government-initiated legislation count
Approved Proposals: Passed by parliament
Pending Proposals: Under consideration
Rejection Rate: Failed proposals percentage
Average Processing Time: Days from submission to decision

Sample Query: Recent Government Legislation

SELECT proposal_id, title, submitted_date, status, committee_assigned
FROM view_riksdagen_goverment_proposals
WHERE submitted_date >= CURRENT_DATE - INTERVAL '6 months'
ORDER BY submitted_date DESC;

view_riksdagen_goverment_role_member ⭐⭐⭐⭐

Category: Government Membership (v1.1)
Type: Standard View
Intelligence Value: HIGH - Cabinet Personnel Tracking

Purpose

Maps individual politicians to government roles (minister positions), tracking appointment/dismissal dates, role durations, and government turnover.

Key Columns

person_id: Minister identifier
role_code: Ministry/position code
role_title: Minister title (e.g., "Utbildningsminister")
from_date: Appointment date
to_date: End date (NULL if current)
days_in_role: Duration of appointment

Sample Query: Current Cabinet

SELECT first_name, last_name, party, role_title, from_date, days_in_role
FROM view_riksdagen_goverment_role_member
WHERE to_date IS NULL
ORDER BY role_title;

view_riksdagen_goverment_roles ⭐⭐⭐

Category: Government Structure (v1.1)
Type: Standard View
Intelligence Value: MEDIUM - Government Role Definitions

Purpose

Catalog of government roles, ministries, and position definitions. Reference data for government structure analysis.

Key Columns

role_code: Unique role identifier
role_title: Official role name
ministry_category: Ministry grouping
role_level: CABINET, STATE_SECRETARY, etc.

Purpose

Aggregates ministry/government department data from assignment_data, providing ministry-level statistics including total assignments, member counts, activity periods, and current operational status. Serves as the foundational reference for ministry-level comparative analysis and government structure tracking.

Key Columns

Column	Type	Description	Example
`ministry_id`	VARCHAR(50)	Ministry organization code	'U', 'Ju', 'Fi', 'Fö'
`ministry_name`	VARCHAR(255)	Official ministry department name	'Utrikesdepartementet'
`role_code`	VARCHAR(50)	Government role code filter	'MINISTER', 'STATSSEKRETERARE'
`total_assignments`	INTEGER	Total role assignments to ministry	47
`total_members`	INTEGER	Unique individuals assigned (COUNT DISTINCT)	23
`first_assignment_date`	DATE	Earliest assignment date	'1995-01-01'
`last_assignment_date`	DATE	Most recent assignment date	'2024-12-31'
`is_active`	BOOLEAN	Ministry currently operational (to_date >= CURRENT_DATE or NULL)	TRUE

Swedish Government Ministries

Code	Ministry Name (Swedish)	English Name	Typical Cabinet Size
Ju	Justitiedepartementet	Ministry of Justice	1-2 ministers
U	Utrikesdepartementet	Ministry of Foreign Affairs	1-2 ministers
Fö	Försvarsdepartementet	Ministry of Defence	1 minister
Fi	Finansdepartementet	Ministry of Finance	1-2 ministers
U	Utbildningsdepartementet	Ministry of Education	1-2 ministers
S	Socialdepartementet	Ministry of Health and Social Affairs	1-3 ministers

Note: Statsrådsberedningen (Prime Minister's Office) is also tracked as a special ministry entity.

Example Queries

1. Current Active Ministries

SELECT
    ministry_id,
    ministry_name,
    total_members,
    total_assignments,
    first_assignment_date,
    last_assignment_date
FROM view_riksdagen_ministry
WHERE is_active = TRUE
ORDER BY total_members DESC;

Output:

 ministry_id | ministry_name           | total_members | total_assignments | first_assignment_date | last_assignment_date
-------------+-------------------------+---------------+-------------------+----------------------+---------------------
 Fi          | Finansdepartementet     |            23 |                47 | 1995-01-01           | 2024-12-31
 Ju          | Justitiedepartementet   |            19 |                38 | 1996-03-15           | 2024-11-30
 U           | Utrikesdepartementet    |            18 |                35 | 1995-02-20           | 2024-10-15

2. Ministry Longevity Analysis

SELECT
    ministry_id,
    ministry_name,
    EXTRACT(YEAR FROM AGE(last_assignment_date, first_assignment_date)) AS years_active,
    total_members,
    ROUND(total_assignments::NUMERIC / total_members, 2) AS avg_assignments_per_person
FROM view_riksdagen_ministry
WHERE is_active = TRUE
ORDER BY years_active DESC;

3. Ministry Activity Timeline

SELECT
    ministry_name,
    first_assignment_date,
    last_assignment_date,
    is_active,
    total_members,
    CASE
        WHEN is_active THEN 'Currently operational'
        ELSE 'Historical ministry'
    END AS status
FROM view_riksdagen_ministry
ORDER BY first_assignment_date;

Intelligence Applications

Government Restructuring Analysis:

Track ministry formation and dissolution patterns
Identify organizational changes in government structure
Monitor ministry expansion/contraction over time

Cabinet Stability Assessment:

Compare member turnover rates across ministries
Identify high-churn ministries (political instability indicator)
Track long-term vs. short-term ministerial appointments

Comparative Ministry Analysis:

Compare ministry sizes across government periods
Analyze resource allocation patterns (member counts)
Identify dominant vs. peripheral ministries

Dependencies

Source Data:

assignment_data - Raw assignment records filtered for ministry roles

Related Views:

view_riksdagen_goverment_roles - Individual role definitions
view_riksdagen_goverment_role_member - Person-to-ministry mappings
view_ministry_decision_impact - Ministry legislative effectiveness

Performance Characteristics

Query Complexity: Medium (GROUP BY aggregation)
Data Volume: Low (< 100 rows - one per ministry/role combination)
Refresh Frequency: Static (updated when assignment_data changes)
Index Recommendations: None required (small result set)

Usage Notes

Filter Criteria: The view includes assignments where:

role_code contains 'MINISTER' (e.g., MINISTER, UTRIKESMINISTER)
detail contains 'departementet' (Swedish for "department")
detail equals 'Statsrådsberedningen' (Prime Minister's Office)

Active Status Logic: Ministry considered active if:

last_assignment_date >= CURRENT_DATE (has recent assignments)
last_assignment_date IS NULL (ongoing assignments)

view_ministry_decision_impact ⭐⭐⭐⭐⭐

Category: Ministry Decision Analysis (v1.35)
Type: Standard View
Intelligence Value: VERY HIGH - Government Policy Effectiveness & Coalition Stability

Purpose

Tracks ministry-initiated proposal outcomes from DOCUMENT_PROPOSAL_DATA, enabling analysis of which government ministries have the highest/lowest success rates for their legislative proposals. Provides comprehensive insight into government policy effectiveness, ministry-parliament relations, and coalition stability indicators.

Key Metrics

ministry_code: Government ministry/department identifier (org)
committee: Parliamentary committee handling the proposal
decision_type: Type of decision rendered
decision_quarter: Quarter when decision was made (aggregated)
decision_year: Year of decision
quarter_num: Quarter number (1-4) for sorting and filtering
total_proposals: Total ministry proposals in period
approved_proposals: Count of approved proposals (bifall, godkänt, bifalla)
rejected_proposals: Count of rejected proposals (avslag, avslå)
referred_back_proposals: Count of proposals referred back to committee (återförvisning, återförvisa)
other_decisions: Count of other decisions (not approved/rejected/referred back)
approval_rate: Percentage of proposals approved (%)
rejection_rate: Percentage of proposals rejected (%)
earliest_proposal_date: Start of period
latest_proposal_date: End of period

Sample Query: Ministry Success Rates (Last 2 Years)

-- Ministry proposal success rates comparison
SELECT ministry_code,
       SUM(total_proposals) AS total,
       ROUND(AVG(approval_rate), 2) AS avg_approval_rate,
       ROUND(AVG(rejection_rate), 2) AS avg_rejection_rate,
       COUNT(DISTINCT committee) AS committees_worked_with
FROM view_ministry_decision_impact
WHERE decision_year >= EXTRACT(YEAR FROM CURRENT_DATE) - 1
GROUP BY ministry_code
ORDER BY avg_approval_rate DESC;

Sample Query: Quarterly Ministry Performance Trends

-- Track ministry performance trends over time
SELECT ministry_code, decision_year, quarter_num,
       total_proposals, approval_rate, rejection_rate
FROM view_ministry_decision_impact
WHERE ministry_code = 'U'  -- Example: Ministry of Foreign Affairs
  AND decision_year >= 2020
ORDER BY decision_year DESC, quarter_num DESC;

Sample Query: Committee-Ministry Relationship Analysis

-- Which committees approve ministry proposals most frequently
SELECT committee, ministry_code,
       SUM(total_proposals) AS proposals,
       ROUND(AVG(approval_rate), 2) AS avg_approval
FROM view_ministry_decision_impact
WHERE decision_year >= EXTRACT(YEAR FROM CURRENT_DATE) - 2
GROUP BY committee, ministry_code
HAVING SUM(total_proposals) >= 5  -- At least 5 proposals
ORDER BY avg_approval DESC;

Intelligence Applications

Government Policy Effectiveness:
- Identify ministries with low approval rates indicating policy effectiveness issues
- Track which ministries have strongest parliamentary support
- Monitor government legislative success over time
Coalition Stability Analysis:
- Low ministry approval rates may signal coalition tensions
- Track whether government proposals face increasing rejection
- Identify at-risk ministries with declining success rates
Committee-Ministry Relations:
- Analyze which committees are most supportive/critical of specific ministries
- Identify bottlenecks where ministry proposals stall
- Map institutional relationships between executive and legislative branches
Temporal Trend Analysis:
- Detect declining ministry effectiveness over quarters/years
- Forecast future approval rates based on historical patterns
- Identify seasonal variations in ministry legislative success
Policy Domain Success Patterns:
- Compare success rates across different policy domains (ministries)
- Identify high-performing vs. struggling ministries
- Resource allocation and capacity assessment

view_riksdagen_party_decision_flow (Issue Hack23/cia#7918) - Party-level decision patterns
view_riksdagen_politician_decision_pattern (Issue Hack23/cia#7919) - Individual politician decisions
view_decision_temporal_trends (Issue Hack23/cia#7922) - Temporal decision analysis
view_ministry_effectiveness_trends - Broader ministry performance metrics
view_riksdagen_goverment_proposals - Government legislative proposals tracking

Complementary Analysis

This view complements the existing ministry effectiveness views by adding decision outcome tracking:

view_ministry_effectiveness_trends: Tracks document productivity
view_ministry_productivity_matrix: Compares ministry output
view_ministry_risk_evolution: Monitors ministry risk indicators
view_ministry_decision_impact: Adds proposal success/failure outcomes ← NEW

Together, these views provide comprehensive government performance intelligence spanning productivity, risk, and legislative effectiveness.

view_riksdagen_politician_career_trajectory ⭐⭐⭐⭐⭐

Category: Politician Intelligence Views (v1.55)
Type: Standard View
Intelligence Value: VERY HIGH - Career Pattern Detection & Predictive Analytics
Changelog: v1.55 Career Trajectory Tracking

Purpose

Tracks individual politician performance evolution across election cycles (2002-2026+), enabling career pattern detection, performance trend analysis, and resignation risk prediction. Provides META/META-level historical analysis with election cycle context for comprehensive career trajectory understanding.

Key Columns

Column	Type	Description	Example
`person_id`	VARCHAR(255)	Unique politician identifier	'0532213467925'
`first_name`	VARCHAR(255)	Politician first name	'Anna'
`last_name`	VARCHAR(255)	Politician last name	'Kinberg Batra'
`party`	VARCHAR(50)	Current party affiliation	'M'
`election_year`	INTEGER	Election cycle year (2002-2026)	2018
`career_cycle_number`	INTEGER	Sequential cycle in career	3
`total_cycles`	BIGINT	Total election cycles active	5
`career_start_year`	INTEGER	First election cycle	2006
`career_end_year`	INTEGER	Latest election cycle	2022
`ballot_count`	BIGINT	Votes cast in this cycle	847
`attendance_rate`	NUMERIC	Attendance % (vs absences)	94.3
`win_rate`	NUMERIC	Win % on party alignment votes	87.2
`leadership_roles`	BIGINT	Leadership positions held	3
`documents_authored`	BIGINT	Documents authored	42
`avg_career_attendance`	NUMERIC	Career average attendance	91.5
`performance_vs_baseline`	NUMERIC	Current vs career average	+2.8
`career_stage`	VARCHAR(50)	'EARLY_CAREER', 'MID_CAREER', 'LATE_CAREER'	'MID_CAREER'
`performance_trend`	VARCHAR(50)	'IMPROVING', 'DECLINING', 'STABLE', 'NEW_ENTRY'	'IMPROVING'
`career_pattern`	VARCHAR(50)	Overall pattern classification	'RISING_STAR'

Career Pattern Classification

Pattern	Criteria	Intelligence Value
PEAK_PERFORMANCE	High attendance (>90%), stable trend, mid-late career	Retention target, mentorship candidate
RISING_STAR	Improving trend, early-mid career, above baseline	Promotion candidate, future leader
LATE_CAREER_DECLINE	Declining trend, late career, below baseline	Retirement risk, succession planning
STRUGGLING_NEWCOMER	Low attendance (<70%), new entry, first cycle	Support needed, attrition risk
CONSISTENT	Stable trend, near baseline, multiple cycles	Reliable performer, backbone

Sample Queries

1. Identify Rising Stars (Early Career High Performers)

SELECT 
    first_name,
    last_name,
    party,
    career_stage,
    performance_trend,
    career_pattern,
    attendance_rate,
    performance_vs_baseline
FROM view_riksdagen_politician_career_trajectory
WHERE career_pattern = 'RISING_STAR'
    AND election_year = 2022
ORDER BY performance_vs_baseline DESC
LIMIT 20;

2. Track Individual Career Evolution

SELECT 
    election_year,
    career_cycle_number,
    attendance_rate,
    win_rate,
    leadership_roles,
    performance_trend,
    career_pattern
FROM view_riksdagen_politician_career_trajectory
WHERE person_id = '0532213467925'
ORDER BY election_year;

3. Retirement Risk Analysis (Late Career Decline)

SELECT 
    first_name,
    last_name,
    party,
    career_start_year,
    total_cycles,
    attendance_rate,
    performance_vs_baseline,
    career_pattern
FROM view_riksdagen_politician_career_trajectory
WHERE career_pattern = 'LATE_CAREER_DECLINE'
    AND election_year = 2022
    AND total_cycles >= 4
ORDER BY attendance_rate ASC;

Intelligence Applications

Predictive Intelligence (Framework 4): Resignation risk prediction based on declining trends
Talent Management: Identify rising stars and succession planning candidates
Performance Analysis: Compare early career vs late career effectiveness
Party Strategy: Assess member retention risks and renewal needs
Coalition Analysis: Track influential politician trajectory changes

Integration Points

Feeds into Predictive Intelligence Framework (Framework 4) for career forecasting
Complements view_riksdagen_politician_longevity_analysis for retention risk assessment
Links to view_riksdagen_politician_role_evolution for career progression patterns
Used by view_politician_risk_summary for comprehensive risk scoring

view_riksdagen_politician_role_evolution ⭐⭐⭐⭐⭐

Category: Politician Intelligence Views (v1.55)
Type: Standard View
Intelligence Value: VERY HIGH - Career Progression & Advancement Tracking
Changelog: v1.55 Role Evolution Tracking

Purpose

Tracks politician role progression through the political hierarchy, measuring advancement velocity between consecutive roles, and identifying career peaks and progression patterns (backbencher → committee → chair → minister). Critical for understanding power structure evolution and identifying fast-track careers.

Key Columns

Column	Type	Description	Example
`person_id`	VARCHAR(255)	Unique politician identifier	'0532213467925'
`first_name`	VARCHAR(255)	Politician first name	'Ebba'
`last_name`	VARCHAR(255)	Politician last name	'Busch'
`party`	VARCHAR(50)	Party affiliation	'KD'
`role_code`	VARCHAR(255)	Role identifier	'Partiledare'
`role_tier`	VARCHAR(50)	Role classification	'PARTY_LEADER'
`role_weight`	INTEGER	Role importance score (50-1000)	900
`role_start`	DATE	Role start date	2015-04-25
`role_end`	DATE	Role end date	2022-09-19
`role_start_year`	INTEGER	Start year	2015
`role_end_year`	INTEGER	End year	2022
`role_instances`	BIGINT	Times held this role	1
`total_days_in_role`	INTEGER	Total days in role	2704
`years_in_role`	INTEGER	Total years	7
`is_current_role`	BOOLEAN	Currently active	FALSE
`role_sequence`	BIGINT	Role order in career	3
`peak_role_weight`	INTEGER	Highest role achieved	900
`career_first_year`	INTEGER	Career start	2002
`career_last_year`	INTEGER	Career end/current	2022
`progression_pattern`	VARCHAR(50)	Career trajectory	'CAREER_PEAK'
`career_level`	VARCHAR(50)	Overall achievement level	'TOP_LEADERSHIP'
`advancement_velocity`	NUMERIC	Role weight increase per year	42.50

Role Tier Hierarchy & Weights

Tier	Weight	Roles	Example
MINISTER	1000	Government ministers, Prime Minister	'Statsminister', 'Finansminister'
SPEAKER	800	Parliament Speaker, Vice Speakers	'Talman', 'Förste vice talman'
PARTY_LEADER	900	Party leaders	'Partiledare'
COMMITTEE_CHAIR	600	Committee chairs	'Ordförande'
COMMITTEE_VICE_CHAIR	500	Committee vice chairs	'Vice ordförande'
COMMITTEE_MEMBER	400	Committee members	'Ledamot'
MP	300	Parliament members	'Riksdagsledamot'
SUBSTITUTE	100	Substitute members	'Suppleant'

Progression Patterns

Pattern	Description	Career Implication
ASCENDING	Steady upward progression	Successful career, increasing responsibility
CAREER_PEAK	Reached peak role weight	At highest achievement level
DESCENDING	Moving to lower tier roles	Career wind-down or party loss
LATERAL	Same tier, different roles	Specialization, no advancement
PEAK_ROLE	Highest weighted role achieved	Career pinnacle identified

Sample Queries

1. Fast-Track Careers (High Advancement Velocity)

SELECT 
    first_name,
    last_name,
    party,
    role_tier,
    advancement_velocity,
    career_first_year,
    peak_role_weight,
    progression_pattern
FROM view_riksdagen_politician_role_evolution
WHERE advancement_velocity > 50
    AND career_level IN ('TOP_LEADERSHIP', 'SENIOR_LEADERSHIP')
ORDER BY advancement_velocity DESC
LIMIT 20;

2. Role Progression Timeline

SELECT 
    role_start_year,
    role_tier,
    role_code,
    role_weight,
    years_in_role,
    progression_pattern
FROM view_riksdagen_politician_role_evolution
WHERE person_id = '0532213467925'
ORDER BY role_start_year;

3. Career Peak Analysis by Party

SELECT 
    party,
    COUNT(*) FILTER (WHERE career_level = 'TOP_LEADERSHIP') AS top_leaders,
    COUNT(*) FILTER (WHERE career_level = 'SENIOR_LEADERSHIP') AS senior_leaders,
    AVG(peak_role_weight) AS avg_peak_weight,
    AVG(advancement_velocity) AS avg_velocity
FROM view_riksdagen_politician_role_evolution
WHERE progression_pattern != 'LATERAL'
GROUP BY party
ORDER BY avg_peak_weight DESC;

Intelligence Applications

Succession Planning: Identify politicians approaching career peaks
Talent Pipeline: Track advancement velocity for fast-trackers
Power Structure Mapping: Visualize role transitions and hierarchy
Career Forecasting: Predict next role based on progression patterns
Comparative Analysis: Benchmark advancement speeds across parties

Integration Points

Links to view_riksdagen_politician_career_trajectory for performance correlation
Feeds Predictive Intelligence Framework for promotion probability scoring
Complements view_riksdagen_politician_experience_summary for role weighting
Used in Network Analysis for power structure visualization

view_riksdagen_politician_longevity_analysis ⭐⭐⭐⭐⭐

Category: Politician Intelligence Views (v1.55)
Type: Standard View
Intelligence Value: VERY HIGH - Retention Risk & Career Duration Analytics
Changelog: v1.55 Longevity Analysis

Purpose

Comprehensive career longevity and activity pattern analysis measuring politician career duration, engagement levels, and identifying retention risks for active politicians. Provides dynamic continuity scores that work across any evaluation date using career span calculations.

Key Columns

Column	Type	Description	Example
`person_id`	VARCHAR(255)	Unique politician identifier	'0532213467925'
`first_name`	VARCHAR(255)	Politician first name	'Margot'
`last_name`	VARCHAR(255)	Politician last name	'Wallström'
`party`	VARCHAR(50)	Party affiliation	'S'
`status`	VARCHAR(50)	Current status	'active', 'retired'
`born_year`	INTEGER	Birth year	1953
`career_start_date`	DATE	First activity date	1988-10-03
`career_end_date`	DATE	Last activity date	2022-09-15
`first_activity_year`	INTEGER	First election cycle	1988
`last_activity_year`	INTEGER	Last election cycle	2022
`total_career_days`	INTEGER	Total career days	12396
`total_career_years`	NUMERIC	Total years active	33.95
`age_at_career_start`	INTEGER	Age when started	35
`age_at_career_end`	INTEGER	Current age or age at exit	69
`election_cycles_active`	BIGINT	Number of cycles participated	8
`total_votes_cast`	BIGINT	Lifetime votes cast	6842
`total_assignments`	BIGINT	Total assignments held	18
`is_currently_active`	BOOLEAN	Currently serving	TRUE
`avg_votes_per_year`	NUMERIC	Voting activity rate	201.5
`avg_assignments_per_year`	NUMERIC	Assignment turnover	0.53
`career_continuity_score`	NUMERIC	% of possible cycles active	88.9
`longevity_category`	VARCHAR(50)	Duration classification	'VETERAN_20_PLUS'
`activity_level`	VARCHAR(50)	Engagement classification	'VERY_ACTIVE'
`continuity_pattern`	VARCHAR(50)	Career consistency	'CONTINUOUS'
`career_life_stage`	VARCHAR(50)	Age-based classification	'SENIOR_ACTIVE'
`retention_risk`	VARCHAR(50)	Risk assessment for active	'HIGH_RETIREMENT_RISK'

Longevity Categories

Category	Criteria	Typical Profile
VETERAN_20_PLUS	20+ years	Elder statesmen, institutional memory
LONG_SERVICE_15_20	15-20 years	Senior experienced politicians
ESTABLISHED_10_15	10-15 years	Established, experienced members
MID_CAREER_5_10	5-10 years	Mid-career, proven track record
JUNIOR_2_5	2-5 years	Junior members, building experience
NEWCOMER_UNDER_2	<2 years	New entrants, learning phase

Activity Levels

Level	Votes/Year	Description
VERY_ACTIVE	300+	Highly engaged, consistent participation
ACTIVE	200-299	Regular participation, good attendance
MODERATE	100-199	Moderate engagement, average attendance
LOW_ACTIVITY	50-99	Below average participation
MINIMAL	<50	Very low engagement, risk indicator

Retention Risk Assessment

Risk Level	Criteria	Action Required
HIGH_RETIREMENT_RISK	15+ years, age 60+, veteran	Succession planning critical
MODERATE_ATTRITION_RISK	10+ years, <150 votes/year	Engagement support needed
EARLY_EXIT_RISK	<3 years, <200 votes/year	Onboarding and support
ENGAGEMENT_RISK	Continuity <60%, any duration	Re-engagement strategies
LOW_RISK	Active, high engagement, stable	Retention secure

Sample Queries

1. High Retirement Risk (Succession Planning)

SELECT 
    first_name,
    last_name,
    party,
    total_career_years,
    age_at_career_end,
    longevity_category,
    retention_risk,
    avg_votes_per_year
FROM view_riksdagen_politician_longevity_analysis
WHERE retention_risk = 'HIGH_RETIREMENT_RISK'
    AND is_currently_active = TRUE
ORDER BY total_career_years DESC;

2. Early Exit Risk Analysis

SELECT 
    first_name,
    last_name,
    party,
    total_career_years,
    avg_votes_per_year,
    activity_level,
    retention_risk
FROM view_riksdagen_politician_longevity_analysis
WHERE retention_risk = 'EARLY_EXIT_RISK'
    AND is_currently_active = TRUE
ORDER BY avg_votes_per_year ASC;

3. Party Retention Health by Longevity

SELECT 
    party,
    COUNT(*) FILTER (WHERE longevity_category IN ('VETERAN_20_PLUS', 'LONG_SERVICE_15_20')) AS veterans,
    COUNT(*) FILTER (WHERE longevity_category = 'NEWCOMER_UNDER_2') AS newcomers,
    COUNT(*) FILTER (WHERE retention_risk IN ('HIGH_RETIREMENT_RISK', 'MODERATE_ATTRITION_RISK')) AS at_risk,
    ROUND(AVG(total_career_years), 1) AS avg_tenure,
    ROUND(AVG(career_continuity_score), 1) AS avg_continuity
FROM view_riksdagen_politician_longevity_analysis
WHERE is_currently_active = TRUE
GROUP BY party
ORDER BY avg_tenure DESC;

4. Career Continuity Patterns

SELECT 
    continuity_pattern,
    COUNT(*) AS count,
    ROUND(AVG(election_cycles_active), 1) AS avg_cycles,
    ROUND(AVG(career_continuity_score), 1) AS avg_score,
    ROUND(AVG(total_career_years), 1) AS avg_years
FROM view_riksdagen_politician_longevity_analysis
WHERE total_career_years > 0
GROUP BY continuity_pattern
ORDER BY avg_score DESC;

Intelligence Applications

Succession Planning: Identify high retirement risk politicians requiring replacement
Talent Retention: Monitor engagement levels and intervene for at-risk members
Party Strategy: Assess veteran vs newcomer balance for renewal planning
Predictive Analytics: Forecast attrition rates and career duration probabilities
Comparative Analysis: Benchmark longevity and continuity across parties

Integration Points

Feeds Predictive Intelligence Framework (Framework 4) for resignation forecasting
Links to view_riksdagen_politician_career_trajectory for performance correlation
Complements view_riksdagen_politician_role_evolution for career pattern analysis
Used in view_politician_risk_summary for comprehensive retention risk scoring

Party Views (Additional Documentation)

view_party_performance_metrics ⭐⭐⭐⭐⭐

Purpose: Comprehensive party performance indicators including win rates, document productivity, member activity, and comparative rankings.
Key Metrics: Party win percentage, avg documents per member, active member count, performance tier (TIER_1-TIER_4)
Sample Query: SELECT party, win_percentage, docs_per_member, performance_tier FROM view_party_performance_metrics ORDER BY performance_tier;
Applications: Party benchmarking, performance scorecards, electoral analysis
Sample Data: view_party_performance_metrics_sample.csv (41 rows - all Swedish political parties)

view_riksdagen_party_ballot_support_annual_summary ⭐⭐⭐⭐

Purpose: Annual aggregation of party voting support patterns, win/loss statistics, and ballot participation rates.
Key Metrics: Year, party, total_ballots, won_ballots, win_percentage, absent_percentage
Sample Query: SELECT year, party, won_ballots, win_percentage FROM view_riksdagen_party_ballot_support_annual_summary WHERE year >= 2020 ORDER BY year DESC, win_percentage DESC;
Applications: Historical performance analysis, trend identification, electoral cycle patterns

view_riksdagen_party_coalation_against_annual_summary ⭐⭐⭐⭐

Purpose: Annual summary of party opposition patterns - which parties consistently vote together AGAINST specific parties.
Key Metrics: Year, party, opposition_party, votes_against_together, opposition_alignment_rate
Sample Query: SELECT party, opposition_party, opposition_alignment_rate FROM view_riksdagen_party_coalation_against_annual_summary WHERE year = EXTRACT(YEAR FROM CURRENT_DATE) ORDER BY opposition_alignment_rate DESC;
Applications: Opposition bloc analysis, conflict mapping, adversarial relationship tracking

view_riksdagen_party_document_daily_summary ⭐⭐⭐⭐

Purpose: Daily document submission counts by party and document type (materialized).
Key Metrics: public_date, party, document_type, total_documents
Sample Query: SELECT party, SUM(total_documents) as monthly_docs FROM view_riksdagen_party_document_daily_summary WHERE public_date >= CURRENT_DATE - INTERVAL '30 days' GROUP BY party ORDER BY monthly_docs DESC;
Applications: Daily productivity tracking, document type analysis, temporal patterns

view_riksdagen_party_member ⭐⭐⭐⭐

Purpose: Current party membership roster with member details and assignment status.
Key Columns: party, person_id, first_name, last_name, status, electoral_region, assignment_type
Sample Query: SELECT party, COUNT(*) as members FROM view_riksdagen_party_member WHERE status = 'Tjänstgörande riksdagsledamot' GROUP BY party ORDER BY members DESC;
Applications: Membership tracking, party size monitoring, regional representation analysis

view_riksdagen_party_momentum_analysis ⭐⭐⭐⭐⭐

Purpose: Party momentum indicators tracking trend direction, stability, and performance velocity.
Key Metrics: party, trend_direction (STRONG_POSITIVE, POSITIVE, STABLE, NEGATIVE), momentum_score, stability_classification
Sample Query: SELECT party, trend_direction, momentum_score FROM view_riksdagen_party_momentum_analysis ORDER BY momentum_score DESC;
Applications: Electoral forecasting, trend analysis, strategic positioning assessment

view_riksdagen_party_role_member ⭐⭐⭐

Purpose: Party leadership roles and internal organization structure (party board, chair, secretary, etc.).
Key Columns: party, person_id, role_code, role_title, from_date, to_date
Sample Query: SELECT party, role_title, first_name, last_name FROM view_riksdagen_party_role_member WHERE to_date IS NULL ORDER BY party, role_title;
Applications: Leadership tracking, internal structure analysis

view_riksdagen_party_signatures_document_summary ⭐⭐⭐

Purpose: Party document signature analysis - tracks which parties co-sign documents together.
Key Metrics: party, signature_count, co_signature_parties (array), cross_party_collaboration_rate
Sample Query: SELECT party, signature_count, cross_party_collaboration_rate FROM view_riksdagen_party_signatures_document_summary ORDER BY cross_party_collaboration_rate DESC;
Applications: Coalition potential assessment, collaboration pattern analysis

view_riksdagen_party_transition_history ⭐⭐⭐⭐⭐

Purpose: Tracks politicians who switched parties while serving in Riksdagen, analyzing historical transition patterns, timing relative to elections, and subsequent political career trajectories (NEW v1.55).
Key Metrics: person_id, first_name, last_name, previous_party, new_party, transition_date, transition_type, transition_year, next_election, months_until_next_election, previous_election, months_since_last_election
Sample Query: SELECT first_name, last_name, previous_party, new_party, transition_date, transition_type, months_until_next_election FROM view_riksdagen_party_transition_history ORDER BY transition_date DESC;
Applications: Party stability analysis, defection risk assessment, electoral cycle pattern recognition, career trajectory analysis
Intelligence Value: ⭐⭐⭐⭐⭐ VERY HIGH - Tracks rare but politically significant events enabling predictive modeling of future defections
GitHub Issue: #8208 Historical Politician Transitions - Leaving Party While in Riksdagen

Key Features

Transition Type Classification: Distinguishes "SWITCHED_WHILE_SERVING" (continuous service) vs "REJOINED_RIKSDAGEN" (gap >30 days)
Election Proximity Analysis: Calculates months until next election and months since last election
Historical Coverage: Tracks transitions since 2002 across 7 Swedish election cycles (2002-2026)
Window Functions: Uses LAG/LEAD over assignment_data.org_code to detect party changes

Intelligence Applications

Predictive Intelligence: Framework 4 - Defection risk modeling and early warning signals
Temporal Analysis: Framework 1 - Electoral cycle influence on party transitions
Risk Assessment: Framework 3 - Party stability indicators and cohesion metrics
Strategic Forecasting: Coalition formation analysis considering defection patterns

view_riksdagen_party_defector_analysis ⭐⭐⭐⭐⭐

Purpose: Analyzes behavioral patterns of politicians who defected from their party, measuring pre/post transition voting attendance and document productivity to identify early warning signals (NEW v1.55).
Key Metrics: person_id, first_name, last_name, previous_party, new_party, transition_date, months_until_next_election, pre_transition_attendance, post_transition_attendance, attendance_change, docs_before, docs_after, defection_timing
Sample Query: SELECT first_name, last_name, previous_party, new_party, defection_timing, attendance_change FROM view_riksdagen_party_defector_analysis WHERE ABS(attendance_change) > 10 ORDER BY transition_date DESC;
Applications: Early warning system for defections, behavioral anomaly detection, engagement pattern analysis
Intelligence Value: ⭐⭐⭐⭐⭐ VERY HIGH - Identifies predictive signals for party defection risk
GitHub Issue: #8208 Historical Politician Transitions - Leaving Party While in Riksdagen

Key Features

Attendance Analysis: 6-month pre/post transition voting attendance patterns from vote_data
Defection Timing Classification: PRE_ELECTION (≤12mo), MID_TERM (≥36mo), NORMAL (>12mo and <36mo), UNKNOWN_TIMING (post-2026 transitions)
Behavioral Metrics: Attendance change delta, document production before/after transition
Early Warning Signals: Declining participation as predictor of party switching

Intelligence Applications

Risk Assessment: Identifies politicians at risk of defection based on declining engagement
Behavioral Analysis: Detects participation drop-offs preceding party transitions
Predictive Modeling: Builds defection risk scores based on historical patterns
Counterintelligence: Monitors party cohesion and loyalty indicators

view_riksdagen_party_switcher_outcomes ⭐⭐⭐⭐⭐

Purpose: Measures post-transition career success for party switchers, tracking continued MP status, re-election success, leadership positions, and subsequent political assignments (NEW v1.55).
Key Metrics: person_id, first_name, last_name, previous_party, new_party, transition_date, next_election, months_until_next_election, total_subsequent_assignments, total_days_served_after_switch, continued_as_active_mp, served_in_next_election, attained_leadership_post_switch, post_switch_roles, current_status
Sample Query: SELECT first_name, last_name, previous_party, new_party, continued_as_active_mp, served_in_next_election, attained_leadership_post_switch FROM view_riksdagen_party_switcher_outcomes ORDER BY transition_date DESC;
Applications: Career trajectory analysis, defection outcome assessment, party switching viability evaluation
Intelligence Value: ⭐⭐⭐⭐⭐ VERY HIGH - Quantifies career consequences of party transitions
GitHub Issue: #8208 Historical Politician Transitions - Leaving Party While in Riksdagen

Key Features

Career Outcome Metrics: Total subsequent assignments, days served after transition
Electoral Success: Tracks continued MP status and service in next election cycle
Leadership Attainment: Identifies leadership positions gained post-transition (Partiledare, Gruppledare, etc.)
NULL-Safe Handling: Handles transitions after last defined election (2026-09-13)

Intelligence Applications

Strategic Assessment: Evaluates career viability of party switching decisions
Predictive Intelligence: Models defection success rates by timing and target party
Historical Analysis: Tracks long-term consequences of political realignment
Coalition Dynamics: Assesses impact of defections on party strength and government formation

view_riksdagen_person_signed_document_summary ⭐⭐⭐

Purpose: Individual politician document signature patterns and co-signing behavior.
Key Metrics: person_id, total_signatures, solo_documents, co_signed_documents, collaboration_rate
Sample Query: SELECT first_name, last_name, party, collaboration_rate FROM view_riksdagen_person_signed_document_summary WHERE total_signatures >= 50 ORDER BY collaboration_rate DESC LIMIT 20;
Applications: Collaboration analysis, legislative style assessment

view_riksdagen_member_proposals ⭐⭐⭐

Purpose: Parliamentary member legislative proposals (motions) submitted to Riksdagen.
Key Columns: id, document_type, title, made_public_date, org (committee), status
Sample Query: SELECT title, made_public_date, org, status FROM view_riksdagen_member_proposals WHERE made_public_date >= CURRENT_DATE - INTERVAL '3 months' ORDER BY made_public_date DESC;
Applications: Legislative initiative tracking, proposal success rate analysis

Vote Data Views (Complete Documentation)

Overview

Vote data views provide temporal aggregations of ballot outcomes, party voting patterns, and politician voting records at daily, weekly, monthly, and annual granularities. All vote summary views are materialized and refreshed daily at 02:00 UTC for optimal query performance.

Ballot-Level Vote Summaries (5 views) ⭐⭐⭐⭐

Views: view_riksdagen_vote_data_ballot_summary, _daily, _weekly, _monthly, _annual

Purpose: Aggregate ballot outcomes with winner/loser counts, total votes, and decision types.

Key Metrics: ballot_id, vote_date, total_votes_cast, winning_side_count, losing_side_count, decision_type

Sample Query (Daily):

SELECT vote_date, COUNT(DISTINCT ballot_id) as ballots_count,
       SUM(total_votes_cast) as total_votes
FROM view_riksdagen_vote_data_ballot_summary_daily
WHERE vote_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY vote_date
ORDER BY vote_date DESC;

Applications: Parliamentary activity monitoring, voting frequency analysis, decision type trends

Party-Level Vote Summaries (5 views) ⭐⭐⭐⭐⭐

Views: view_riksdagen_vote_data_ballot_party_summary, _daily, _weekly, _monthly, _annual

Purpose: Party voting behavior aggregated by time period with win rates, absence rates, and discipline metrics.

Key Metrics: party, vote_date/period, total_ballots, won_ballots, lost_ballots, absent_votes, win_percentage, absence_percentage, party_discipline_score

Sample Query (Monthly):

SELECT month_start, party, won_ballots, win_percentage, absence_percentage
FROM view_riksdagen_vote_data_ballot_party_summary_monthly
WHERE month_start >= DATE_TRUNC('year', CURRENT_DATE)
ORDER BY month_start DESC, win_percentage DESC;

Applications: Party effectiveness tracking, temporal performance analysis, coalition strength assessment

Politician-Level Vote Summaries (5 views) ⭐⭐⭐⭐⭐

Views: view_riksdagen_vote_data_ballot_politician_summary, _daily (documented), _weekly, _monthly, _annual

Purpose: Individual politician voting records aggregated by time period with win rates, absence, and rebellion metrics.

Key Metrics: person_id, vote_date/period, ballots_participated, won_votes, lost_votes, absent_votes, rebellion_votes, win_percentage, absence_percentage, rebellion_rate

Sample Query (Annual):

SELECT year, person_id, first_name, last_name, party,
       ballots_participated, win_percentage, absence_percentage
FROM view_riksdagen_vote_data_ballot_politician_summary_annual
WHERE year = EXTRACT(YEAR FROM CURRENT_DATE)
ORDER BY win_percentage DESC
LIMIT 30;

Applications: Politician performance scorecards, behavioral trend analysis, absence tracking

Intelligence Applications

These views are CRITICAL for the CIA platform's intelligence capabilities:

Analysis Framework	Use Case	Supported Granularities	Link
Temporal Analysis	Track voting behavior changes over time	Daily, Weekly, Monthly, Annual	Framework Docs
Comparative Analysis	Benchmark politicians against peers	Annual, Monthly	Framework Docs
Pattern Recognition	Detect behavioral anomalies and clusters	All granularities	Framework Docs
Predictive Intelligence	Forecast future voting behavior and risks	Monthly, Annual	Framework Docs

Risk Rules Supported: These views power most politician risk rules including:

Intelligence Products Generated:

🏆 Political Scorecards - Performance rankings by win rate, absence, productivity
⚠️ Risk Assessments - Behavioral anomaly detection and risk scoring
📊 Trend Reports - Temporal analysis of engagement and effectiveness
🎯 Predictive Alerts - Early warning system for declining performance

Data Flow: Intelligence Data Flow Map - Vote Data Views

Committee Views (Complete Documentation)

view_riksdagen_committee ⭐⭐⭐⭐

Purpose: Committee structure, membership, and activity metrics for all Riksdagen committees.
Key Columns: committee_code, committee_name, member_count, active_proposals, decision_count, chair_person_id
Sample Query: SELECT committee_name, member_count, active_proposals FROM view_riksdagen_committee ORDER BY active_proposals DESC;
Applications: Committee workload analysis, membership tracking, productivity assessment

view_committee_productivity ⭐⭐⭐⭐

Purpose: Committee productivity metrics with decision counts, processing times, and efficiency indicators.
Key Metrics: committee_code, decisions_per_quarter, avg_processing_days, productivity_level (HIGH, MODERATE, LOW)
Sample Query: SELECT committee_name, decisions_per_quarter, productivity_level FROM view_committee_productivity ORDER BY decisions_per_quarter DESC;
Applications: Committee efficiency analysis, resource allocation, bottleneck identification

view_committee_productivity_matrix ⭐⭐⭐⭐

Purpose: Matrix comparison of all committees across productivity dimensions (decisions, proposals, processing speed).
Key Metrics: committee_code, productivity_rank, efficiency_percentile, workload_tier
Sample Query: SELECT committee_name, productivity_rank, efficiency_percentile FROM view_committee_productivity_matrix ORDER BY productivity_rank;
Applications: Cross-committee benchmarking, performance tier classification

view_riksdagen_committee_ballot_decision_party_summary ⭐⭐⭐⭐

Purpose: Party-level voting behavior in committee ballots (materialized).
Key Metrics: committee_code, ballot_id, party, votes_for, votes_against, party_position
Sample Query: SELECT committee_code, party, COUNT(*) as decisions, SUM(votes_for) as total_support FROM view_riksdagen_committee_ballot_decision_party_summary GROUP BY committee_code, party;
Applications: Party committee alignment analysis, voting coalition patterns

view_riksdagen_committee_ballot_decision_politician_summary ⭐⭐⭐⭐

Purpose: Individual politician voting in committee ballots (materialized).
Key Metrics: committee_code, ballot_id, person_id, vote_decision, alignment_with_party
Sample Query: SELECT person_id, first_name, last_name, COUNT(*) as committee_votes FROM view_riksdagen_committee_ballot_decision_politician_summary GROUP BY person_id, first_name, last_name ORDER BY committee_votes DESC LIMIT 20;
Applications: Committee participation tracking, individual voting patterns

view_riksdagen_committee_ballot_decision_summary ⭐⭐⭐⭐

Purpose: Aggregated committee ballot outcomes with winner/loser counts (materialized).
Key Metrics: committee_code, ballot_id, decision_date, total_votes, votes_for, votes_against, decision_outcome
Sample Query: SELECT committee_code, COUNT(*) as total_decisions FROM view_riksdagen_committee_ballot_decision_summary GROUP BY committee_code ORDER BY total_decisions DESC;
Applications: Committee decision volume analysis, outcome tracking

view_riksdagen_committee_decisions ⭐⭐⭐⭐

Purpose: Detailed committee decisions including proposal handling, recommendations, and approval status (materialized).
Key Metrics: decision_id, committee_code, proposal_id, decision_type, decision_date, recommendation, status
Sample Query: SELECT committee_code, decision_type, COUNT(*) as count FROM view_riksdagen_committee_decisions WHERE decision_date >= CURRENT_DATE - INTERVAL '12 months' GROUP BY committee_code, decision_type;
Applications: Decision type analysis, proposal success rates, committee workload

view_riksdagen_committee_decision_type_org_summary ⭐⭐⭐

Purpose: Committee decisions aggregated by type and organization (materialized).
Key Metrics: committee_code, org_code, decision_type, total_decisions
Sample Query: SELECT committee_code, decision_type, SUM(total_decisions) as count FROM view_riksdagen_committee_decision_type_org_summary GROUP BY committee_code, decision_type ORDER BY count DESC;
Applications: Decision type distribution, organizational pattern analysis

view_riksdagen_committee_decision_type_summary ⭐⭐⭐

Purpose: Simplified committee decision aggregation by type (materialized).
Key Metrics: committee_code, decision_type, decision_count
Sample Query: SELECT * FROM view_riksdagen_committee_decision_type_summary ORDER BY committee_code, decision_count DESC;
Applications: Quick decision type reference, committee specialization

view_riksdagen_committee_parliament_member_proposal ⭐⭐⭐

Purpose: Parliamentary member proposals assigned to specific committees.
Key Columns: proposal_id, committee_code, person_id, proposal_type, submitted_date, status
Sample Query: SELECT committee_code, COUNT(*) as proposals FROM view_riksdagen_committee_parliament_member_proposal GROUP BY committee_code ORDER BY proposals DESC;
Applications: Committee workload assessment, proposal routing analysis

view_riksdagen_committee_role_member ⭐⭐⭐

Purpose: Committee role assignments (chair, vice-chair, members) with assignment dates.
Key Columns: committee_code, person_id, role_code, role_title, from_date, to_date
Sample Query: SELECT committee_code, role_title, first_name, last_name FROM view_riksdagen_committee_role_member WHERE to_date IS NULL ORDER BY committee_code, role_title;
Applications: Committee leadership tracking, membership history

view_riksdagen_committee_roles ⭐⭐⭐

Purpose: Committee role definitions and structure (metadata).
Key Columns: role_code, role_title, committee_code, role_level
Sample Query: SELECT * FROM view_riksdagen_committee_roles ORDER BY committee_code, role_level;
Applications: Role reference data, committee structure documentation

Document Views (Additional Documentation)

view_document_data_committee_report_url ⭐⭐⭐

Purpose: URLs for committee reports and documents for external reference/linking.
Key Columns: document_id, committee_code, report_url_xml, report_url_html
Sample Query: SELECT document_id, committee_code, report_url_html FROM view_document_data_committee_report_url WHERE committee_code = 'au' LIMIT 10;
Applications: Document linking, external system integration

view_riksdagen_org_document_daily_summary ⭐⭐⭐

Purpose: Daily document submissions by organization/committee (materialized).
Key Metrics: public_date, org_code, document_type, total_documents
Sample Query: SELECT org_code, SUM(total_documents) as monthly_docs FROM view_riksdagen_org_document_daily_summary WHERE public_date >= CURRENT_DATE - INTERVAL '30 days' GROUP BY org_code ORDER BY monthly_docs DESC;
Applications: Organizational productivity tracking, temporal document patterns

view_riksdagen_document_type_daily_summary ⭐⭐⭐

Purpose: Daily document type distribution across entire Riksdagen (materialized).
Key Metrics: public_date, document_type, total_documents
Sample Query: SELECT document_type, SUM(total_documents) as yearly_count FROM view_riksdagen_document_type_daily_summary WHERE public_date >= DATE_TRUNC('year', CURRENT_DATE) GROUP BY document_type ORDER BY yearly_count DESC;
Applications: Document type trend analysis, legislative activity monitoring

Application & Audit Views (Complete Documentation)

Application Action Event Views (12 views) ⭐⭐

Purpose: Track user interactions with CIA platform pages, UI elements, and access modes at various time granularities.

Key Metrics: action_date/period, page_name, element_name, action_type, event_count, unique_users

Applications: User behavior analytics, feature usage tracking, platform optimization, engagement analysis

view_application_action_event_page_annual_summary ⭐⭐

Purpose: Annual summary of user interactions with platform pages.
Key Metrics: year, page_name, total_events, unique_users, avg_events_per_user
Sample Query: SELECT year, page_name, total_events FROM view_application_action_event_page_annual_summary WHERE year >= 2024 ORDER BY year DESC, total_events DESC;
Applications: Annual usage trends, page popularity analysis, long-term engagement tracking

view_application_action_event_page_daily_summary ⭐⭐

Purpose: Daily summary of user interactions with platform pages.
Key Metrics: action_date, page_name, event_count, unique_users
Sample Query: SELECT action_date, page_name, SUM(event_count) as total FROM view_application_action_event_page_daily_summary WHERE action_date >= CURRENT_DATE - 7 GROUP BY action_date, page_name ORDER BY action_date DESC;
Applications: Daily usage monitoring, page traffic analysis, user engagement tracking

view_application_action_event_page_element_annual_summary ⭐⭐

Purpose: Annual summary of user interactions with UI elements within pages.
Key Metrics: year, page_name, element_name, total_clicks, unique_users
Sample Query: SELECT year, page_name, element_name, total_clicks FROM view_application_action_event_page_element_annual_summary WHERE year >= 2024 ORDER BY total_clicks DESC;
Applications: Feature usage analysis, UI/UX optimization, element effectiveness tracking

view_application_action_event_page_element_daily_summary ⭐⭐

Purpose: Daily summary of user interactions with UI elements within pages.
Key Metrics: action_date, page_name, element_name, click_count, unique_users
Sample Query: SELECT action_date, element_name, SUM(click_count) as clicks FROM view_application_action_event_page_element_daily_summary WHERE action_date >= CURRENT_DATE - 7 GROUP BY action_date, element_name ORDER BY clicks DESC;
Applications: Daily feature monitoring, UI element tracking, user interaction patterns

view_application_action_event_page_element_hourly_summary ⭐⭐

Purpose: Hourly summary of user interactions with UI elements within pages.
Key Metrics: action_timestamp, page_name, element_name, click_count, unique_users
Sample Query: SELECT DATE_TRUNC('hour', action_timestamp) as hour, element_name, SUM(click_count) FROM view_application_action_event_page_element_hourly_summary WHERE action_timestamp >= NOW() - INTERVAL '24 hours' GROUP BY hour, element_name;
Applications: Real-time monitoring, peak usage detection, hourly engagement patterns

view_application_action_event_page_element_weekly_summary ⭐⭐

Purpose: Weekly summary of user interactions with UI elements within pages.
Key Metrics: week_start_date, page_name, element_name, total_clicks, unique_users
Sample Query: SELECT week_start_date, element_name, SUM(total_clicks) as clicks FROM view_application_action_event_page_element_weekly_summary WHERE week_start_date >= CURRENT_DATE - 30 GROUP BY week_start_date, element_name ORDER BY clicks DESC;
Applications: Weekly trend analysis, UI element performance, engagement patterns

view_application_action_event_page_hourly_summary ⭐⭐

Purpose: Hourly summary of user interactions with platform pages.
Key Metrics: action_timestamp, page_name, event_count, unique_users
Sample Query: SELECT DATE_TRUNC('hour', action_timestamp) as hour, page_name, SUM(event_count) FROM view_application_action_event_page_hourly_summary WHERE action_timestamp >= NOW() - INTERVAL '24 hours' GROUP BY hour, page_name ORDER BY hour DESC;
Applications: Real-time traffic monitoring, peak hour detection, load balancing

view_application_action_event_page_modes_annual_summary ⭐⭐

Purpose: Annual summary of user access modes (desktop, mobile, tablet) by page.
Key Metrics: year, page_name, access_mode, event_count, unique_users
Sample Query: SELECT year, access_mode, SUM(event_count) as total FROM view_application_action_event_page_modes_annual_summary WHERE year >= 2024 GROUP BY year, access_mode ORDER BY total DESC;
Applications: Device usage trends, platform optimization, responsive design decisions

view_application_action_event_page_modes_daily_summary ⭐⭐

Purpose: Daily summary of user access modes (desktop, mobile, tablet) by page.
Key Metrics: action_date, page_name, access_mode, event_count, unique_users
Sample Query: SELECT action_date, access_mode, SUM(event_count) as total FROM view_application_action_event_page_modes_daily_summary WHERE action_date >= CURRENT_DATE - 7 GROUP BY action_date, access_mode ORDER BY action_date DESC;
Applications: Daily device analytics, mobile vs desktop usage, accessibility monitoring

view_application_action_event_page_modes_hourly_summary ⭐⭐

Purpose: Hourly summary of user access modes (desktop, mobile, tablet) by page.
Key Metrics: action_timestamp, page_name, access_mode, event_count, unique_users
Sample Query: SELECT DATE_TRUNC('hour', action_timestamp) as hour, access_mode, SUM(event_count) FROM view_application_action_event_page_modes_hourly_summary WHERE action_timestamp >= NOW() - INTERVAL '24 hours' GROUP BY hour, access_mode;
Applications: Real-time device monitoring, peak usage by device type

view_application_action_event_page_modes_weekly_summary ⭐⭐

Purpose: Weekly summary of user access modes (desktop, mobile, tablet) by page.
Key Metrics: week_start_date, page_name, access_mode, event_count, unique_users
Sample Query: SELECT week_start_date, access_mode, SUM(event_count) as total FROM view_application_action_event_page_modes_weekly_summary WHERE week_start_date >= CURRENT_DATE - 30 GROUP BY week_start_date, access_mode ORDER BY total DESC;
Applications: Weekly device trends, platform strategy decisions

view_application_action_event_page_weekly_summary ⭐⭐

Purpose: Weekly summary of user interactions with platform pages.
Key Metrics: week_start_date, page_name, event_count, unique_users
Sample Query: SELECT week_start_date, page_name, SUM(event_count) as total FROM view_application_action_event_page_weekly_summary WHERE week_start_date >= CURRENT_DATE - 90 GROUP BY week_start_date, page_name ORDER BY week_start_date DESC;
Applications: Weekly engagement tracking, page popularity trends, user activity patterns

view_audit_author_summary ⭐⭐

Purpose: Data change audit trail by author/user.
Key Metrics: author_id, total_changes, tables_modified, last_change_date
Sample Query: SELECT author_id, total_changes, tables_modified FROM view_audit_author_summary ORDER BY total_changes DESC;
Applications: Change tracking, data governance, audit compliance

view_audit_data_summary ⭐⭐

Purpose: Aggregated audit trail statistics (inserts, updates, deletes by table).
Key Metrics: table_name, insert_count, update_count, delete_count, last_modified
Sample Query: SELECT table_name, insert_count, update_count, delete_count FROM view_audit_data_summary ORDER BY (insert_count + update_count + delete_count) DESC;
Applications: Data modification monitoring, audit reporting, change volume analysis

WorldBank Data View

view_worldbank_indicator_data_country_summary ⭐⭐⭐

Purpose: Economic indicators for Sweden from World Bank data (materialized).
Key Metrics: country_code, indicator_id, year, indicator_value, indicator_name
Sample Query: SELECT year, indicator_name, indicator_value FROM view_worldbank_indicator_data_country_summary WHERE country_code = 'SWE' AND indicator_id = 'NY.GDP.MKTP.CD' ORDER BY year DESC;
Applications: Economic context for political analysis, GDP trends, macro-economic indicators

Party Views

Overview

Party views provide organizational-level intelligence on Swedish political parties, tracking electoral performance, coalition behavior, internal discipline, decision effectiveness, performance trends, and longitudinal analysis across election cycles (2002-2026). These views enable coalition analysis, party comparison, government formation forecasting, and historical trend analysis.

Total Party Views: 16 (NEW: 4 Recreated Party Analysis views v1.61 + Party Transition Tracking v1.57 + Party Decision Flow v1.35)
Intelligence Value: ⭐⭐⭐⭐⭐ VERY HIGH
Primary Use Cases: Coalition analysis, party performance monitoring, electoral forecasting, bloc alignment, legislative effectiveness, longitudinal trend analysis, election cycle patterns, coalition evolution tracking

New in v1.61: 4 views recreated after v1.53/v1.6 drops:

view_riksdagen_party_summary - Party-level assignment and document aggregation (59 columns)
view_riksdagen_party_longitudinal_performance - Semester-based performance tracking with 70 KPIs and advanced window functions
view_riksdagen_party_coalition_evolution - Party-pair alliance tracking with 35 coalition metrics
view_riksdagen_party_electoral_trends - Electoral performance analysis with 49 forecasting indicators

view_riksdagen_party ⭐⭐⭐⭐⭐

Category: Base Party Views (v1.1, enhanced multiple versions)
Type: Standard View
Intelligence Value: VERY HIGH - Core Party Profile

Business Context

Market Value: €8M TAM (Media & Journalism segment)
Product Integration: Political Intelligence API, Advanced Analytics Suite
Revenue Impact: Essential for €6,000/month Professional and €15,000/month Enterprise tiers
JSON Export Spec: party-schema.md
API Endpoint: GET /api/v1/parties, GET /api/v1/parties/{id}

Used In Product Features:

Party performance dashboards (Corporate Government Affairs, NGOs & Advocacy)
Coalition stability monitoring (Political Parties, Political Consulting)
Comparative party analysis (Media & Journalism, Academic Research)
Voting pattern analysis (All segments)

Target Customer Segments:

Media & Journalism (€8M TAM): Investigative reporting, election coverage, political analysis
Corporate Affairs (€12M TAM): Government relations, stakeholder monitoring, regulatory risk
Political Consulting (€15M TAM): Coalition forecasting, opposition research, campaign strategy
Academic Research (€5M TAM): Political science research, party system analysis

Business Documentation: See BUSINESS_PRODUCT_DOCUMENT.md#product-line-1

Purpose

Central party profile view aggregating party identification, current member counts, parliamentary representation, and organizational metadata. Serves as the primary reference for party-level analysis and coalition mapping.

Key Columns

Column	Type	Description	Example
`party`	VARCHAR(50)	Party short code (primary key)	'S', 'M', 'SD', 'C', 'V', 'KD', 'L', 'MP'
`party_name`	VARCHAR(255)	Full party name	'Socialdemokraterna'
`party_english_name`	VARCHAR(255)	English translation	'Social Democrats'
`member_count`	INTEGER	Current active members	70
`website`	VARCHAR(255)	Official party website	'https://www.socialdemokraterna.se'
`registered_date`	DATE	Registration date	'1889-04-23'
`party_size_category`	VARCHAR(50)	Classification	'MAJOR', 'MEDIUM', 'SMALL'
`bloc_alignment`	VARCHAR(50)	Political bloc	'LEFT_BLOC', 'RIGHT_BLOC', 'INDEPENDENT'

Swedish Political Parties

Code	Party Name (Swedish)	English Name	Bloc	Historical Seats
S	Socialdemokraterna	Social Democrats	LEFT	70-110
M	Moderaterna	Moderate Party	RIGHT	68-107
SD	Sverigedemokraterna	Sweden Democrats	RIGHT*	62-73
C	Centerpartiet	Centre Party	RIGHT	22-31
V	Vänsterpartiet	Left Party	LEFT	21-28
KD	Kristdemokraterna	Christian Democrats	RIGHT	19-25
L	Liberalerna	Liberals	RIGHT	16-24
MP	Miljöpartiet	Green Party	LEFT	16-25

*Note: SD traditionally excluded from coalitions but increasingly influential

Bloc Structure

LEFT_BLOC (Red-Green):

S (Social Democrats) - Traditional lead party
V (Left Party) - Socialist/left-wing
MP (Green Party) - Environmental focus

RIGHT_BLOC (Alliance/Tidö):

M (Moderate Party) - Traditional lead party
C (Centre Party) - Liberal-conservative (sometimes swing)
KD (Christian Democrats) - Conservative
L (Liberals) - Classical liberal
SD (Sweden Democrats) - Nationalist (conditionally included post-2022)

Example Queries

1. Current Parliament Composition

SELECT
    party,
    party_name,
    member_count,
    bloc_alignment,
    ROUND(100.0 * member_count / SUM(member_count) OVER (), 2) AS seat_percentage,
    party_size_category
FROM view_riksdagen_party
WHERE member_count > 0
ORDER BY member_count DESC;

Output:

 party | party_name             | member_count | bloc    | seat_percentage | category
-------+------------------------+--------------+---------+----------------+---------
 SD    | Sverigedemokraterna    |           73 |  RIGHT  |         20.92   | MAJOR
 S     | Socialdemokraterna     |           70 |  LEFT   |         20.06   | MAJOR
 M     | Moderaterna            |           68 |  RIGHT  |         19.48   | MAJOR

2. Bloc Strength Analysis

SELECT
    bloc_alignment,
    COUNT(DISTINCT party) AS party_count,
    SUM(member_count) AS total_seats,
    ROUND(100.0 * SUM(member_count) / (SELECT SUM(member_count) FROM view_riksdagen_party), 2) AS bloc_percentage,
    STRING_AGG(party || ' (' || member_count || ')', ', ' ORDER BY member_count DESC) AS parties
FROM view_riksdagen_party
WHERE member_count > 0
GROUP BY bloc_alignment
ORDER BY total_seats DESC;

3. Historical Party Comparison (Requires Join with Election Data)

SELECT
    rp.party,
    rp.party_name,
    rp.member_count AS current_seats,
    ed.previous_seats,
    rp.member_count - ed.previous_seats AS seat_change,
    ROUND(100.0 * (rp.member_count - ed.previous_seats) / NULLIF(ed.previous_seats, 0), 1) AS change_pct
FROM view_riksdagen_party rp
LEFT JOIN election_data ed ON ed.party = rp.party AND ed.election_year = 2022
WHERE rp.member_count > 0
ORDER BY seat_change DESC;

4. Party Size Distribution

SELECT
    party_size_category,
    COUNT(*) AS party_count,
    STRING_AGG(party || ' (' || member_count || ')', ', ' ORDER BY member_count DESC) AS parties,
    SUM(member_count) AS total_members
FROM view_riksdagen_party
WHERE member_count > 0
GROUP BY party_size_category
ORDER BY 
    CASE party_size_category
        WHEN 'MAJOR' THEN 1
        WHEN 'MEDIUM' THEN 2
        WHEN 'SMALL' THEN 3
    END;

5. Coalition Viability Matrix (Seat Count Combinations)

WITH party_combos AS (
    SELECT
        p1.party AS party_1,
        p1.member_count AS seats_1,
        p2.party AS party_2,
        p2.member_count AS seats_2,
        p1.member_count + p2.member_count AS combined_seats
    FROM view_riksdagen_party p1
    CROSS JOIN view_riksdagen_party p2
    WHERE p1.party < p2.party  -- Avoid duplicates
        AND p1.member_count > 0
        AND p2.member_count > 0
)
SELECT
    party_1,
    party_2,
    combined_seats,
    CASE
        WHEN combined_seats >= 175 THEN 'MAJORITY_POSSIBLE'
        WHEN combined_seats >= 165 THEN 'CLOSE_TO_MAJORITY'
        ELSE 'INSUFFICIENT'
    END AS coalition_viability
FROM party_combos
WHERE combined_seats >= 165  -- Near majority threshold (175/349)
ORDER BY combined_seats DESC;

Performance Characteristics

Query Time: <5ms (small table, indexed)
Indexes Used: Primary key on party
Data Volume: ~15 rows (active + historical parties)
Refresh Frequency: Real-time (updated with member assignments)

Data Sources

Primary Table: Aggregation from person_data and assignment_data
Party Registry: sweden_political_party for official information

Dependencies

No view dependencies
Used by: Nearly all party-related views

Risk Rules Supported

PartyWeakSupport (Y-01): Member count for strength assessment
PartyCoalitionUnstable (Y-02): Bloc alignment for coalition analysis
All party-level risk rules use this as base reference

Intelligence Frameworks Applicable

Comparative Analysis: Party-to-party comparisons
Coalition Analysis: Government formation scenarios
Power Structure: Parliamentary balance assessment

view_party_effectiveness_trends ⭐⭐⭐⭐⭐

Category: Intelligence Views (v1.30)
Type: Standard View
Intelligence Value: VERY HIGH - Party Performance Tracking
Changelog: v1.30 OSINT Party Effectiveness Monitoring

Purpose

Comprehensive party-level performance tracking with monthly time-series analysis of voting effectiveness, member discipline, legislative productivity, and coalition behavior. Enables trend detection and comparative party performance assessment.

Key Columns

Column	Type	Description	Example
`party`	VARCHAR(50)	Party code	'S'
`year_month`	DATE	Month of analysis	'2024-10-01'
`active_members`	INTEGER	Members active in voting	68
`avg_party_absence_rate`	NUMERIC(5,2)	Average absence across members	8.50
`avg_party_win_rate`	NUMERIC(5,2)	Average effectiveness	72.30
`avg_party_discipline`	NUMERIC(5,2)	Party cohesion (100 - rebel rate)	94.20
`total_documents`	INTEGER	Documents produced this month	145
`interpellations_filed`	INTEGER	Questions to government	23
`motions_submitted`	INTEGER	Legislative proposals	67
`committee_participation`	NUMERIC(5,2)	Committee activity level	88.50
`absence_trend`	NUMERIC(5,2)	Month-over-month change	-1.20
`effectiveness_trend`	NUMERIC(5,2)	Win rate change	+2.50
`discipline_trend`	NUMERIC(5,2)	Cohesion change	+0.80
`productivity_trend`	INTEGER	Document count change	+12
`ma_3month_effectiveness`	NUMERIC(5,2)	3-month moving average	71.80
`party_performance_status`	VARCHAR(50)	Classification	'STRONG_PERFORMANCE'
`coalition_potential_score`	NUMERIC(5,2)	Reliability metric (0-100)	87.50

Performance Classifications

Party Performance Status:

EXCELLENT_PERFORMANCE: High effectiveness, low absence, high discipline
STRONG_PERFORMANCE: Above average across all metrics
MODERATE_PERFORMANCE: Average performance, some weaknesses
CONCERNING_PERFORMANCE: Multiple problematic indicators
POOR_PERFORMANCE: Systematic performance issues

Coalition Potential Score (0-100): Calculated from:

Party discipline (40% weight) - Cohesion indicator
Attendance rates (30% weight) - Reliability indicator
Effectiveness trends (30% weight) - Momentum indicator

Example Queries

1. Current Party Performance Rankings

SELECT
    party,
    active_members,
    ROUND(avg_party_absence_rate, 2) AS absence_rate,
    ROUND(avg_party_win_rate, 2) AS win_rate,
    ROUND(avg_party_discipline, 2) AS discipline,
    total_documents,
    party_performance_status,
    ROUND(coalition_potential_score, 2) AS coalition_score
FROM view_party_effectiveness_trends
WHERE year_month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
ORDER BY coalition_potential_score DESC;

2. Party Performance Trends (Last 12 Months)

SELECT
    party,
    COUNT(*) AS months_tracked,
    ROUND(AVG(avg_party_win_rate), 2) AS avg_effectiveness_12mo,
    ROUND(AVG(avg_party_absence_rate), 2) AS avg_absence_12mo,
    ROUND(AVG(avg_party_discipline), 2) AS avg_discipline_12mo,
    ROUND(AVG(effectiveness_trend), 2) AS avg_trend,
    SUM(total_documents) AS total_docs_12mo
FROM view_party_effectiveness_trends
WHERE year_month >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY party
ORDER BY avg_effectiveness_12mo DESC;

3. Party Momentum Analysis (Improving vs. Declining)

WITH recent_trends AS (
    SELECT
        party,
        AVG(effectiveness_trend) AS avg_effectiveness_trend,
        AVG(discipline_trend) AS avg_discipline_trend,
        AVG(productivity_trend) AS avg_productivity_trend
    FROM view_party_effectiveness_trends
    WHERE year_month >= CURRENT_DATE - INTERVAL '6 months'
    GROUP BY party
)
SELECT
    party,
    ROUND(avg_effectiveness_trend, 2) AS effectiveness_momentum,
    ROUND(avg_discipline_trend, 2) AS discipline_momentum,
    ROUND(avg_productivity_trend, 0) AS productivity_momentum,
    CASE
        WHEN avg_effectiveness_trend > 2 AND avg_discipline_trend > 0 THEN 'STRONG_MOMENTUM'
        WHEN avg_effectiveness_trend > 0 THEN 'POSITIVE_MOMENTUM'
        WHEN avg_effectiveness_trend < -2 THEN 'DECLINING'
        ELSE 'STABLE'
    END AS momentum_assessment
FROM recent_trends
ORDER BY avg_effectiveness_trend DESC;

4. Comparative Party Analysis (Bloc-Level Aggregation)

WITH party_bloc AS (
    SELECT
        CASE
            WHEN pef.party IN ('S', 'V', 'MP') THEN 'LEFT_BLOC'
            WHEN pef.party IN ('M', 'KD', 'L', 'C', 'SD') THEN 'RIGHT_BLOC'
            ELSE 'OTHER'
        END AS bloc,
        pef.*
    FROM view_party_effectiveness_trends pef
    WHERE year_month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
)
SELECT
    bloc,
    COUNT(DISTINCT party) AS party_count,
    SUM(active_members) AS total_members,
    ROUND(AVG(avg_party_win_rate), 2) AS bloc_avg_effectiveness,
    ROUND(AVG(avg_party_discipline), 2) AS bloc_avg_discipline,
    ROUND(AVG(coalition_potential_score), 2) AS bloc_avg_coalition_score
FROM party_bloc
GROUP BY bloc
ORDER BY bloc_avg_effectiveness DESC;

5. Party Productivity Analysis (Legislative Output)

SELECT
    party,
    year_month,
    total_documents,
    interpellations_filed,
    motions_submitted,
    ROUND(interpellations_filed::NUMERIC / NULLIF(active_members, 0), 2) AS interpellations_per_member,
    ROUND(total_documents::NUMERIC / NULLIF(active_members, 0), 2) AS documents_per_member,
    productivity_trend
FROM view_party_effectiveness_trends
WHERE year_month >= CURRENT_DATE - INTERVAL '6 months'
ORDER BY party, year_month DESC;
``$

#### \text{Performance} \text{Characteristics}

- **\text{Query} \text{Time}:** 150-250\text{ms} (\text{complex} \text{multi}-\text{source} \text{aggregation})
- **\text{Indexes} \text{Used}:** \text{Multiple} \text{indexes} \text{on} \text{vote}, \text{document}, \text{and} \text{assignment} \text{tables}
- **\text{Data} \text{Volume}:** ~500 \text{rows} (8 \text{parties}  \times  60+ \text{months})
- **\text{Refresh} \text{Frequency}:** \text{Real}-\text{time} (\text{recalculated} \text{on} \text{query})
- **\text{Optimization}:** \text{Strong} \text{candidate} \text{for} \text{materialization}

#### \text{Data} \text{Sources}

- **\text{Vote} \text{Data}:** $view_riksdagen_vote_data_ballot_party_summary_daily` (aggregated monthly)
- **Document Data:** `view_riksdagen_party_document_summary`
- **Member Data:** `view_riksdagen_party` (active member counts)

#### Dependencies

- Depends on: Multiple vote and document summary views
- Used by: `view_riksdagen_intelligence_dashboard`, coalition analysis tools

#### Risk Rules Supported

From [RISK_RULES_INTOP_OSINT.md](RISK_RULES_INTOP_OSINT.md):
- **PartyWeakSupport (Y-01)**: Effectiveness and trend metrics
- **PartyCoalitionUnstable (Y-02)**: Coalition potential score
- **PartyLowDiscipline (Y-03)**: `avg_party_discipline` metric
- **PartyDecliningProductivity (Y-04)**: Document trends

#### Intelligence Frameworks Applicable

From [DATA_ANALYSIS_INTOP_OSINT.md](DATA_ANALYSIS_INTOP_OSINT.md):
- **Temporal Analysis**: Time-series tracking with moving averages
- **Comparative Analysis**: Inter-party and bloc-level comparison
- **Predictive Intelligence**: Momentum analysis for forecasting
- **Coalition Analysis**: Reliability scoring for government formation

---

### view_riksdagen_coalition_alignment_matrix ⭐⭐⭐⭐⭐

**Category:** Intelligence Views (v1.29)  
**Type:** Standard View  
**Intelligence Value:** VERY HIGH - Coalition Formation Analysis  
**Changelog:** v1.29 OSINT Coalition Intelligence

#### Purpose

Party-pair voting alignment matrix tracking 2-year rolling window of shared voting patterns. Calculates alignment rates, identifies natural coalition partners, and provides automated intelligence assessment of partnership viability for government formation forecasting.

#### Key Columns

| Column | Type | Description | Example |
|--------|------|-------------|---------|
| `party_1` | VARCHAR(50) | First party | 'M' |
| `party_2` | VARCHAR(50) | Second party | 'KD' |
| `alignment_rate` | NUMERIC(5,2) | Percentage voting together | 89.50 |
| `shared_votes` | INTEGER | Number of ballots both voted on | 1247 |
| `votes_aligned` | INTEGER | Times voted the same way | 1116 |
| `votes_opposed` | INTEGER | Times voted oppositely | 131 |
| `coalition_likelihood` | VARCHAR(50) | Viability classification | 'VERY_HIGH' |
| `bloc_relationship` | VARCHAR(50) | Bloc classification | 'RIGHT_BLOC_INTERNAL' |
| `intelligence_assessment` | TEXT | Automated commentary | 'Strong natural coalition partners...' |

#### Coalition Likelihood Classifications

| Classification | Alignment Rate | Description |
|---------------|----------------|-------------|
| **VERY_HIGH** | ≥ 80% | Strong natural partners, consistent alignment |
| **HIGH** | ≥ 65% | Viable coalition, good compatibility |
| **MEDIUM** | ≥ 50% | Possible with negotiations, some differences |
| **LOW** | ≥ 35% | Difficult coalition, fundamental disagreements |
| **VERY_LOW** | < 35% | Coalition highly unlikely, opposing ideologies |

#### Bloc Relationship Types

- **LEFT_BLOC_INTERNAL**: S-V, S-MP, V-MP (traditional red-green alliances)
- **RIGHT_BLOC_INTERNAL**: M-KD, M-L, M-C, KD-L, etc. (Alliance partners)
- **CROSS_BLOC**: S-C, M-S (centrist cooperation, "Grand Coalition")
- **SD_RELATIONSHIP**: Any party paired with SD (Sweden Democrats)
- **UNAFFILIATED**: Other combinations

#### Example Queries

**1. Current Coalition Viability Matrix (All Party Pairs)**

```sql
SELECT
    party_1,
    party_2,
    ROUND(alignment_rate, 2) AS alignment,
    shared_votes,
    coalition_likelihood,
    bloc_relationship
FROM view_riksdagen_coalition_alignment_matrix
WHERE shared_votes >= 100  -- Sufficient sample size
ORDER BY alignment_rate DESC;

Output:

 party_1 | party_2 | alignment | shared_votes | coalition_likelihood | bloc_relationship
---------+---------+-----------+--------------+---------------------+-------------------
 M       | KD      |     89.50 |         1247 | VERY_HIGH           | RIGHT_BLOC_INTERNAL
 S       | V       |     87.20 |         1198 | VERY_HIGH           | LEFT_BLOC_INTERNAL
 KD      | L       |     82.30 |         1156 | VERY_HIGH           | RIGHT_BLOC_INTERNAL

2. Potential Government Coalitions (Majority Analysis)

WITH party_seats AS (
    SELECT party, member_count AS seats
    FROM view_riksdagen_party
    WHERE member_count > 0
),
coalition_combos AS (
    SELECT
        cam.party_1,
        cam.party_2,
        ps1.seats AS seats_1,
        ps2.seats AS seats_2,
        ps1.seats + ps2.seats AS combined_seats,
        cam.alignment_rate,
        cam.coalition_likelihood
    FROM view_riksdagen_coalition_alignment_matrix cam
    JOIN party_seats ps1 ON ps1.party = cam.party_1
    JOIN party_seats ps2 ON ps2.party = cam.party_2
    WHERE cam.coalition_likelihood IN ('VERY_HIGH', 'HIGH')
)
SELECT
    party_1,
    party_2,
    combined_seats,
    ROUND(alignment_rate, 2) AS alignment,
    coalition_likelihood,
    CASE
        WHEN combined_seats >= 175 THEN 'MAJORITY'
        WHEN combined_seats >= 165 THEN 'NEAR_MAJORITY'
        ELSE 'INSUFFICIENT'
    END AS government_viability
FROM coalition_combos
WHERE combined_seats >= 165
ORDER BY combined_seats DESC, alignment_rate DESC;

3. Cross-Bloc Cooperation Opportunities

SELECT
    party_1,
    party_2,
    ROUND(alignment_rate, 2) AS alignment,
    shared_votes,
    coalition_likelihood,
    intelligence_assessment
FROM view_riksdagen_coalition_alignment_matrix
WHERE bloc_relationship = 'CROSS_BLOC'
    AND alignment_rate >= 50  -- Realistic cooperation threshold
ORDER BY alignment_rate DESC;

4. Sweden Democrats Coalition Potential

SELECT
    CASE
        WHEN party_1 = 'SD' THEN party_2
        ELSE party_1
    END AS other_party,
    ROUND(alignment_rate, 2) AS alignment_with_sd,
    shared_votes,
    coalition_likelihood,
    intelligence_assessment
FROM view_riksdagen_coalition_alignment_matrix
WHERE party_1 = 'SD' OR party_2 = 'SD'
ORDER BY alignment_rate DESC;

5. Coalition Stability Trends (Requires Time-Series Extension)

-- Current vs Historical Comparison (if view extended with time dimension)
SELECT
    party_1,
    party_2,
    ROUND(alignment_rate, 2) AS current_alignment,
    LAG(ROUND(alignment_rate, 2)) OVER (
        PARTITION BY party_1, party_2 
        ORDER BY analysis_date
    ) AS previous_alignment,
    ROUND(alignment_rate - LAG(alignment_rate) OVER (
        PARTITION BY party_1, party_2 
        ORDER BY analysis_date
    ), 2) AS alignment_change
FROM view_riksdagen_coalition_alignment_matrix
WHERE analysis_date >= CURRENT_DATE - INTERVAL '24 months'
ORDER BY party_1, party_2, analysis_date DESC;
``$

#### \text{Performance} \text{Characteristics}

- **\text{Query} \text{Time}:** 200-400\text{ms} (\text{complex} \text{cross}-\text{join} \text{aggregation})
- **\text{Indexes} \text{Used}:** \text{Vote} \text{summary} \text{indexes}, \text{party} \text{indexes}
- **\text{Data} \text{Volume}:** ~36 \text{rows} (8 \text{active} \text{parties}  \times  8 = 64 \text{pairs}, \text{minus} \text{self}-\text{pairs})
- **\text{Refresh} \text{Frequency}:** \text{Daily} \text{recalculation} \text{recommended}
- **\text{Optimization}:** \text{Strong} \text{candidate} \text{for} \text{materialized} \text{view}

#### \text{Data} \text{Sources}

- **\text{Primary} \text{View}:** $view_riksdagen_vote_data_ballot_party_summary_daily`
- **Calculation:** Cross-join of parties with alignment calculation
- **Window:** Rolling 2-year period for stability

#### Dependencies

- Depends on: `view_riksdagen_vote_data_ballot_party_summary_daily`
- Used by: `view_riksdagen_intelligence_dashboard`, coalition forecasting tools

#### Risk Rules Supported

From [RISK_RULES_INTOP_OSINT.md](RISK_RULES_INTOP_OSINT.md):
- **PartyCoalitionUnstable (Y-02)**: Alignment metrics for stability assessment
- **PartyIsolated (Y-05)**: Identifies parties with low alignment across board

#### Intelligence Frameworks Applicable

From [DATA_ANALYSIS_INTOP_OSINT.md](DATA_ANALYSIS_INTOP_OSINT.md):
- **Coalition Analysis**: Primary use case - government formation forecasting
- **Network Analysis**: Party relationship mapping
- **Predictive Intelligence**: Election outcome scenario planning
- **Comparative Analysis**: Bloc cohesion vs. cross-bloc cooperation

---

### view_riksdagen_party_decision_flow ⭐⭐⭐⭐⭐

**Category:** Decision Flow Views (NEW in v1.35)  
**Type:** Standard View  
**Intelligence Value:** VERY HIGH - Party Legislative Effectiveness  
**Changelog:** v1.35 Party Decision Flow Analysis

#### Purpose

Aggregates proposal decision data by party, enabling analysis of party-level legislative effectiveness, coalition alignment on proposals, committee influence, and temporal decision patterns. Provides comprehensive party scorecards showing success rates in getting proposals approved or rejected.

#### Key Columns

| Column | Type | Description | Example |
|--------|------|-------------|---------|
| `party` | VARCHAR(255) | Party short code | 'S', 'M', 'SD' |
| `committee` | VARCHAR(255) | Committee processing proposal | 'Finansutskottet' |
| `decision_type` | VARCHAR(255) | Type of decision | 'Utlåtande', 'Betänkande' |
| `committee_org` | VARCHAR(255) | Committee organization code | 'FiU' |
| `decision_month` | TIMESTAMP | Month of decision (truncated) | '2024-10-01 00:00:00' |
| `decision_year` | NUMERIC | Year of decision | 2024 |
| `decision_month_num` | NUMERIC | Month number (1-12) | 10 |
| `total_proposals` | BIGINT | Total proposals processed | 45 |
| `approved_proposals` | BIGINT | Proposals approved (bifall) | 32 |
| `rejected_proposals` | BIGINT | Proposals rejected (avslag) | 10 |
| `referred_back_proposals` | BIGINT | Proposals referred back | 2 |
| `other_decisions` | BIGINT | Other decision outcomes | 1 |
| `approval_rate` | NUMERIC(5,2) | Percentage approved | 71.11 |
| `rejection_rate` | NUMERIC(5,2) | Percentage rejected | 22.22 |
| `earliest_decision_date` | DATE | First decision in period | '2024-10-01' |
| `latest_decision_date` | DATE | Last decision in period | '2024-10-31' |

#### Swedish Decision Terms

The view recognizes Swedish parliamentary decision terminology:

| Swedish Term | English | Aggregation Column |
|-------------|---------|-------------------|
| **Bifall** / Bifalla / Godkänt | Approval/Accepted | `approved_proposals` |
| **Avslag** / Avslå | Rejection/Denied | `rejected_proposals` |
| **Återförvisning** / Återförvisa | Referral back to committee | `referred_back_proposals` |

#### Example Queries

**1. Party Success Rates (Current Year)**

```sql
SELECT
    party,
    SUM(total_proposals) AS total,
    SUM(approved_proposals) AS approved,
    SUM(rejected_proposals) AS rejected,
    ROUND(
        100.0 * SUM(approved_proposals) / NULLIF(SUM(total_proposals), 0),
        2
    ) AS overall_approval_rate
FROM view_riksdagen_party_decision_flow
WHERE decision_year = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY party
ORDER BY overall_approval_rate DESC;

Output:

 party | total | approved | rejected | overall_approval_rate
-------+-------+----------+----------+---------------------
 S     |   234 |      178 |       42 |               76.07
 M     |   198 |      145 |       38 |               73.23
 SD    |   156 |      102 |       45 |               65.38

2. Committee Effectiveness by Party

SELECT
    committee,
    party,
    SUM(total_proposals) AS proposals,
    SUM(approved_proposals) AS approved,
    ROUND(
        100.0 * SUM(approved_proposals) / NULLIF(SUM(total_proposals), 0),
        2
    ) AS approval_rate
FROM view_riksdagen_party_decision_flow
WHERE decision_year >= EXTRACT(YEAR FROM CURRENT_DATE) - 1
GROUP BY committee, party
HAVING SUM(total_proposals) >= 10  -- Minimum threshold for statistical relevance
ORDER BY committee, approval_rate DESC;

3. Temporal Trends - Party Success Over Time

SELECT
    party,
    decision_year,
    decision_month_num,
    TO_CHAR(decision_month, 'YYYY-MM') AS month,
    SUM(total_proposals) AS proposals,
    SUM(approved_proposals) AS approved,
    ROUND(AVG(approval_rate), 2) AS avg_approval_rate
FROM view_riksdagen_party_decision_flow
WHERE decision_month >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY party, decision_year, decision_month_num, decision_month
ORDER BY party, decision_year DESC, decision_month_num DESC;

4. Coalition Alignment Analysis

Compare decision patterns between government and opposition parties:

WITH party_decisions AS (
    SELECT
        party,
        SUM(total_proposals) AS total,
        SUM(approved_proposals) AS approved,
        SUM(rejected_proposals) AS rejected,
        ROUND(
            100.0 * SUM(approved_proposals) / NULLIF(SUM(total_proposals), 0),
            2
        ) AS approval_rate
    FROM view_riksdagen_party_decision_flow
    WHERE decision_year = EXTRACT(YEAR FROM CURRENT_DATE)
    GROUP BY party
)
SELECT
    pd.*,
    CASE
        WHEN party IN ('M', 'SD', 'KD', 'L') THEN 'GOVERNMENT'
        WHEN party IN ('S', 'V', 'MP', 'C') THEN 'OPPOSITION'
        ELSE 'OTHER'
    END AS bloc,
    ROUND(approval_rate - (SELECT AVG(approval_rate) FROM party_decisions), 2) AS vs_avg
FROM party_decisions pd
ORDER BY bloc, approval_rate DESC;

5. Committee Influence by Party (Which committees favor which parties?)

SELECT
    committee,
    party,
    SUM(total_proposals) AS proposals,
    ROUND(AVG(approval_rate), 2) AS avg_approval_rate,
    RANK() OVER (PARTITION BY committee ORDER BY AVG(approval_rate) DESC) AS party_rank
FROM view_riksdagen_party_decision_flow
WHERE decision_year >= EXTRACT(YEAR FROM CURRENT_DATE) - 2
GROUP BY committee, party
HAVING SUM(total_proposals) >= 5
ORDER BY committee, party_rank;

6. Decision Pattern Analysis (Approval vs Rejection Trends)

SELECT
    decision_year,
    party,
    SUM(total_proposals) AS total,
    SUM(approved_proposals) AS approved,
    SUM(rejected_proposals) AS rejected,
    SUM(referred_back_proposals) AS referred_back,
    ROUND(100.0 * SUM(approved_proposals) / NULLIF(SUM(total_proposals), 0), 2) AS approval_pct,
    ROUND(100.0 * SUM(rejected_proposals) / NULLIF(SUM(total_proposals), 0), 2) AS rejection_pct
FROM view_riksdagen_party_decision_flow
WHERE decision_year >= 2020
GROUP BY decision_year, party
ORDER BY decision_year DESC, party;

7. Government vs Opposition Success Rates

WITH bloc_classification AS (
    SELECT
        *,
        CASE
            WHEN party IN ('M', 'SD', 'KD', 'L') THEN 'GOVERNMENT_BLOC'
            WHEN party IN ('S', 'V', 'MP', 'C') THEN 'OPPOSITION_BLOC'
            ELSE 'INDEPENDENT'
        END AS political_bloc
    FROM view_riksdagen_party_decision_flow
    WHERE decision_year = EXTRACT(YEAR FROM CURRENT_DATE)
)
SELECT
    political_bloc,
    COUNT(DISTINCT party) AS party_count,
    SUM(total_proposals) AS total_proposals,
    SUM(approved_proposals) AS approved_proposals,
    ROUND(
        100.0 * SUM(approved_proposals) / NULLIF(SUM(total_proposals), 0),
        2
    ) AS bloc_approval_rate
FROM bloc_classification
GROUP BY political_bloc
ORDER BY bloc_approval_rate DESC;

Performance Characteristics

Query Time: 50-200ms (depends on date range and aggregation)
Indexes: Base table indexes on proposal data and document data
Data Volume: Typically 500-2000 rows per year (varies by proposal activity)
Refresh: Real-time (standard view)
Optimization: For heavy analytical use, consider materializing

Data Sources

Primary Tables:

document_proposal_data - Proposal details and decisions
document_proposal_container - Linkage structure
document_status_container - Status information
document_data - Document metadata and dates
document_person_reference_da_0 - Party attribution

Join Path:

document_proposal_data
  → document_proposal_container (proposal_document_proposal_c_0)
  → document_status_container (document_proposal_document_s_0)
  → document_data (document_document_status_con_0)
  ← document_person_reference_co_0 (document_person_reference_co_1)
  ← document_person_reference_da_0 (document_person_reference_li_1)

Dependencies

Depends on: Base tables (no view dependencies)
Used by: Party scorecards, coalition analysis, committee effectiveness dashboards

Risk Rules Supported

PartyWeakSupport (Y-01): Low approval rates indicate weak legislative position
PartyCoalitionUnstable (Y-02): Divergent approval rates indicate coalition stress
PartyLowDiscipline (Y-03): Inconsistent decision patterns suggest internal divisions
PartyIsolated (Y-05): Low approval rates across committees indicate marginalization

Intelligence Frameworks Applicable

Framework	Application	Example Analysis
Temporal Analysis	Track party effectiveness trends over time	Monthly approval rate trajectories
Comparative Analysis	Party-to-party effectiveness comparison	Government vs opposition success rates
Pattern Recognition	Identify committee specializations	Which parties succeed in which committees
Predictive Intelligence	Forecast proposal outcomes	Based on party sponsorship and committee
Coalition Analysis	Assess coalition alignment on decisions	Do coalition partners have similar approval rates?

Intelligence Products Generated:

🏆 Party Legislative Scorecards - Success rates, committee effectiveness
🤝 Coalition Alignment Reports - Decision pattern convergence/divergence
📊 Committee Influence Maps - Which parties control which committees
📈 Temporal Effectiveness Trends - Is party legislative power growing or declining?

Data Flow: See Intelligence Data Flow Map - Party Views for complete data pipeline.

Integration with Product Features

Party Dashboard (Product Line 1): Legislative effectiveness metrics
Coalition Analysis (Product Line 2): Formation scenario modeling
Committee Analytics (Product Line 2): Committee influence assessment
Comparative Analytics (Product Line 2): Party-to-party benchmarking

Intelligence Applications

1. Party Scorecards

Legislative effectiveness tracking (approval vs rejection rates)
Committee specialization identification
Temporal trend analysis (improving or declining)

2. Coalition Analysis

Government formation scenarios (which parties work well together on proposals?)
Coalition stability assessment (diverging approval rates = coalition stress)
Cross-bloc cooperation opportunities

3. Committee Effectiveness

Party influence in different committees
Committee chair performance (approval rates under their leadership)
Proposal routing optimization (which committee most favorable for party's proposals)

4. Strategic Intelligence

Early warning: Declining approval rates signal weakening position
Opportunity identification: High approval rates in specific committees
Opposition strategy: Which proposals have best chance of success?

5. OSINT Research Applications

Academic research on legislative effectiveness
Journalism: Data-driven political analysis
Citizen engagement: Understanding how parties perform on proposals
Think tanks: Evidence-based policy recommendations

Notes

Empty Data Warning: View will be empty if document_proposal_data has no records with party references
Swedish Language: Decision terms use official Riksdag Swedish terminology
Party Attribution: Requires person reference data linking documents to parties
Historical Analysis: Effective for analyzing any time period with available data

view_riksdagen_party_summary ⭐⭐⭐⭐

Category: Party Views (v1.61 - Recreated)
Type: Standard View
Intelligence Value: HIGH - Party Assignment & Document Aggregation
Changelog: v1.61 Recreation after v1.6 drop

📋 Purpose

Aggregates assignment data and document statistics at party level. Provides comprehensive party-level metrics including assignments, days served across different roles, active members, document production, and collaboration patterns. Foundation view for party comparison and performance tracking.

Note: Originally created in early versions, dropped in v1.6, and recreated in v1.61 after JPA entity mismatch discovery.

📊 Key Columns

Column	Type	Description	Example
`party`	VARCHAR(50)	Party short code (PK)	'S', 'M', 'SD'
`first_assignment_date`	DATE	First party assignment	'2002-10-01'
`last_assignment_date`	DATE	Most recent assignment	'2024-12-15'
`total_assignments`	BIGINT	All assignments across members	2847
`current_assignments`	BIGINT	Active assignments	156
`total_days_served`	BIGINT	Total service days (all roles)	890456
`total_days_served_parliament`	BIGINT	Parliamentary service days	456789
`total_days_served_committee`	BIGINT	Committee service days	234567
`total_days_served_government`	BIGINT	Government service days	123456
`total_days_served_eu`	BIGINT	EU representative days	12345
`total_days_served_speaker`	BIGINT	Speaker role days	5678
`total_days_served_party`	BIGINT	Party leadership days	45678
`active`	BOOLEAN	Has any active members	true
`active_parliament`	BOOLEAN	Has parliamentary members	true
`active_government`	BOOLEAN	Has government members	false
`active_committee`	BOOLEAN	Has committee members	true
`total_active`	BIGINT	Count of active members	68
`total_active_parliament`	BIGINT	Active parliamentarians	68
`total_active_government`	BIGINT	Active government members	0
`total_active_committee`	BIGINT	Active committee members	102
`total_documents`	BIGINT	Documents produced	8542
`avg_documents_per_member`	NUMERIC	Avg docs per member	125.62
`total_party_motions`	BIGINT	Party-wide motions	234
`total_individual_motions`	BIGINT	Individual member motions	5678
`very_high_activity_members`	BIGINT	Members with >100 docs	12
`high_activity_members`	BIGINT	Members with 50-100 docs	23
`medium_activity_members`	BIGINT	Members with 10-49 docs	28
`low_activity_members`	BIGINT	Members with <10 docs	5
`currently_active_members`	BIGINT	Active in last year	64
`total_documents_last_year`	BIGINT	Docs last 12 months	1245
`avg_documents_last_year`	NUMERIC	Avg docs/member last year	19.45

💡 Example SQL Queries

1. Party Activity Comparison

SELECT
    party,
    total_active_parliament AS current_seats,
    total_documents AS lifetime_docs,
    total_documents_last_year AS recent_docs,
    ROUND(avg_documents_per_member, 2) AS productivity,
    very_high_activity_members + high_activity_members AS productive_members,
    ROUND(100.0 * currently_active_members / NULLIF(total_active, 0), 2) AS active_pct
FROM view_riksdagen_party_summary
WHERE active = true
ORDER BY current_seats DESC;

2. Government vs Opposition Experience

SELECT
    party,
    active_parliament,
    active_government,
    total_days_served_government AS govt_experience_days,
    ROUND(total_days_served_government::NUMERIC / 365.25, 1) AS govt_experience_years,
    total_days_served_parliament AS parliament_days,
    ROUND(100.0 * total_days_served_government / NULLIF(total_days_served_parliament, 0), 2) AS govt_pct
FROM view_riksdagen_party_summary
WHERE active = true
ORDER BY govt_experience_days DESC;

3. Member Activity Distribution

SELECT
    party,
    total_active AS members,
    very_high_activity_members AS very_high,
    high_activity_members AS high,
    medium_activity_members AS medium,
    low_activity_members AS low,
    ROUND(100.0 * very_high_activity_members / NULLIF(total_active, 0), 1) AS elite_pct
FROM view_riksdagen_party_summary
WHERE active = true
ORDER BY elite_pct DESC;

⚡ Performance Characteristics

Query Time: <10ms (small result set, indexed on PK)
Indexes Used: Primary key on party
Data Volume: ~10-15 rows (active parties)
Refresh Frequency: Real-time (standard view)
Dependencies: Aggregates from view_riksdagen_politician and document_data

🔗 Dependencies

Upstream:

view_riksdagen_politician - Individual politician aggregations
document_data - Document production metrics
document_status_container - Document linkage
document_person_reference_* - Party attribution

Downstream:

Used by other party comparison queries
Foundation for party performance analysis

🎯 Framework Integration

Comparative Analysis:

Party-to-party productivity comparison
Government vs opposition experience metrics
Member activity distribution analysis

Temporal Analysis:

Service duration tracking across roles
Historical vs current activity patterns

Pattern Recognition:

High vs low productivity member identification
Party activity classification patterns

🎯 Use Cases

Party Scorecard Generation - Comprehensive party profiles with assignments, activity, and productivity
Experience Assessment - Track party expertise in government, committees, parliament
Member Activity Analysis - Identify high-performing and low-performing member distributions
Coalition Capacity Assessment - Evaluate party governance experience for coalition formation

view_riksdagen_party_longitudinal_performance ⭐⭐⭐⭐⭐

Category: Party Views (v1.61 - Recreated from v1.53)
Type: Standard View
Intelligence Value: VERY HIGH - Advanced Longitudinal Analysis with Window Functions
Changelog: v1.53 Original, v1.61 Recreated

📋 Purpose

Tracks party performance evolution across election cycles (2002-2026+) with semester granularity, employing advanced window functions (RANK, PERCENT_RANK, NTILE, LAG, LEAD, STDDEV_POP) for trend detection, performance tier classification, predictive forecasting, and volatility assessment. Enables cross-cycle comparative analysis with Swedish parliamentary context (autumn/spring semesters, pre-election dynamics).

Key Innovation: Combines 7 types of window functions with 3-semester moving averages, z-score calculations, and composite performance indices for sophisticated trend analysis.

🔬 Statistical Methodology

Window Function Coverage:

RANK() - Party ranking by win rate, participation, size, approval, productivity, discipline (6 dimensions)
PERCENT_RANK() - Percentile positioning (0.0-1.0) for comparative analysis
NTILE(4) - Quartile-based performance tiers (1=top 25%, 4=bottom 25%)
LAG() - Previous semester metrics for trend detection (6 metrics tracked)
LEAD() - Next semester metrics for predictive forecasting (3 forward-looking metrics)
STDDEV_POP() - Volatility measurement (sector-wide and party-specific)
AVG() ROWS BETWEEN 2 PRECEDING - 3-semester moving averages for smoothing

Composite Indices:

Composite Performance Index: Win rate (35%) + Participation (25%) + Approval (20%) + Size (10%) + Productivity (10%)
Discipline Effectiveness Score: Participation (50%) + Inverse rebel rate (50%)
Legislative Effectiveness Index: Win rate (35%) + Participation (25%) + Approval (25%) + Discipline (15%)

📊 Key Columns (70 Total)

Primary Keys (Composite):

Column	Type	Description	Example
`party`	VARCHAR(50)	Party code	'S'
`election_cycle_id`	TEXT	Election cycle	'2018-2021'
`semester`	TEXT	Semester	'autumn', 'spring'

Core Metrics (14 columns):

Column	Type	Description	Example
`cycle_year`	INTEGER	Year in 4-year cycle (1-4)	3
`calendar_year`	INTEGER	Actual year	2024
`total_ballots`	BIGINT	Ballots in semester	847
`participation_rate`	NUMERIC	Attendance %	92.5
`win_rate`	NUMERIC	Win % on ballots	67.8
`approval_rate`	NUMERIC	Decision approval %	71.2
`active_members`	BIGINT	Member count	68
`documents_last_year`	BIGINT	Documents produced	1245
`avg_rebel_rate`	NUMERIC	Party dissent rate	3.2

Ranking Metrics (12 columns - RANK & PERCENT_RANK):

rank_by_win_rate, rank_by_participation, rank_by_size, rank_by_approval, rank_by_productivity, rank_by_discipline
percentile_win_rate, percentile_participation, percentile_approval, percentile_productivity
quartile_by_win_rate, quartile_by_overall_performance

Temporal Metrics (12 columns - LAG & LEAD):

prev_semester_win_rate, prev_semester_participation, prev_semester_members, prev_semester_approval
next_semester_win_rate, next_semester_participation, next_semester_members
Plus 5 more LAG metrics

Change Metrics (7 columns):

win_rate_change_absolute, win_rate_change_pct, participation_change_absolute
membership_change, approval_rate_change, documents_change, discipline_change

Trajectory Classifications (2 columns):

trajectory_win_rate: ASCENDING, RECOVERING, STABLE, DECLINING, DESCENDING, BASELINE
trajectory_participation: IMPROVING, STABLE, DECLINING, BASELINE

Composite Scores (3 columns):

composite_performance_index (0-100)
discipline_effectiveness_score (0-100)
legislative_effectiveness_score (0-100)

Volatility Metrics (4 columns + 2 classifications):

stddev_win_rate_sector, stddev_win_rate_party, stddev_participation_sector, stddev_participation_party
volatility_classification: HIGH_VOLATILITY, MODERATE_VOLATILITY, LOW_VOLATILITY
stability_classification: UNSTABLE, MODERATELY_STABLE, STABLE

Predictive Indicators (4 columns):

forecast_trend: EXPECTED_IMPROVEMENT, EXPECTED_DECLINE, EXPECTED_STABLE, NO_FORECAST
trend_deviation_from_ma (vs 3-semester moving average)
trend_position: UNDERPERFORMING_VS_TREND, ON_TREND, OVERPERFORMING_VS_TREND
trajectory_confidence_score (0-100)

Performance Tiers (2 columns):

performance_tier: ELITE_PERFORMER, STRONG_PERFORMER, MODERATE_PERFORMER, WEAK_PERFORMER
productivity_tier: HIGHLY_PRODUCTIVE, MODERATELY_PRODUCTIVE, LOW_PRODUCTIVITY, VERY_LOW_PRODUCTIVITY

Early Warning Flags (1 column):

early_warning_flag: CRITICAL_DECLINE, MODERATE_DECLINE, CRITICAL_PARTICIPATION_DROP, MODERATE_PARTICIPATION_DROP, NORMAL

Election Context Flags (3 columns):

is_pre_election_spring, is_election_autumn, is_election_cycle_end

💡 Example SQL Queries

1. Current Performance Rankings with Trajectory

SELECT
    party,
    semester,
    win_rate,
    rank_by_win_rate,
    performance_tier,
    trajectory_win_rate,
    forecast_trend,
    ROUND(composite_performance_index, 2) AS performance_index
FROM view_riksdagen_party_longitudinal_performance
WHERE election_cycle_id = '2022-2025'
    AND calendar_year = 2024
    AND semester = 'autumn'
ORDER BY rank_by_win_rate;

2. Identify Rising Stars (Improving Performance)

SELECT
    party,
    election_cycle_id,
    calendar_year,
    semester,
    win_rate,
    win_rate_change_absolute,
    trajectory_win_rate,
    performance_tier,
    ROUND(momentum_z_score_win_rate, 2) AS momentum_significance
FROM view_riksdagen_party_longitudinal_performance
WHERE trajectory_win_rate IN ('ASCENDING', 'RECOVERING')
    AND calendar_year >= 2022
ORDER BY win_rate_change_absolute DESC
LIMIT 20;

3. Volatility Analysis (High-Risk Parties)

SELECT
    party,
    ROUND(AVG(win_rate), 2) AS avg_win_rate,
    ROUND(MAX(stddev_win_rate_party), 2) AS volatility,
    volatility_classification,
    COUNT(*) AS semesters_tracked,
    STRING_AGG(DISTINCT early_warning_flag, ', ') AS warnings
FROM view_riksdagen_party_longitudinal_performance
WHERE calendar_year >= 2020
GROUP BY party, volatility_classification
ORDER BY volatility DESC;

4. Pre-Election Performance Surges

SELECT
    party,
    election_cycle_id,
    calendar_year,
    win_rate,
    prev_semester_win_rate,
    win_rate_change_absolute AS pre_election_surge,
    participation_rate,
    documents_last_year
FROM view_riksdagen_party_longitudinal_performance
WHERE is_pre_election_spring = true
    AND calendar_year >= 2014
ORDER BY pre_election_surge DESC;

5. Performance Tier Transitions

WITH tier_changes AS (
    SELECT
        party,
        calendar_year,
        semester,
        performance_tier,
        LAG(performance_tier) OVER (PARTITION BY party ORDER BY calendar_year, semester) AS prev_tier
    FROM view_riksdagen_party_longitudinal_performance
    WHERE calendar_year >= 2018
)
SELECT
    party,
    calendar_year,
    semester,
    prev_tier || ' → ' || performance_tier AS tier_transition,
    CASE
        WHEN performance_tier < prev_tier THEN 'PROMOTED'
        WHEN performance_tier > prev_tier THEN 'DEMOTED'
        ELSE 'STABLE'
    END AS direction
FROM tier_changes
WHERE prev_tier IS NOT NULL
    AND performance_tier != prev_tier
ORDER BY calendar_year DESC, party;

6. Predictive Accuracy Assessment

SELECT
    party,
    calendar_year,
    semester,
    forecast_trend,
    CASE
        WHEN forecast_trend = 'EXPECTED_IMPROVEMENT' AND next_semester_win_rate > win_rate THEN 'CORRECT'
        WHEN forecast_trend = 'EXPECTED_DECLINE' AND next_semester_win_rate < win_rate THEN 'CORRECT'
        WHEN forecast_trend = 'EXPECTED_STABLE' AND ABS(next_semester_win_rate - win_rate) < 3 THEN 'CORRECT'
        ELSE 'INCORRECT'
    END AS forecast_accuracy
FROM view_riksdagen_party_longitudinal_performance
WHERE next_semester_win_rate IS NOT NULL
    AND calendar_year >= 2020;
``$

#### ⚡ \text{Performance} \text{Characteristics}

- **\text{Query} \text{Time}:** 100-300\text{ms} (\text{complex} \text{window} \text{functions})
- **\text{Indexes} \text{Suggested}:** \text{Composite} \text{index} \text{on} (\text{party}, \text{election\_cycle\_id}, \text{cycle\_year}, \text{semester})
- **\text{Data} \text{Volume}:** ~800-1200 \text{rows} (8-10 \text{parties}  \times  6 \text{cycles}  \times  8 \text{semesters}/\text{cycle})
- **\text{Refresh} \text{Frequency}:** \text{Real}-\text{time} (\text{standard} \text{view})
- **\text{Optimization} \text{Note}:** \text{Consider} \text{materialization} \text{for} \text{heavy} \text{analytical} \text{workloads}

#### 🔗 \text{Dependencies}

**\text{Upstream}:**
- $view_riksdagen_vote_data_ballot_party_summary_annual` - Core voting metrics
- `view_party_performance_metrics` - Current performance scores

**Downstream:**
- Party trend dashboards
- Election forecasting models
- Coalition stability analysis

#### 🎯 Framework Integration

**Temporal Analysis:**
- Cross-cycle performance evolution
- Semester-to-semester trends
- Pre-election behavioral shifts

**Comparative Analysis:**
- Party-to-party ranking across 6 dimensions
- Percentile-based competitive positioning
- Quartile tier classification

**Pattern Recognition:**
- Trajectory pattern detection (ASCENDING, DECLINING, STABLE)
- Volatility classification (HIGH, MODERATE, LOW)
- Early warning signal identification

**Predictive Intelligence:**
- Next-semester performance forecasting
- Trajectory confidence scoring
- Trend deviation alerts

#### 🎯 Use Cases

1. **Election Forecasting** - Predict party performance based on historical trajectories and moving averages
2. **Coalition Planning** - Assess party reliability via volatility metrics and discipline scores
3. **Performance Benchmarking** - Compare parties using percentile ranks and composite indices
4. **Trend Analysis** - Detect multi-semester trends with LAG/LEAD analysis and moving averages
5. **Risk Assessment** - Identify high-volatility parties with early warning flags
6. **Pre-Election Intelligence** - Track behavioral changes in pre-election semesters

---

### view_riksdagen_party_coalition_evolution ⭐⭐⭐⭐⭐

**Category:** Party Views (v1.61 - Recreated from v1.53)  
**Type:** Standard View  
**Intelligence Value:** VERY HIGH - Coalition Dynamics & Alliance Tracking  
**Changelog:** v1.53 Original, v1.61 Recreated

#### 📋 Purpose

Tracks party-pair alliance and coalition behavior across election cycles with semester granularity. Analyzes voting alignment, coalition strength, breakup risk, strategic shifts, and volatility through advanced window functions. Enables coalition formation forecasting and alliance stability assessment.

**Key Innovation:** Pairwise party analysis with alignment rate calculation, coalition strength rankings, realignment detection, and breakup risk scoring. Employs window functions for temporal tracking and volatility measurement.

#### 🔬 Statistical Methodology

**Alignment Calculation:**
``$
\text{Alignment} \text{Rate} = (\text{Aligned} \text{Ballots} / \text{Total} \text{Joint} \text{Ballots})  \times  100
\text{Aligned} \text{Ballot} = \text{Both} \text{parties} \text{vote} \text{same} \text{direction} (\text{both} \text{YES} \text{or} \text{both} \text{NO})
$``

**Vote Divergence:**

Avg Vote Divergence = AVG(|Party1_YES% - Party2_YES%|)


**Stability Score Formula:**
``$
\text{Stability} \text{Score} = (\text{Alignment\_Rate}  \times  40%) + 
                  ((100 - \text{Avg\_Vote\_Divergence})  \times  30%) + 
                  ((100 - \text{Vote\_Divergence\_StdDev})  \times  30%)
$``

**Breakup Risk Scoring:**
- Alliance < 50% and previously ≥ 65%: **90% risk**
- Alliance < 50% with high volatility (stddev > 15): **75% risk**
- Alliance < 65% with decline > 10 points: **60% risk**
- High volatility (stddev > 15): **50% risk**
- Alliance < 50%: **40% risk**
- Recent decline > 8 points: **30% risk**
- Otherwise: **10% risk** (baseline)

#### 📊 Key Columns (35 Total)

**Primary Keys (Composite):**
| Column | Type | Description | Example |
|--------|------|-------------|---------|
| `party_1` | VARCHAR(50) | First party (alphabetically) | 'M' |
| `party_2` | VARCHAR(50) | Second party (alphabetically) | 'S' |
| `election_cycle_id` | TEXT | Election cycle | '2018-2021' |

**Core Metrics (13 columns):**
| Column | Type | Description | Example |
|--------|------|-------------|---------|
| `semester` | TEXT | Semester (autumn/spring) | 'autumn' |
| `cycle_year` | INTEGER | Year in cycle (1-4) | 3 |
| `calendar_year` | INTEGER | Actual year | 2024 |
| `joint_voting_days` | BIGINT | Days both voted | 156 |
| `joint_ballots` | BIGINT | Total shared ballots | 847 |
| `aligned_ballots` | BIGINT | Ballots voted same | 623 |
| `alignment_rate` | NUMERIC | % of aligned votes | 73.5 |
| `avg_vote_divergence` | NUMERIC | Avg % difference | 26.5 |
| `vote_divergence_stddev` | NUMERIC | Consistency metric | 18.3 |

**Ranking Metrics (9 columns):**
- `rank_by_alignment` - Coalition strength rank (1 = strongest)
- `rank_by_activity` - Joint ballot activity rank
- `rank_by_consistency` - Vote divergence consistency rank
- `percentile_alignment` - Alignment percentile (0.0-1.0)
- `percentile_cohesion` - Cohesion percentile
- `quartile_coalition_strength` - Strength quartile (1-4)

**Temporal Metrics (10 columns - LAG & LEAD):**
- `prev_semester_alignment`, `prev_semester_joint_ballots`, `prev_semester_divergence`
- `next_semester_alignment`, `next_semester_divergence`

**Change Metrics (4 columns):**
- `alignment_change_absolute`, `alignment_change_pct`
- `activity_change`, `divergence_change`

**Coalition Classifications (7 columns):**

| Column | Type | Values | Description |
|--------|------|--------|-------------|
| `coalition_strength` | TEXT | VERY_STRONG (≥80%), STRONG (≥65%), MODERATE (≥50%), WEAK (≥35%), OPPOSITION (<35%) | Alliance strength tier |
| `coalition_trend` | TEXT | RAPIDLY_STRENGTHENING (+15), STRENGTHENING (+8), IMPROVING (+3), STABLE, DECLINING (-3), WEAKENING (-8), RAPIDLY_WEAKENING (-15) | Direction of change |
| `strategic_shift` | TEXT | COALITION_FORMATION, COALITION_BREAKUP, MAJOR_REALIGNMENT (±20), SIGNIFICANT_SHIFT (±10), MINOR_SHIFT (±5), STABLE | Major realignments |
| `volatility_classification` | TEXT | HIGHLY_VOLATILE (>15), MODERATELY_VOLATILE (>10), SLIGHTLY_VOLATILE (>5), STABLE_PAIR | Alliance stability |
| `consistency_classification` | TEXT | INCONSISTENT (>20), MODERATE (<10), HIGH_CONSISTENCY | Vote pattern consistency |
| `coalition_tier` | TEXT | ELITE_COALITION (top 25%), STRONG_TIER (50-75%), MODERATE_TIER (25-50%), WEAK_TIER (bottom 25%) | Percentile-based tier |
| `bridge_classification` | TEXT | CORE_COALITION_BRIDGE, STRONG_BRIDGE, MODERATE_BRIDGE, WEAK_BRIDGE | Network centrality |

**Predictive Metrics (7 columns):**
- `forecast_trend`: EXPECTED_STRENGTHENING, EXPECTED_WEAKENING, EXPECTED_STABLE, NO_FORECAST
- `alignment_deviation_from_ma`: Deviation from 3-semester moving average
- `trend_position`: BELOW_TREND, ON_TREND, ABOVE_TREND
- `momentum_z_score`: Statistical significance of alignment change
- `stability_score` (0-100): Composite stability index
- `breakup_risk_score` (0-100): Coalition dissolution probability
- `realignment_probability`: HIGH, MODERATE, LOW, VERY_LOW

**Network Metrics (2 columns):**
- `coalition_density_score`: Alignment × activity weight
- `bridge_classification`: Coalition network position

#### 💡 Example SQL Queries

**1. Current Coalition Landscape**

```sql
SELECT
    party_1,
    party_2,
    alignment_rate,
    coalition_strength,
    coalition_trend,
    rank_by_alignment,
    ROUND(stability_score, 2) AS stability,
    breakup_risk_score AS risk
FROM view_riksdagen_party_coalition_evolution
WHERE election_cycle_id = '2022-2025'
    AND calendar_year = 2024
    AND semester = 'autumn'
    AND coalition_strength IN ('VERY_STRONG_COALITION', 'STRONG_COALITION')
ORDER BY rank_by_alignment;

2. Coalition Formation Detection

SELECT
    party_1,
    party_2,
    calendar_year,
    semester,
    prev_semester_alignment AS before,
    alignment_rate AS after,
    alignment_change_absolute AS change,
    strategic_shift
FROM view_riksdagen_party_coalition_evolution
WHERE strategic_shift = 'COALITION_FORMATION'
    AND calendar_year >= 2018
ORDER BY calendar_year DESC, alignment_rate DESC;

3. Breakup Risk Assessment

SELECT
    party_1,
    party_2,
    election_cycle_id,
    alignment_rate,
    coalition_trend,
    breakup_risk_score,
    ROUND(stability_score, 2) AS stability,
    volatility_classification
FROM view_riksdagen_party_coalition_evolution
WHERE breakup_risk_score >= 50
    AND calendar_year = 2024
ORDER BY breakup_risk_score DESC;

4. Cross-Bloc Cooperation Analysis

WITH bloc_pairs AS (
    SELECT
        *,
        CASE
            WHEN party_1 IN ('S', 'V', 'MP') AND party_2 IN ('M', 'SD', 'KD', 'L') THEN 'CROSS_BLOC'
            WHEN party_1 IN ('S', 'V', 'MP') AND party_2 IN ('S', 'V', 'MP') THEN 'LEFT_BLOC'
            WHEN party_1 IN ('M', 'SD', 'KD', 'L') AND party_2 IN ('M', 'SD', 'KD', 'L') THEN 'RIGHT_BLOC'
        END AS bloc_type
    FROM view_riksdagen_party_coalition_evolution
    WHERE calendar_year >= 2022
)
SELECT
    bloc_type,
    ROUND(AVG(alignment_rate), 2) AS avg_alignment,
    COUNT(*) AS pair_count,
    STRING_AGG(DISTINCT party_1 || '-' || party_2, ', ') AS examples
FROM bloc_pairs
GROUP BY bloc_type
ORDER BY avg_alignment DESC;

5. Volatile Coalition Tracking

SELECT
    party_1,
    party_2,
    ROUND(AVG(alignment_rate), 2) AS avg_alignment,
    ROUND(MAX(stddev_alignment_pair), 2) AS volatility,
    COUNT(*) AS semesters,
    STRING_AGG(DISTINCT coalition_trend ORDER BY coalition_trend, ', ') AS trends_observed
FROM view_riksdagen_party_coalition_evolution
WHERE volatility_classification IN ('HIGHLY_VOLATILE_PAIR', 'MODERATELY_VOLATILE_PAIR')
    AND calendar_year >= 2018
GROUP BY party_1, party_2
ORDER BY volatility DESC;

6. Elite Coalition Bridges (Network Centrality)

SELECT
    party_1,
    party_2,
    alignment_rate,
    percentile_alignment,
    percentile_cohesion,
    bridge_classification,
    coalition_density_score
FROM view_riksdagen_party_coalition_evolution
WHERE bridge_classification IN ('CORE_COALITION_BRIDGE', 'STRONG_BRIDGE')
    AND calendar_year = 2024
ORDER BY coalition_density_score DESC;
``$

#### ⚡ \text{Performance} \text{Characteristics}

- **\text{Query} \text{Time}:** 150-400\text{ms} (\text{party} \text{pair} \text{cartesian} \text{join} + \text{window} \text{functions})
- **\text{Indexes} \text{Suggested}:** \text{Composite} \text{index} \text{on} (\text{party\_1}, \text{party\_2}, \text{election\_cycle\_id}, \text{cycle\_year}, \text{semester})
- **\text{Data} \text{Volume}:** ~2800-4200 \text{rows} (28-45 \text{party} \text{pairs}  \times  6 \text{cycles}  \times  8 \text{semesters}/\text{cycle})
- **\text{Refresh} \text{Frequency}:** \text{Real}-\text{time} (\text{standard} \text{view})
- **\text{Minimum} \text{Activity} \text{Filter}:** \text{Requires} ≥5 \text{joint} \text{voting} \text{days} \text{per} \text{semester}

#### 🔗 \text{Dependencies}

**\text{Upstream}:**
- $view_riksdagen_vote_data_ballot_party_summary_annual` - Party voting data

**Downstream:**
- Coalition formation forecasting
- Government stability analysis
- Bloc alignment tracking

#### 🎯 Framework Integration

**Network Analysis:**
- Party relationship mapping
- Coalition bridge identification
- Cross-bloc cooperation patterns

**Temporal Analysis:**
- Coalition evolution tracking
- Strategic realignment detection
- Breakup timeline analysis

**Comparative Analysis:**
- Alliance strength ranking
- Cross-cycle coalition comparison
- Bloc cohesion assessment

**Predictive Intelligence:**
- Breakup risk forecasting
- Realignment probability
- Next-semester alignment prediction

#### 🎯 Use Cases

1. **Government Formation** - Identify viable coalition combinations based on historical alignment
2. **Coalition Stability Monitoring** - Track breakup risk and volatility for existing governments
3. **Strategic Realignment Detection** - Identify major shifts in party alliances
4. **Cross-Bloc Analysis** - Detect emerging cooperation patterns between left/right blocs
5. **Network Centrality** - Identify "bridge" parties critical to coalition formation
6. **Pre-Election Intelligence** - Track alliance shifts before elections

---

### view_riksdagen_party_electoral_trends ⭐⭐⭐⭐⭐

**Category:** Party Views (v1.61 - Recreated from v1.53)  
**Type:** Standard View  
**Intelligence Value:** VERY HIGH - Electoral Performance & Seat Forecasting  
**Changelog:** v1.53 Original, v1.61 Recreated

#### 📋 Purpose

Analyzes party electoral performance across election cycles with semester granularity, tracking seat counts (via active member proxy), electoral growth trajectories, performance tiers, and election readiness. Employs window functions for trend analysis, percentile ranking, and predictive seat forecasting.

**Key Innovation:** Combines performance metrics with seat proxies and electoral forecasting. Uses moving averages, z-scores, and composite indices for multi-dimensional electoral strength assessment.

#### 🔬 Statistical Methodology

**Seat Count Proxy:**

Seat Count Proxy = Active Members (from view_party_performance_metrics) Note: Actual seat allocation data not directly available, using active member count as proxy


**Composite Electoral Score Formula:**
``$
\text{Electoral} \text{Score} = (\text{Seat\_Count\_Normalized}  \times  50%) + 
                  (\text{Win\_Rate}  \times  30%) + 
                  (\text{Documents\_Normalized}  \times  20%)

\text{where} \text{normalized} = (\text{value} / \text{max\_value\_in\_semester})  \times  \text{scale\_factor}
$``

**Legislative Effectiveness Index:**
``$
\text{Legislative} \text{Effectiveness} = (\text{Win\_Rate}  \times  35%) + 
                           (\text{Participation\_Rate}  \times  25%) + 
                           (\text{Approval\_Rate}  \times  25%) + 
                           ((100 - \text{Rebel\_Rate})  \times  15%)
$``

**Election Readiness Score** (pre-election spring semester only):
``$
\text{Readiness} = (\text{Win\_Rate}  \times  30%) + 
            (\text{Participation\_Rate}  \times  20%) + 
            (\text{Approval\_Rate}  \times  20%) + 
            (\text{Seat\_Normalized}  \times  20%) + 
            (\text{Productivity\_Normalized}  \times  10%)
$``

#### 📊 Key Columns (49 Total)

**Primary Keys (Composite):**
| Column | Type | Description | Example |
|--------|------|-------------|---------|
| `party` | VARCHAR(50) | Party code | 'S' |
| `election_cycle_id` | TEXT | Election cycle | '2018-2021' |

**Core Metrics (13 columns):**
| Column | Type | Description | Example |
|--------|------|-------------|---------|
| `semester` | TEXT | Semester | 'spring' |
| `cycle_year` | INTEGER | Year in cycle (1-4) | 4 |
| `calendar_year` | INTEGER | Actual year | 2022 |
| `ballots_participated` | BIGINT | Ballots voted on | 847 |
| `win_rate` | NUMERIC | Win % | 67.8 |
| `yes_rate` | NUMERIC | Yes vote % | 52.3 |
| `approval_rate` | NUMERIC | Approval % | 71.2 |
| `participation_rate` | NUMERIC | Attendance % | 92.5 |
| `seat_count_proxy` | BIGINT | Active members (seat estimate) | 68 |
| `documents_produced` | BIGINT | Documents authored | 1245 |
| `avg_rebel_rate` | NUMERIC | Party dissent rate | 3.2 |

**Ranking Metrics (9 columns):**
- `rank_by_seats` - Size-based ranking
- `rank_by_win_rate` - Performance ranking
- `rank_by_productivity` - Document output ranking
- `rank_by_engagement` - Participation ranking
- `rank_by_effectiveness` - Approval rate ranking
- `percentile_seats`, `percentile_win_rate`, `percentile_productivity`
- `quartile_by_size`, `quartile_by_performance`

**Temporal Metrics (11 columns - LAG & LEAD):**
- `prev_semester_seats`, `prev_semester_win_rate`, `prev_semester_documents`, `prev_semester_participation`
- `next_semester_seats`, `next_semester_win_rate`
- Plus stddev metrics: `stddev_seats_sector`, `stddev_win_rate_sector`, `stddev_seats_party`, `stddev_win_rate_party`
- Moving averages: `ma_3semester_seats`, `ma_3semester_win_rate`

**Change Metrics (6 columns):**
- `seat_change_absolute`, `seat_change_pct`
- `win_rate_change_absolute`, `win_rate_change_pct`
- `documents_change`

**Electoral Classifications (8 columns):**

| Column | Type | Values | Description |
|--------|------|--------|-------------|
| `electoral_trend` | TEXT | SURGING (+10), STRONG_GROWTH (+5), GROWTH (+1), STABLE, DECLINE (-1), STRONG_DECLINE (-5), COLLAPSING (-10) | Seat trajectory |
| `party_size_category` | TEXT | DOMINANT (≥100), MAJOR (≥75), LARGE (≥50), MEDIUM (≥30), SMALL (≥15), MINOR (<15) | Size classification |
| `volatility_classification` | TEXT | HIGHLY_VOLATILE (>15), MODERATELY_VOLATILE (>10), SLIGHTLY_VOLATILE (>5), STABLE_PARTY | Electoral stability |
| `seat_forecast` | TEXT | EXPECTED_GROWTH, EXPECTED_DECLINE, EXPECTED_SLIGHT_GROWTH, EXPECTED_SLIGHT_DECLINE, EXPECTED_STABLE, NO_FORECAST | Next semester prediction |
| `performance_forecast` | TEXT | EXPECTED_IMPROVEMENT, EXPECTED_DETERIORATION, EXPECTED_STABLE, NO_FORECAST | Performance direction |
| `trend_position_seats` | TEXT | SIGNIFICANTLY_ABOVE_TREND (+5), ABOVE_TREND (+2), ON_TREND, BELOW_TREND (-2), SIGNIFICANTLY_BELOW_TREND (-5) | vs Moving Average |
| `electoral_tier` | TEXT | TOP_ELECTORAL_TIER (top 25%), UPPER_MID_TIER (50-75%), LOWER_MID_TIER (25-50%), BOTTOM_TIER (bottom 25%) | Percentile tier |
| `electoral_warning_flag` | TEXT | CRITICAL_SEAT_LOSS, SIGNIFICANT_SEAT_LOSS, CRITICAL_PERFORMANCE_DROP, CRITICAL_ENGAGEMENT_DROP, SEAT_LOSS, PERFORMANCE_WARNING, NORMAL | Alert system |

**Composite Scores (3 columns):**
- `composite_electoral_score` (0-100): Multi-dimensional electoral strength
- `legislative_effectiveness_index` (0-100): Policy influence capacity
- `election_readiness_score` (0-100): Pre-election preparedness (spring semester only)

**Momentum & Deviation Metrics (5 columns):**
- `seat_deviation_from_ma`: Seats vs 3-semester moving average
- `momentum_z_score_seats`: Statistical significance of seat change
- `momentum_z_score_win_rate`: Statistical significance of performance change
- `projected_seat_change`: Linear projection based on LAG/LEAD
- Early warning and cycle context flags

**Election Context Flags (3 columns):**
- `is_pre_election_period` - Final spring before election
- `is_election_period` - Autumn during election year
- `is_post_election_period` - First spring after election

#### 💡 Example SQL Queries

**1. Current Electoral Standings**

```sql
SELECT
    party,
    seat_count_proxy AS current_seats,
    rank_by_seats,
    party_size_category,
    electoral_tier,
    ROUND(composite_electoral_score, 2) AS electoral_strength,
    electoral_trend,
    seat_change_absolute AS momentum
FROM view_riksdagen_party_electoral_trends
WHERE election_cycle_id = '2022-2025'
    AND calendar_year = 2024
    AND semester = 'autumn'
ORDER BY rank_by_seats;

2. Election Readiness Assessment (Pre-Election)

SELECT
    party,
    seat_count_proxy,
    ROUND(election_readiness_score, 2) AS readiness,
    ROUND(win_rate, 2) AS win_rate,
    ROUND(participation_rate, 2) AS turnout,
    documents_produced,
    electoral_trend
FROM view_riksdagen_party_electoral_trends
WHERE is_pre_election_period = true
    AND calendar_year >= 2018
ORDER BY calendar_year DESC, readiness DESC;

3. Electoral Momentum Tracking

SELECT
    party,
    calendar_year,
    seat_count_proxy AS seats,
    seat_change_absolute AS change,
    seat_change_pct AS change_pct,
    electoral_trend,
    ROUND(momentum_z_score_seats, 2) AS momentum_significance
FROM view_riksdagen_party_electoral_trends
WHERE calendar_year >= 2018
    AND electoral_trend IN ('SURGING', 'STRONG_GROWTH', 'STRONG_DECLINE', 'COLLAPSING')
ORDER BY calendar_year DESC, ABS(momentum_z_score_seats) DESC;

4. Volatility Risk Analysis

SELECT
    party,
    ROUND(AVG(seat_count_proxy), 1) AS avg_seats,
    ROUND(MAX(stddev_seats_party), 2) AS seat_volatility,
    volatility_classification,
    COUNT(*) AS semesters_tracked,
    MAX(seat_count_proxy) - MIN(seat_count_proxy) AS seat_range
FROM view_riksdagen_party_electoral_trends
WHERE calendar_year >= 2018
GROUP BY party, volatility_classification
ORDER BY seat_volatility DESC;

5. Electoral Forecast Accuracy

WITH forecasts AS (
    SELECT
        party,
        calendar_year,
        semester,
        seat_count_proxy AS current_seats,
        next_semester_seats,
        seat_forecast,
        CASE
            WHEN seat_forecast = 'EXPECTED_GROWTH' AND next_semester_seats > seat_count_proxy THEN 'CORRECT'
            WHEN seat_forecast = 'EXPECTED_DECLINE' AND next_semester_seats < seat_count_proxy THEN 'CORRECT'
            WHEN seat_forecast = 'EXPECTED_STABLE' AND ABS(next_semester_seats - seat_count_proxy) <= 2 THEN 'CORRECT'
            ELSE 'INCORRECT'
        END AS accuracy
    FROM view_riksdagen_party_electoral_trends
    WHERE next_semester_seats IS NOT NULL
        AND calendar_year >= 2020
)
SELECT
    party,
    COUNT(*) AS forecasts_made,
    SUM(CASE WHEN accuracy = 'CORRECT' THEN 1 ELSE 0 END) AS correct,
    ROUND(100.0 * SUM(CASE WHEN accuracy = 'CORRECT' THEN 1 ELSE 0 END) / COUNT(*), 2) AS accuracy_pct
FROM forecasts
GROUP BY party
ORDER BY accuracy_pct DESC;

6. Early Warning System

SELECT
    party,
    calendar_year,
    semester,
    seat_count_proxy,
    electoral_warning_flag,
    seat_change_absolute,
    win_rate_change_absolute,
    electoral_trend,
    ROUND(composite_electoral_score, 2) AS overall_score
FROM view_riksdagen_party_electoral_trends
WHERE electoral_warning_flag != 'NORMAL'
    AND calendar_year >= 2022
ORDER BY 
    CASE electoral_warning_flag
        WHEN 'CRITICAL_SEAT_LOSS' THEN 1
        WHEN 'SIGNIFICANT_SEAT_LOSS' THEN 2
        WHEN 'CRITICAL_PERFORMANCE_DROP' THEN 3
        WHEN 'CRITICAL_ENGAGEMENT_DROP' THEN 4
        ELSE 5
    END,
    calendar_year DESC;

7. Post-Election vs Pre-Election Comparison

WITH pre_post AS (
    SELECT
        party,
        calendar_year,
        CASE
            WHEN is_pre_election_period THEN 'PRE_ELECTION'
            WHEN is_post_election_period THEN 'POST_ELECTION'
        END AS election_phase,
        seat_count_proxy,
        win_rate,
        participation_rate,
        documents_produced
    FROM view_riksdagen_party_electoral_trends
    WHERE (is_pre_election_period = true OR is_post_election_period = true)
        AND calendar_year >= 2014
)
SELECT
    party,
    calendar_year AS election_year,
    MAX(CASE WHEN election_phase = 'PRE_ELECTION' THEN seat_count_proxy END) AS pre_seats,
    MAX(CASE WHEN election_phase = 'POST_ELECTION' THEN seat_count_proxy END) AS post_seats,
    MAX(CASE WHEN election_phase = 'POST_ELECTION' THEN seat_count_proxy END) - 
        MAX(CASE WHEN election_phase = 'PRE_ELECTION' THEN seat_count_proxy END) AS seat_change
FROM pre_post
GROUP BY party, calendar_year
ORDER BY calendar_year DESC, seat_change DESC;

⚡ Performance Characteristics

Query Time: 120-350ms (party-level aggregation + window functions)
Indexes Suggested: Composite index on (party, election_cycle_id, cycle_year, semester)
Data Volume: ~800-1000 rows (8-10 parties × 6 cycles × 8 semesters/cycle)
Refresh Frequency: Real-time (standard view)
Seat Count Limitation: Uses active member proxy; not actual seat allocation data

🔗 Dependencies

Upstream:

view_riksdagen_vote_data_ballot_party_summary_annual - Voting performance
view_party_performance_metrics - Seat count proxy, document production

Downstream:

Election forecasting models
Coalition viability analysis
Electoral trend reporting

🎯 Framework Integration

Temporal Analysis:

Cross-cycle electoral evolution
Pre-election vs post-election patterns
Moving average trend smoothing

Comparative Analysis:

Party-to-party electoral ranking
Percentile-based tier classification
Government vs opposition growth rates

Pattern Recognition:

Electoral trend classification (SURGING, COLLAPSING)
Volatility pattern detection
Early warning signal identification

Predictive Intelligence:

Seat forecast generation
Trajectory confidence scoring
Election readiness assessment

🎯 Use Cases

Election Forecasting - Project seat counts based on historical trends and moving averages
Electoral Risk Assessment - Identify parties with high volatility and seat loss warnings
Pre-Election Analysis - Measure party readiness and campaign momentum
Post-Election Evaluation - Compare predicted vs actual electoral outcomes
Coalition Viability - Assess party sizes for government formation scenarios
Trend Analysis - Track long-term growth/decline patterns across cycles
Early Warning System - Alert on critical performance degradation

Vote Data Views

Overview

Vote data views provide temporal aggregations of parliamentary voting records at multiple granularities (daily, weekly, monthly, annual) for ballots, parties, and individual politicians. These materialized views enable efficient time-series analysis and performance tracking.

Total Vote Views: 20+
Intelligence Value: ⭐⭐⭐⭐ HIGH
Primary Use Cases: Temporal analysis, performance trending, absence tracking, effectiveness metrics

View Hierarchy

graph TB
    A[vote_data<br/>Source Table] --> B[view_riksdagen_vote_data_ballot_summary]
    
    B --> C1[view_riksdagen_vote_data_ballot_summary_daily]
    B --> C2[view_riksdagen_vote_data_ballot_summary_weekly]
    B --> C3[view_riksdagen_vote_data_ballot_summary_monthly]
    B --> C4[view_riksdagen_vote_data_ballot_summary_annual]
    
    A --> D[view_riksdagen_vote_data_ballot_party_summary]
    D --> E1[view_riksdagen_vote_data_ballot_party_summary_daily]
    D --> E2[view_riksdagen_vote_data_ballot_party_summary_weekly]
    D --> E3[view_riksdagen_vote_data_ballot_party_summary_monthly]
    D --> E4[view_riksdagen_vote_data_ballot_party_summary_annual]
    
    A --> F[view_riksdagen_vote_data_ballot_politician_summary]
    F --> G1[view_riksdagen_vote_data_ballot_politician_summary_daily]
    F --> G2[view_riksdagen_vote_data_ballot_politician_summary_weekly]
    F --> G3[view_riksdagen_vote_data_ballot_politician_summary_monthly]
    F --> G4[view_riksdagen_vote_data_ballot_politician_summary_annual]
    
    style B fill:#d1f2eb,stroke:#333,stroke-width:2px
    style D fill:#d1f2eb,stroke:#333,stroke-width:2px
    style F fill:#d1f2eb,stroke:#333,stroke-width:2px
    style C1 fill:#e1f5ff,stroke:#333,stroke-width:2px
    style E1 fill:#cce5ff,stroke:#333,stroke-width:2px
    style G1 fill:#ffeb99,stroke:#333,stroke-width:2px

Materialized Views Summary

All vote summary views are materialized for performance optimization:

View Category	Granularity	Materialized	Refresh	Use Case
Ballot Summaries	Daily/Weekly/Monthly/Annual	✓	Daily 02:00	Overall chamber statistics
Party Summaries	Daily/Weekly/Monthly/Annual	✓	Daily 02:00	Party performance tracking
Politician Summaries	Daily/Weekly/Monthly/Annual	✓	Daily 02:00	Individual MP monitoring

Common Metrics Across All Vote Views

Metric	Description	Calculation
`ballot_count`	Number of votes	COUNT(DISTINCT ballot_id)
`yes_votes`	Total "Ja" votes	SUM(CASE vote = 'Ja')
`no_votes`	Total "Nej" votes	SUM(CASE vote = 'Nej')
`abstain_votes`	Total "Avstår" votes	SUM(CASE vote = 'Avstår')
`absent_votes`	Total absences	SUM(CASE vote = 'Frånvarande')
`absence_rate`	Percentage absent	100.0 * absent / total
`win_rate`	Percentage on winning side	100.0 * wins / participated
`rebel_rate`	Percentage against party	100.0 * party_opposition / participated

view_riksdagen_vote_data_ballot_politician_summary_daily ⭐⭐⭐⭐⭐

Category: Vote Data Views (v1.2, materialized v1.25)
Type: Materialized View
Intelligence Value: VERY HIGH - Individual Daily Performance Tracking

Purpose

Daily aggregation of individual politician voting behavior, tracking presence, effectiveness, and party discipline. Foundation for all behavioral trend analysis and risk assessment calculations. Most frequently queried vote view.

Key Columns

Column	Type	Description	Example
`vote_date`	DATE	Voting date	'2024-10-15'
`intressent_id`	VARCHAR(255)	Politician ID	'0532213467925'
`first_name`	VARCHAR(255)	Politician first name	'Anna'
`last_name`	VARCHAR(255)	Politician last name	'Andersson'
`party`	VARCHAR(50)	Party affiliation	'S'
`ballot_count`	INTEGER	Ballots this day	12
`yes_votes`	INTEGER	"Ja" votes	7
`no_votes`	INTEGER	"Nej" votes	4
`abstain_votes`	INTEGER	Abstentions	0
`absent_votes`	INTEGER	Times absent	1
`total_votes`	INTEGER	Total voting opportunities	12
`absence_rate`	NUMERIC(5,2)	Daily absence percentage	8.33
`win_rate`	NUMERIC(5,2)	Percentage on winning side	75.00
`rebel_rate`	NUMERIC(5,2)	Percentage against party	5.00
`party_consensus_agree`	INTEGER	Votes with party consensus	10
`party_consensus_disagree`	INTEGER	Votes against party consensus	1

Example Queries

1. Daily Absence Report (Recent Activity)

SELECT
    vote_date,
    first_name,
    last_name,
    party,
    ballot_count,
    absent_votes,
    ROUND(absence_rate, 2) AS absence_pct
FROM view_riksdagen_vote_data_ballot_politician_summary_daily
WHERE vote_date >= CURRENT_DATE - INTERVAL '7 days'
    AND absent_votes > 0
ORDER BY vote_date DESC, absence_rate DESC;

2. Monthly Performance Aggregation (for Trend Analysis)

SELECT
    DATE_TRUNC('month', vote_date) AS month,
    intressent_id,
    first_name,
    last_name,
    party,
    SUM(ballot_count) AS total_ballots,
    SUM(absent_votes) AS total_absences,
    ROUND(100.0 * SUM(absent_votes) / NULLIF(SUM(ballot_count), 0), 2) AS monthly_absence_rate,
    ROUND(AVG(win_rate), 2) AS avg_win_rate,
    ROUND(AVG(rebel_rate), 2) AS avg_rebel_rate
FROM view_riksdagen_vote_data_ballot_politician_summary_daily
WHERE vote_date >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY DATE_TRUNC('month', vote_date), intressent_id, first_name, last_name, party
HAVING SUM(ballot_count) >= 10  -- Minimum activity threshold
ORDER BY intressent_id, month DESC;

3. Perfect Attendance Recognition

WITH recent_activity AS (
    SELECT
        intressent_id,
        first_name,
        last_name,
        party,
        SUM(ballot_count) AS total_ballots,
        SUM(absent_votes) AS total_absences,
        COUNT(DISTINCT vote_date) AS days_active
    FROM view_riksdagen_vote_data_ballot_politician_summary_daily
    WHERE vote_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY intressent_id, first_name, last_name, party
)
SELECT
    first_name,
    last_name,
    party,
    total_ballots,
    days_active
FROM recent_activity
WHERE total_absences = 0
    AND total_ballots >= 50  -- Significant activity
ORDER BY total_ballots DESC;

4. High Rebellion Rate Detection

SELECT
    vote_date,
    first_name,
    last_name,
    party,
    ballot_count,
    ROUND(rebel_rate, 2) AS rebellion_pct,
    party_consensus_disagree AS rebellion_count
FROM view_riksdagen_vote_data_ballot_politician_summary_daily
WHERE vote_date >= CURRENT_DATE - INTERVAL '30 days'
    AND rebel_rate > 15  -- High rebellion threshold
    AND ballot_count >= 5  -- Sufficient sample
ORDER BY rebel_rate DESC, vote_date DESC;

5. Daily Performance Outliers (Statistical Anomalies)

WITH daily_stats AS (
    SELECT
        vote_date,
        AVG(absence_rate) AS avg_absence,
        STDDEV(absence_rate) AS stddev_absence,
        AVG(win_rate) AS avg_win,
        STDDEV(win_rate) AS stddev_win
    FROM view_riksdagen_vote_data_ballot_politician_summary_daily
    WHERE vote_date >= CURRENT_DATE - INTERVAL '90 days'
        AND ballot_count >= 5
    GROUP BY vote_date
)
SELECT
    vd.vote_date,
    vd.first_name,
    vd.last_name,
    vd.party,
    ROUND(vd.absence_rate, 2) AS absence_rate,
    ROUND(ds.avg_absence, 2) AS daily_avg_absence,
    ROUND((vd.absence_rate - ds.avg_absence) / NULLIF(ds.stddev_absence, 0), 2) AS absence_z_score
FROM view_riksdagen_vote_data_ballot_politician_summary_daily vd
JOIN daily_stats ds ON ds.vote_date = vd.vote_date
WHERE vd.vote_date >= CURRENT_DATE - INTERVAL '30 days'
    AND ABS((vd.absence_rate - ds.avg_absence) / NULLIF(ds.stddev_absence, 0)) > 2  -- 2 standard deviations
ORDER BY ABS((vd.absence_rate - ds.avg_absence) / NULLIF(ds.stddev_absence, 0)) DESC
LIMIT 20;

Performance Characteristics

Query Time: <50ms (materialized, indexed)
Indexes Used: idx_vote_summary_daily_date_person, idx_vote_summary_daily_party_date
Data Volume: ~1.5 million rows (350 politicians × ~4,000 sitting days)
Refresh Frequency: Daily 02:00 UTC
Storage: ~200 MB (materialized)

Data Sources

Primary Table: vote_data (raw voting records)
Aggregation: Daily GROUP BY politician
Enhancements: Party consensus calculations, win/loss tracking

Dependencies

No view dependencies (built from source table)
Used by: view_politician_behavioral_trends, view_risk_score_evolution, all politician performance views

Risk Rules Supported

PoliticianLazy (P-01): absence_rate, absent_votes
PoliticianIneffectiveVoting (P-02): win_rate
PoliticianHighRebelRate (P-03): rebel_rate, party_consensus_disagree
All politician performance rules depend on this view

Election Cycle Views (NEW v1.51)

Overview

Election Cycle Views (v1.51) provide META/META-level historical analysis of Swedish parliamentary activity across election cycles, incorporating Swedish parliamentary temporal context (4-year cycles, autumn/spring semesters, pre-election significance). These views aggregate existing advanced analytics with election cycle dimensions to enable cross-cycle comparative analysis and election-aware intelligence.

Total Election Cycle Views: 6
Intelligence Value: ⭐⭐⭐⭐⭐ VERY HIGH
Type: META/META Level (aggregate existing views only)
Changelog: v1.51 - GitHub Issue #8205
Primary Use Cases: Election forecasting, cross-cycle analysis, pre-election behavioral patterns, coalition stability trends, legislative effectiveness by cycle phase

Swedish Parliamentary Context

Elections: Every 4 years since 1994 (second Sunday in September): 1994, 1998, 2002, 2006, 2010, 2014, 2018, 2022, 2026...
Parliamentary Periods: Start second Tuesday in September after elections
Autumn Semester: Mid-September to ~January 25
Spring Semester: ~January 26 to next September
Pre-Election Significance: Final spring semester before election (campaign impact, policy shifts, attendance spikes/drops, coalition volatility)

Framework Coverage

All 6 analytical frameworks are explicitly covered with corresponding views:

Framework	View	Supporting Views	Risk Rules	Operational
Temporal Analysis	view_election_cycle_temporal_trends	35 views	20+ rules	100%
Comparative Analysis	view_election_cycle_comparative_analysis	26 views	15+ rules	100%
Predictive Intelligence	view_election_cycle_predictive_intelligence	14 views	8/8 rules	100%
Network Analysis	view_election_cycle_network_analysis	11 views	3/4 rules	75%
Decision Intelligence	view_election_cycle_decision_intelligence	5 views	5/5 rules	100%
Pattern Recognition	view_election_cycle_anomaly_pattern	23 views	12/13 rules	92%

View Inventory

View Name	Framework	Intelligence Value	Description
view_election_cycle_temporal_trends	Temporal Analysis	⭐⭐⭐⭐⭐	Attendance, ballots, violations by cycle/semester
view_election_cycle_comparative_analysis	Comparative Analysis	⭐⭐⭐⭐⭐	Party-level metrics comparison by cycle/semester
view_election_cycle_predictive_intelligence	Predictive Intelligence	⭐⭐⭐⭐⭐	Risk forecasts and trajectory analysis by cycle
view_election_cycle_network_analysis	Network Analysis	⭐⭐⭐⭐⭐	Coalition alignment structure by election cycle
view_election_cycle_decision_intelligence	Decision Intelligence	⭐⭐⭐⭐⭐	Proposal success rates and effectiveness by cycle
view_election_cycle_anomaly_pattern	Pattern Recognition	⭐⭐⭐⭐⭐	Anomaly detection and risk pattern aggregation by cycle

view_election_cycle_temporal_trends ⭐⭐⭐⭐⭐

Category: Election Cycle Views (v1.51)
Type: Standard View (META/META level)
Framework: Temporal Analysis (35 supporting views)
Intelligence Value: VERY HIGH - Cross-Cycle Temporal Intelligence

Purpose

Aggregates longitudinal trends for attendance, bill proposals, voting activity, and committee participation across election cycles with semester granularity. Enables analysis of how political behavior varies across the 4-year election cycle and between autumn/spring semesters, with special emphasis on pre-election periods.

Key Columns

Column	Type	Description	Example
`election_cycle_id`	TEXT	Election cycle identifier	'2018-2022', '2022-2026'
`cycle_year`	INTEGER	Year within 4-year cycle (1-4)	3
`calendar_year`	INTEGER	Actual calendar year	2024
`semester`	TEXT	Semester (autumn/spring)	'spring'
`is_pre_election_semester`	BOOLEAN	TRUE for final spring before election	true
`months_until_election`	INTEGER	Countdown to next election	6
`active_politicians`	BIGINT	Count of active politicians	349
`avg_attendance_rate`	NUMERIC(5,2)	Average attendance percentage	87.50
`total_ballots`	BIGINT	Total ballots in period	1250
`total_votes`	BIGINT	Total votes cast	425000
`avg_win_rate`	NUMERIC(5,2)	Average win rate percentage	45.80
`avg_rebel_rate`	NUMERIC(5,2)	Average rebel rate percentage	12.30
`violation_count`	BIGINT	Total violations detected	45
`distinct_ballots`	BIGINT	Count of unique ballots	1200

Source Views (META Level)

view_politician_behavioral_trends: Monthly behavioral metrics (absence, win, rebel rates)
view_riksdagen_vote_data_ballot_politician_summary_monthly: Voting pattern aggregations

Example Queries

1. Pre-Election Semester Analysis

SELECT 
    election_cycle_id,
    calendar_year,
    avg_attendance_rate,
    avg_rebel_rate,
    violation_count
FROM view_election_cycle_temporal_trends
WHERE is_pre_election_semester = true
ORDER BY calendar_year DESC
LIMIT 3;

2. Cross-Cycle Comparison

SELECT 
    election_cycle_id,
    cycle_year,
    semester,
    avg_attendance_rate,
    avg_rebel_rate
FROM view_election_cycle_temporal_trends
WHERE semester = 'spring' AND cycle_year = 4
ORDER BY election_cycle_id;

Intelligence Applications

Election Forecasting: Analyze pre-election behavioral patterns
Temporal Trends: Track how metrics evolve across 4-year cycles
Attendance Patterns: Identify election-related attendance spikes/drops
Coalition Stability: Monitor rebel rates approaching elections
Cross-Cycle Benchmarking: Compare current cycle to historical patterns

view_election_cycle_comparative_analysis ⭐⭐⭐⭐⭐

Category: Election Cycle Views (v1.51)
Type: Standard View (META/META level)
Framework: Comparative Analysis (26 supporting views)

Purpose

Party-level comparative analysis across election cycles with semester granularity, enabling cross-party and cross-cycle performance benchmarking.

Key Columns

Column	Type	Description
`election_cycle_id`	TEXT	Election cycle identifier
`cycle_year`	INTEGER	Year within cycle (1-4)
`semester`	TEXT	Semester (autumn/spring)
`party`	TEXT	Political party abbreviation
`total_votes_party`	BIGINT	Total party votes
`avg_yes_percentage`	NUMERIC(5,2)	Average yes vote percentage
`avg_absence_percentage`	NUMERIC(5,2)	Average absence rate
`avg_rebel_percentage`	NUMERIC(5,2)	Average rebellion rate
`party_member_count`	BIGINT	Active party members

Source Views

view_riksdagen_vote_data_ballot_party_summary_monthly

view_election_cycle_predictive_intelligence ⭐⭐⭐⭐⭐

Category: Election Cycle Views (v1.51)
Framework: Predictive Intelligence (14 supporting views)

Purpose

Forecasts, risk spikes, and predictive intelligence signals by election cycle and semester.

Key Columns

Column	Type	Description
`risk_forecast_category`	TEXT	Predicted risk level
`politicians_at_risk`	BIGINT	Count with escalating risk
`avg_risk_score_change`	NUMERIC(5,2)	Average risk delta
`predicted_high_absenteeism`	BIGINT	Forecast attendance issues
`predicted_coalition_volatility`	BIGINT	High rebel trend count

Source Views

view_risk_score_evolution
view_politician_behavioral_trends

view_election_cycle_network_analysis ⭐⭐⭐⭐⭐

Category: Election Cycle Views (v1.51)
Framework: Network Analysis (11 supporting views)

Purpose

Coalition structure and network trend mapping per election cycle and semester.

Key Columns

Column	Type	Description
`party_a`	TEXT	First party in pair
`party_b`	TEXT	Second party in pair
`alignment_score`	NUMERIC(5,2)	Voting alignment percentage
`coalition_strength`	TEXT	Strength classification

Source Views

view_riksdagen_coalition_alignment_matrix

view_election_cycle_decision_intelligence ⭐⭐⭐⭐⭐

Category: Election Cycle Views (v1.51)
Framework: Decision Intelligence (5 supporting views)

Purpose

Policy success, coalition stability, and legislative outcome analysis by election cycle and semester.

Key Columns

Column	Type	Description
`party`	TEXT	Political party
`total_proposals`	BIGINT	Total proposals submitted
`approved_proposals`	BIGINT	Successfully approved proposals
`avg_approval_rate`	NUMERIC(5,2)	Approval success percentage
`decision_effectiveness`	TEXT	Effectiveness classification

Source Views

view_riksdagen_party_decision_flow

view_election_cycle_anomaly_pattern ⭐⭐⭐⭐⭐

Category: Election Cycle Views (v1.51)
Type: Standard View (META/META level)
Framework: Pattern Recognition (23 supporting views)
Intelligence Value: VERY HIGH - Anomaly Detection & Risk Pattern Intelligence

Purpose

Multi-source anomaly pattern detection aggregating risk score evolution, voting anomaly classification, and politician risk summaries across election cycles and semesters. Identifies risk escalation patterns, behavioral outliers, and anomaly acceleration trends with windowed statistics including semester-over-semester change tracking.

Key Columns

Column	Type	Description
`election_cycle_id`	TEXT	Election cycle identifier (e.g., '2018-2022')
`anomaly_type`	TEXT	Pattern classification type
`politician_count_with_risk`	BIGINT	Politicians with HIGH/CRITICAL risk severity
`avg_risk_score`	NUMERIC(5,2)	Average risk score across cycle
`risk_escalations`	BIGINT	Count of risk severity escalations
`high_anomaly_count`	BIGINT	Frequent/consistent rebel count
`avg_total_rebellions`	NUMERIC(5,2)	Average rebellion count
`strong_consensus_rebels`	BIGINT	Politicians with 5+ strong consensus rebellions
`avg_risk_score_prs`	NUMERIC(5,2)	Average risk score from politician risk summary
`high_risk_politicians`	BIGINT	HIGH/CRITICAL risk level politicians
`risk_trend`	TEXT	Risk trajectory (escalating/improving/stable)
`anomaly_acceleration`	BIGINT	Change in anomaly count vs previous semester

Sample Query

SELECT election_cycle_id, semester, avg_risk_score,
       high_anomaly_count, risk_trend, anomaly_acceleration
FROM view_election_cycle_anomaly_pattern
WHERE risk_trend = 'escalating'
ORDER BY election_cycle_id, cycle_year, semester;

Source Views

view_risk_score_evolution
view_riksdagen_voting_anomaly_detection
view_politician_risk_summary

Intelligence Views

Overview

Intelligence views (v1.29-v1.30) represent advanced analytical capabilities combining multiple data sources for risk assessment, trend analysis, and predictive intelligence. These views implement sophisticated algorithms for behavioral classification, anomaly detection, and coalition analysis.

Total Intelligence Views: 15+
Intelligence Value: ⭐⭐⭐⭐⭐ VERY HIGH
Primary Use Cases: Risk assessment, trend forecasting, coalition analysis, anomaly detection, dashboard aggregation

View Categories

View Type	Examples	Purpose
Behavioral Trends	politician_behavioral_trends, party_effectiveness_trends	Time-series performance tracking
Risk Assessment	risk_score_evolution, politician_risk_summary	Automated risk scoring
Coalition Analysis	coalition_alignment_matrix	Government formation forecasting
Network Intelligence	politician_influence_metrics	Power structure mapping
Dashboard Aggregation	riksdagen_intelligence_dashboard	Unified intelligence products
Crisis Analysis	crisis_resilience_indicators	Crisis period performance assessment
Anomaly Detection	voting_anomaly_detection	Behavioral outlier identification

view_riksdagen_crisis_resilience_indicators ⭐⭐⭐⭐⭐

Category: Intelligence Views (v1.29)
Type: Standard View
Intelligence Value: VERY HIGH - Crisis Performance Assessment
Changelog: v1.29 Crisis Resilience Analysis

Purpose

Sophisticated crisis resilience analysis comparing politician performance during high-activity "crisis" periods versus normal periods. Automatically identifies crisis periods (ballot volume >150% of average), then calculates individual resilience metrics including attendance, party discipline, and performance consistency. Provides resilience classification from HIGHLY_RESILIENT to LOW_RESILIENCE.

Key Columns

Column	Type	Description	Example
`person_id`	VARCHAR(255)	Politician identifier	'0532213467925'
`first_name`	VARCHAR(255)	Politician first name	'Anna'
`last_name`	VARCHAR(255)	Politician last name	'Andersson'
`party`	VARCHAR(50)	Party affiliation	'S'
`status`	VARCHAR(100)	Current status	'Tjänstgörande riksdagsledamot'
`crisis_period_votes`	BIGINT	Votes during crisis periods	145
`crisis_definitive_votes`	BIGINT	Definitive votes (Ja/Nej) in crisis	132
`crisis_absences`	BIGINT	Absences during crisis	13
`crisis_absence_rate`	DOUBLE PRECISION	Crisis absence rate (0.0-1.0)	0.090 (9%)
`normal_votes`	BIGINT	Votes during normal periods	320
`normal_absences`	BIGINT	Absences during normal periods	28
`normal_absence_rate`	DOUBLE PRECISION	Normal absence rate (0.0-1.0)	0.088 (8.8%)
`absence_delta`	DOUBLE PRECISION	Crisis - Normal absence rate	0.002 (0.2%)
`aligned_crisis_votes`	BIGINT	Votes aligned with party in crisis	125
`crisis_party_alignment_rate`	DOUBLE PRECISION	Party discipline in crisis (0.0-1.0)	0.947 (94.7%)
`resilience_score`	NUMERIC(10,2)	Overall resilience score (0-100)	87.5
`resilience_classification`	TEXT	Resilience level	'HIGHLY_RESILIENT'
`attendance_resilience`	TEXT	Attendance-specific resilience	'RESILIENT'
`discipline_resilience`	TEXT	Discipline-specific resilience	'HIGHLY_RESILIENT'

Crisis Period Detection

Algorithm:

Calculate average monthly ballot count (last 2 years)
Identify months with ballot count >150% of average as "crisis periods"
Remaining months classified as "normal periods"
Compare individual performance across both period types

Example Crisis Triggers:

Budget crises requiring extensive voting
Government formation periods
Major legislative pushes
Political scandals requiring multiple votes
Emergency legislation periods

Resilience Score Calculation

Formula: $ \text{resilience\_score} = 100 - ( (\text{crisis\_absence\_rate} \times 40) + ((1 - \text{crisis\_party\_alignment\_rate}) \times 40) + (\text{max}(0, \text{absence\_delta}) \times 20) ) $

Components:

40% weight: Crisis absence rate (lower is better)
40% weight: Crisis party discipline (higher is better)
20% weight: Absence rate increase in crisis (lower is better)

Resilience Classification Thresholds:

Score Range	Classification	Description
90-100	HIGHLY_RESILIENT	Excellent crisis performance
75-89	RESILIENT	Good crisis performance
60-74	MODERATE_RESILIENCE	Adequate crisis performance
45-59	VULNERABLE	Below average crisis performance
0-44	LOW_RESILIENCE	Poor crisis performance

Example Queries

1. Identify Most Resilient Politicians

SELECT 
    first_name,
    last_name,
    party,
    resilience_score,
    resilience_classification,
    crisis_absence_rate,
    crisis_party_alignment_rate,
    crisis_period_votes
FROM view_riksdagen_crisis_resilience_indicators
WHERE status = 'Tjänstgörande riksdagsledamot'
ORDER BY resilience_score DESC
LIMIT 20;

2. Identify Vulnerable Politicians in Crisis

SELECT 
    first_name,
    last_name,
    party,
    resilience_classification,
    crisis_absence_rate,
    normal_absence_rate,
    absence_delta,
    crisis_party_alignment_rate
FROM view_riksdagen_crisis_resilience_indicators
WHERE resilience_classification IN ('VULNERABLE', 'LOW_RESILIENCE')
    AND status = 'Tjänstgörande riksdagsledamot'
ORDER BY resilience_score ASC;

3. Party-Level Crisis Resilience Analysis

SELECT 
    party,
    COUNT(*) AS member_count,
    ROUND(AVG(resilience_score), 1) AS avg_resilience,
    COUNT(*) FILTER (WHERE resilience_classification = 'HIGHLY_RESILIENT') AS highly_resilient,
    COUNT(*) FILTER (WHERE resilience_classification IN ('VULNERABLE', 'LOW_RESILIENCE')) AS vulnerable,
    ROUND(AVG(crisis_absence_rate) * 100, 1) AS avg_crisis_absence_pct,
    ROUND(AVG(crisis_party_alignment_rate) * 100, 1) AS avg_crisis_discipline_pct
FROM view_riksdagen_crisis_resilience_indicators
WHERE status = 'Tjänstgörande riksdagsledamot'
GROUP BY party
ORDER BY avg_resilience DESC;

4. Crisis Performance Degradation Detection

SELECT 
    first_name,
    last_name,
    party,
    normal_absence_rate,
    crisis_absence_rate,
    absence_delta,
    ROUND(absence_delta * 100, 1) AS absence_increase_pct
FROM view_riksdagen_crisis_resilience_indicators
WHERE absence_delta > 0.05  -- Absence rate increased by >5% in crisis
    AND status = 'Tjänstgörande riksdagsledamot'
ORDER BY absence_delta DESC;

Performance Characteristics

Query Time: 800-2000ms (complex window functions and multi-period aggregation)
Refresh Frequency: Real-time (recalculates on each query)
Data Volume: ~350 rows (all politicians with voting history)
Optimization: Consider materializing for faster dashboard queries

Intelligence Frameworks Applicable

Crisis Analysis: Performance under pressure assessment
Predictive Intelligence: Future crisis performance forecasting
Comparative Analysis: Party resilience benchmarking
Risk Assessment: Vulnerability identification

Risk Rules Supported

PoliticianLazy (P-01): Crisis absence patterns
PoliticianIneffectiveVoting (P-02): Crisis effectiveness degradation
PartyRebelVoting (P-04): Crisis discipline breakdown

Use Cases

Government Formation: Assess coalition partner reliability under pressure
Ministry Appointments: Identify crisis-capable ministers
Early Warning: Detect politicians who crack under pressure
Party Strength: Evaluate party discipline during crises
Historical Analysis: Study performance during past crises (budget, coalition, scandal)

Purpose

Unified intelligence dashboard aggregating key metrics from five core intelligence dimensions into a single real-time overview. Provides at-a-glance assessment of political stability, coalition dynamics, defection risks, influence networks, and crisis readiness. Designed for executive-level intelligence briefings and rapid situational awareness.

Key Columns

Column	Type	Description	Example
`parties_gaining_momentum`	BIGINT	Parties with positive trend direction	3
`parties_losing_momentum`	BIGINT	Parties with negative trend direction	2
`volatile_parties`	BIGINT	Parties with volatile/highly volatile classification	1
`high_probability_coalitions`	BIGINT	Coalition pairs with strong likelihood	4
`cross_bloc_alliances`	BIGINT	Strong/moderate cross-bloc coalitions	2
`high_defection_risks`	BIGINT	Politicians with frequent/consistent rebel pattern	5
`low_discipline_politicians`	BIGINT	All politicians with rebel pattern (moderate+)	12
`power_brokers`	BIGINT	Politicians with strong/moderate broker classification	8
`highly_connected_politicians`	BIGINT	Politicians with highly influential classification	6
`crisis_ready_politicians`	BIGINT	Politicians with highly resilient classification	45
`low_resilience_politicians`	BIGINT	Politicians with low resilience classification	8
`stability_assessment`	TEXT	Overall political stability classification	'STABLE_POLITICAL_ENVIRONMENT'
`coalition_assessment`	TEXT	Coalition landscape assessment	'STABLE_COALITION_PATTERNS'
`latest_vote_data`	DATE	Most recent voting data timestamp	'2024-11-15'
`ballots_last_30_days`	BIGINT	Recent voting activity indicator	42
`intelligence_report_timestamp`	TIMESTAMP	Report generation timestamp	'2026-01-22 15:00:00'

Intelligence Dimensions Aggregated

1. Party Momentum (from view_riksdagen_party_momentum_analysis)

Parties gaining/losing momentum
Volatile parties requiring monitoring

2. Coalition Dynamics (from view_riksdagen_coalition_alignment_matrix)

High-probability coalitions
Cross-bloc alliances (potential realignments)

3. Voting Anomalies (from view_riksdagen_voting_anomaly_detection)

High defection risks (critical rebels)
Low discipline politicians (all rebel patterns)

4. Influence Networks (from view_riksdagen_politician_influence_metrics)

Power brokers (coalition facilitators)
Highly connected politicians (network hubs)

5. Crisis Resilience (from view_riksdagen_crisis_resilience_indicators)

Crisis-ready politicians (high performers under pressure)
Low resilience politicians (vulnerability indicators)

Assessment Classifications

Stability Assessment Logic:

CASE
    WHEN high_defection_risks >= 5 THEN 'HIGH_POLITICAL_INSTABILITY_RISK'
    WHEN volatile_parties >= 3 THEN 'MODERATE_POLITICAL_INSTABILITY_RISK'
    ELSE 'STABLE_POLITICAL_ENVIRONMENT'
END

Coalition Assessment Logic:

CASE
    WHEN cross_bloc_alliances >= 2 THEN 'POTENTIAL_REALIGNMENT_DETECTED'
    WHEN high_probability_coalitions >= 5 THEN 'STABLE_COALITION_PATTERNS'
    ELSE 'UNCERTAIN_COALITION_LANDSCAPE'
END

Example Queries

1. Current Intelligence Snapshot

SELECT 
    stability_assessment,
    coalition_assessment,
    parties_gaining_momentum,
    parties_losing_momentum,
    high_defection_risks,
    cross_bloc_alliances,
    power_brokers,
    crisis_ready_politicians,
    latest_vote_data,
    ballots_last_30_days,
    intelligence_report_timestamp
FROM view_riksdagen_intelligence_dashboard;

2. Risk Alert Detection

SELECT 
    CASE 
        WHEN stability_assessment = 'HIGH_POLITICAL_INSTABILITY_RISK' 
        THEN 'CRITICAL: ' || high_defection_risks || ' high defection risks detected'
        WHEN stability_assessment = 'MODERATE_POLITICAL_INSTABILITY_RISK' 
        THEN 'WARNING: ' || volatile_parties || ' volatile parties detected'
        ELSE 'STABLE: No immediate concerns'
    END AS risk_alert,
    stability_assessment,
    coalition_assessment
FROM view_riksdagen_intelligence_dashboard;

3. Coalition Realignment Monitor

SELECT 
    coalition_assessment,
    cross_bloc_alliances AS potential_realignments,
    high_probability_coalitions AS stable_coalitions,
    CASE 
        WHEN cross_bloc_alliances >= 2 THEN 'Monitor for coalition shifts'
        ELSE 'No realignment indicators'
    END AS recommendation
FROM view_riksdagen_intelligence_dashboard;

4. Intelligence Brief Summary

SELECT 
    'Political Stability: ' || stability_assessment || E'\n' ||
    'Coalition Landscape: ' || coalition_assessment || E'\n' ||
    'High-Risk Politicians: ' || high_defection_risks || E'\n' ||
    'Cross-Bloc Alliances: ' || cross_bloc_alliances || E'\n' ||
    'Power Brokers: ' || power_brokers || E'\n' ||
    'Crisis-Ready: ' || crisis_ready_politicians || E'\n' ||
    'Data Currency: ' || (CURRENT_DATE - latest_vote_data) || ' days old' AS intelligence_brief
FROM view_riksdagen_intelligence_dashboard;

Performance Characteristics

Query Time: 200-500ms (aggregates 5 source views)
Refresh Frequency: Real-time (recalculates on each query)
Data Volume: Single row (dashboard summary)
Optimization: Consider materializing for sub-second response

Dependencies

Critical Dependencies (all must exist):

view_riksdagen_party_momentum_analysis - Party trend analysis
view_riksdagen_coalition_alignment_matrix - Coalition probabilities
view_riksdagen_voting_anomaly_detection - Rebel/discipline patterns
view_riksdagen_politician_influence_metrics - Network analysis
view_riksdagen_crisis_resilience_indicators - Crisis performance
vote_data table - Recent voting activity metrics

Intelligence Frameworks Applicable

Predictive Intelligence: Early warning indicators for instability
Comparative Analysis: Multi-dimensional political assessment
Network Analysis: Coalition and influence patterns
Temporal Intelligence: Momentum and trend tracking
Risk Assessment: Comprehensive threat monitoring

Risk Rules Supported

All Risk Categories: Dashboard aggregates indicators from all 50 risk rules
Executive Summary: High-level roll-up of rule violations
Priority Alerting: Focuses on critical patterns and anomalies

Use Cases

Executive Briefings: Single-view intelligence snapshot for leadership
Situation Room: Real-time political stability monitoring
Early Warning System: Detect instability before it manifests
Coalition Formation: Assess viability of potential government coalitions
Risk Dashboards: Feed executive intelligence dashboards
API Endpoints: Power real-time intelligence APIs
Alert Generation: Trigger notifications on threshold violations

Historical Context

Version History:

v1.29 (2025-03-15): Initial creation as unified dashboard
v1.33 (2025-06-20): Dropped by CASCADE during crisis_resilience_indicators fix, recreated same changeset
v1.40 (2025-09-10): Dropped by CASCADE again, recreated with updated column names
v1.61 (2026-01-19): Indirectly affected by coalition_evolution/electoral_trends issues
v1.62 (2026-01-22): Final recreation after v1.61 DROP CASCADE bug fix

Notes

Single Row View: Returns exactly 1 row with aggregated metrics
No Historical Data: Provides current snapshot only (no time series)
Real-Time Calculation: Reflects latest data from all source views
Alert Threshold Tuning: Thresholds (e.g., 5 defection risks) may need adjustment based on Riksdag size
Materialization Candidate: Consider creating materialized view for faster API responses

view_risk_score_evolution ⭐⭐⭐⭐⭐

Category: Intelligence Views (v1.30)
Type: Standard View
Intelligence Value: VERY HIGH - Risk Tracking & Prediction
Changelog: v1.30 OSINT Risk Evolution Monitoring

Purpose

Temporal risk score tracking combining rule violations, behavioral trends, and predictive indicators. Provides month-over-month risk evolution analysis with automated severity classification and early warning capabilities.

Key Columns

Column	Type	Description	Example
`person_id`	VARCHAR(255)	Politician identifier	'0532213467925'
`first_name`	VARCHAR(255)	Politician first name	'Anna'
`last_name`	VARCHAR(255)	Politician last name	'Andersson'
`party`	VARCHAR(50)	Party affiliation	'S'
`year_month`	DATE	Month of assessment	'2024-10-01'
`risk_score`	INTEGER	Total risk points	85
`risk_severity`	VARCHAR(50)	Classification	'MAJOR'
`active_violations`	INTEGER	Current rule violations	3
`absence_risk`	INTEGER	Absence-related points	30
`effectiveness_risk`	INTEGER	Effectiveness-related points	25
`discipline_risk`	INTEGER	Discipline-related points	15
`productivity_risk`	INTEGER	Productivity-related points	15
`risk_trend`	INTEGER	Month-over-month change	+12
`risk_velocity`	INTEGER	Acceleration of risk growth	+5
`ma_3month_risk`	NUMERIC(10,2)	3-month moving average	78.50
`risk_trajectory`	VARCHAR(50)	Trend classification	'ESCALATING'

Risk Score Calculation

Total Risk Score = Sum of risk category points:

Risk Category	Max Points	Calculation Basis
Absence Risk	0-100	Absence rate × multiplier + trend weighting
Effectiveness Risk	0-100	(100 - win_rate) × multiplier + trend weighting
Discipline Risk	0-100	Rebel rate × multiplier + party impact factor
Productivity Risk	0-100	Document deficit × multiplier

Risk Severity Thresholds:

Severity	Score Range	Description
MINOR	10-49	Early warning, monitor
MAJOR	50-99	Significant concern, investigate
CRITICAL	100+	Severe risk, immediate attention

Risk Trajectory Classifications

STABLE: Risk score variation < 5 points/month, consistent behavior
IMPROVING: Risk score declining > 10 points/month, positive trend
CONCERNING: Risk score increasing 5-15 points/month, monitor closely
ESCALATING: Risk score increasing > 15 points/month, intervention needed
VOLATILE: Large month-to-month swings, unpredictable behavior

Example Queries

1. Current High-Risk Politicians (Top 20)

SELECT
    first_name,
    last_name,
    party,
    risk_score,
    risk_severity,
    active_violations,
    absence_risk,
    effectiveness_risk,
    discipline_risk,
    risk_trajectory
FROM view_risk_score_evolution
WHERE year_month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
    AND risk_score >= 50  -- MAJOR or CRITICAL
ORDER BY risk_score DESC
LIMIT 20;

2. Risk Escalation Detection (Rapid Increase)

SELECT
    first_name,
    last_name,
    party,
    year_month,
    risk_score,
    risk_trend AS monthly_change,
    risk_velocity AS acceleration,
    risk_trajectory,
    CASE
        WHEN risk_trend > 20 THEN 'CRITICAL_ESCALATION'
        WHEN risk_trend > 10 THEN 'MODERATE_ESCALATION'
        WHEN risk_trend > 5 THEN 'MILD_ESCALATION'
        ELSE 'STABLE_OR_IMPROVING'
    END AS escalation_severity
FROM view_risk_score_evolution
WHERE year_month >= CURRENT_DATE - INTERVAL '6 months'
    AND risk_trend > 5  -- Increasing risk
ORDER BY risk_trend DESC, year_month DESC
LIMIT 30;

3. Risk Category Breakdown by Party

SELECT
    party,
    COUNT(*) AS members_assessed,
    ROUND(AVG(risk_score), 1) AS avg_party_risk,
    COUNT(*) FILTER (WHERE risk_severity = 'CRITICAL') AS critical_count,
    COUNT(*) FILTER (WHERE risk_severity = 'MAJOR') AS major_count,
    COUNT(*) FILTER (WHERE risk_severity = 'MINOR') AS minor_count,
    ROUND(AVG(absence_risk), 1) AS avg_absence_risk,
    ROUND(AVG(effectiveness_risk), 1) AS avg_effectiveness_risk,
    ROUND(AVG(discipline_risk), 1) AS avg_discipline_risk
FROM view_risk_score_evolution
WHERE year_month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
GROUP BY party
ORDER BY avg_party_risk DESC;

4. Risk Evolution Time Series (Individual Politician)

SELECT
    year_month,
    risk_score,
    risk_severity,
    risk_trend,
    ma_3month_risk,
    active_violations,
    risk_trajectory
FROM view_risk_score_evolution
WHERE person_id = '0532213467925'  -- Specific politician
    AND year_month >= CURRENT_DATE - INTERVAL '24 months'
ORDER BY year_month DESC;

5. Early Warning System (Deteriorating Performance)

WITH risk_changes AS (
    SELECT
        person_id,
        first_name,
        last_name,
        party,
        year_month,
        risk_score,
        LAG(risk_score) OVER (
            PARTITION BY person_id 
            ORDER BY year_month
        ) AS prev_risk,
        risk_score - LAG(risk_score) OVER (
            PARTITION BY person_id 
            ORDER BY year_month
        ) AS risk_change
    FROM view_risk_score_evolution
    WHERE year_month >= CURRENT_DATE - INTERVAL '6 months'
)
SELECT
    first_name,
    last_name,
    party,
    COUNT(*) AS months_tracked,
    ROUND(AVG(risk_change), 1) AS avg_monthly_increase,
    MAX(risk_score) AS peak_risk,
    MIN(risk_score) AS lowest_risk,
    MAX(risk_score) - MIN(risk_score) AS total_increase
FROM risk_changes
WHERE risk_change IS NOT NULL
GROUP BY person_id, first_name, last_name, party
HAVING AVG(risk_change) > 3  -- Consistently increasing
    AND COUNT(*) >= 3  -- At least 3 months tracked
ORDER BY avg_monthly_increase DESC
LIMIT 20;
``$

#### \text{Performance} \text{Characteristics}

- **\text{Query} \text{Time}:** 100-150\text{ms} (\text{complex} \text{risk} \text{calculation})
- **\text{Indexes} \text{Used}:** \text{Multiple} \text{indexes} \text{on} \text{violation}, \text{behavioral}, \text{and} \text{temporal} \text{data}
- **\text{Data} \text{Volume}:** ~15{,}000 \text{rows} (350 \text{politicians}  \times  36 \text{months} \text{rolling})
- **\text{Refresh} \text{Frequency}:** \text{Real}-\text{time} (\text{recalculated} \text{on} \text{query})
- **\text{Optimization}:** \text{Strong} \text{candidate} \text{for} \text{materialization}

#### \text{Data} \text{Sources}

- **\text{Behavioral} \text{Data}:** $view_politician_behavioral_trends`
- **Violations:** `rule_violation` table
- **Productivity:** Document summary views
- **Party Context:** Party performance benchmarks

#### Dependencies

- Depends on: `view_politician_behavioral_trends`, `rule_violation`, document views
- Used by: `view_riksdagen_intelligence_dashboard`, risk reports

#### Risk Rules Supported

From [RISK_RULES_INTOP_OSINT.md](RISK_RULES_INTOP_OSINT.md):
- **All Politician Risk Rules (P-01 to P-24)**: Unified risk scoring
- **PoliticianCombinedRisk (P-05)**: Primary implementation view
- **Risk Trending Rules**: Temporal risk evolution tracking

---

### view_decision_temporal_trends ⭐⭐⭐⭐⭐

**Category:** Intelligence Views (v1.35)  
**Type:** Standard View  
**Intelligence Value:** VERY HIGH - Temporal Decision Flow Analysis & Predictive Intelligence  
**Changelog:** v1.35 Temporal Decision Trends with Moving Averages

#### Purpose

Temporal trends view for decision flow analysis from DOCUMENT_PROPOSAL_DATA, enabling time-series analysis of decision patterns, seasonal variations, and predictive forecasting of legislative activity. Provides daily aggregations with moving averages (7-day, 30-day, 90-day), year-over-year comparisons, and seasonal decomposition for Swedish parliamentary calendar. Supports anomaly detection through z-score analysis and trend identification via moving average crossovers.

#### Key Columns

| Column | Type | Description | Example |
|--------|------|-------------|---------|
| `decision_day` | DATE | Date of decision(s) | '2024-11-15' |
| `daily_decisions` | BIGINT | Total decisions on this day | 42 |
| `daily_approval_rate` | NUMERIC(5,2) | Percentage of approved decisions (bifall) | 67.50 |
| `approved_decisions` | BIGINT | Count of approved decisions (bifall) | 28 |
| `rejected_decisions` | BIGINT | Count of rejected decisions (avslag) | 8 |
| `referred_back_decisions` | BIGINT | Count of decisions referred back (återförvisning) | 6 |
| `ma_7day_decisions` | NUMERIC(10,2) | 7-day moving average of daily decisions | 38.71 |
| `ma_30day_decisions` | NUMERIC(10,2) | 30-day moving average of daily decisions | 41.15 |
| `ma_90day_decisions` | NUMERIC(10,2) | 90-day moving average of daily decisions | 39.82 |
| `ma_30day_approval_rate` | NUMERIC(5,2) | 30-day moving average of approval rate | 68.20 |
| `decisions_last_year` | BIGINT | Decisions on same day last year | 45 |
| `yoy_decisions_change` | BIGINT | Year-over-year change in decisions | -3 |
| `yoy_decisions_change_pct` | NUMERIC(5,2) | Year-over-year percentage change | -6.67 |
| `decision_year` | NUMERIC | Year of decision | 2024 |
| `decision_month` | NUMERIC | Month of decision (1-12) | 11 |
| `decision_week` | NUMERIC | Week of year (1-53) | 46 |
| `decision_day_of_week` | NUMERIC | Day of week (0=Sunday, 6=Saturday) | 2 |
| `parliamentary_period` | TEXT | Seasonal period classification | 'Autumn Session' |
| `decision_quarter` | TEXT | Quarter and year | 'Q4 2024' |

#### Seasonal Indicators

**Parliamentary Period Classification:**
- `Summer Recess`: July-August (low activity expected)
- `Winter Recess`: December-January (low activity expected)
- `Spring Session`: February-March
- `Late Spring Session`: April-June
- `Autumn Session`: September-November (typically highest activity)
- `Active Session`: Other periods

#### Example Queries

**1. Seasonal Pattern Analysis**

Identify average decision volumes and approval rates by parliamentary period over the last 3 years.

```sql
SELECT 
    parliamentary_period,
    COUNT(DISTINCT decision_day) AS days_with_decisions,
    ROUND(AVG(daily_decisions), 2) AS avg_daily_decisions,
    ROUND(AVG(daily_approval_rate), 2) AS avg_approval_rate,
    ROUND(MIN(daily_decisions), 2) AS min_daily_decisions,
    ROUND(MAX(daily_decisions), 2) AS max_daily_decisions
FROM view_decision_temporal_trends
WHERE decision_day >= CURRENT_DATE - INTERVAL '3 years'
GROUP BY parliamentary_period
ORDER BY avg_daily_decisions DESC;

Sample Output:

parliamentary_period      | days_with_decisions | avg_daily_decisions | avg_approval_rate | min_daily_decisions | max_daily_decisions
--------------------------+---------------------+---------------------+-------------------+---------------------+--------------------
Autumn Session           |                 245 |               42.15 |             68.30 |                  15 |                 87
Late Spring Session      |                 198 |               38.72 |             67.80 |                  12 |                 78
Spring Session           |                 156 |               35.45 |             66.50 |                   8 |                 72
Winter Recess            |                  45 |                8.23 |             71.20 |                   1 |                 25
Summer Recess            |                  38 |                5.67 |             69.40 |                   1 |                 18

Intelligence Application: Identifies seasonal patterns for resource planning and forecasting. Autumn Session shows highest activity (~42 decisions/day), while Summer Recess has minimal activity (~6 decisions/day).

2. Trend Detection (Moving Average Crossover)

Detect uptrends and downtrends in legislative activity using moving average crossovers.

SELECT 
    decision_day,
    daily_decisions,
    ma_7day_decisions,
    ma_30day_decisions,
    CASE 
        WHEN ma_7day_decisions > ma_30day_decisions THEN 'Uptrend ⬆'
        WHEN ma_7day_decisions < ma_30day_decisions THEN 'Downtrend ⬇'
        ELSE 'Neutral ➡'
    END AS trend_signal,
    ROUND(ma_7day_decisions - ma_30day_decisions, 2) AS ma_divergence
FROM view_decision_temporal_trends
WHERE decision_day >= CURRENT_DATE - INTERVAL '6 months'
ORDER BY decision_day DESC
LIMIT 20;

Sample Output:

decision_day | daily_decisions | ma_7day_decisions | ma_30day_decisions | trend_signal | ma_divergence
-------------+-----------------+-------------------+--------------------+--------------+--------------
2024-11-15   |              42 |             38.71 |              41.15 | Downtrend ⬇  |         -2.44
2024-11-14   |              45 |             39.86 |              41.22 | Downtrend ⬇  |         -1.36
2024-11-13   |              48 |             40.29 |              40.95 | Downtrend ⬇  |         -0.66
2024-11-12   |              52 |             41.14 |              40.75 | Uptrend ⬆    |          0.39
2024-11-11   |              47 |             40.71 |              40.35 | Uptrend ⬆    |          0.36

Intelligence Application: Moving average crossovers signal trend changes. When 7-day MA crosses above 30-day MA, legislative activity is accelerating. When it crosses below, activity is declining. This helps identify bottlenecks or unusual activity surges.

3. Anomaly Detection (Z-Score Method)

Identify days with abnormally high or low decision volumes using statistical deviation analysis.

WITH stats AS (
    SELECT 
        AVG(daily_decisions) AS mean_decisions,
        STDDEV(daily_decisions) AS stddev_decisions
    FROM view_decision_temporal_trends
    WHERE decision_day >= CURRENT_DATE - INTERVAL '1 year'
)
SELECT 
    vdt.decision_day,
    vdt.daily_decisions,
    vdt.parliamentary_period,
    ROUND(s.mean_decisions, 2) AS mean_decisions,
    ROUND(s.stddev_decisions, 2) AS stddev_decisions,
    ROUND((vdt.daily_decisions - s.mean_decisions) / NULLIF(s.stddev_decisions, 0), 2) AS z_score,
    CASE 
        WHEN ABS((vdt.daily_decisions - s.mean_decisions) / NULLIF(s.stddev_decisions, 0)) > 2.5 THEN 'EXTREME ANOMALY 🔴'
        WHEN ABS((vdt.daily_decisions - s.mean_decisions) / NULLIF(s.stddev_decisions, 0)) > 2.0 THEN 'ANOMALY 🟡'
        WHEN ABS((vdt.daily_decisions - s.mean_decisions) / NULLIF(s.stddev_decisions, 0)) > 1.5 THEN 'NOTABLE 🟢'
        ELSE 'NORMAL'
    END AS anomaly_status
FROM view_decision_temporal_trends vdt
CROSS JOIN stats s
WHERE vdt.decision_day >= CURRENT_DATE - INTERVAL '3 months'
  AND ABS((vdt.daily_decisions - s.mean_decisions) / NULLIF(s.stddev_decisions, 0)) > 1.5
ORDER BY ABS((vdt.daily_decisions - s.mean_decisions) / NULLIF(s.stddev_decisions, 0)) DESC
LIMIT 15;

Sample Output:

decision_day | daily_decisions | parliamentary_period | mean_decisions | stddev_decisions | z_score | anomaly_status
-------------+-----------------+----------------------+----------------+------------------+---------+------------------
2024-10-22   |              87 |    Autumn Session    |          39.45 |            15.23 |    3.12 | EXTREME ANOMALY 🔴
2024-09-15   |              78 |    Autumn Session    |          39.45 |            15.23 |    2.53 | EXTREME ANOMALY 🔴
2024-11-03   |              72 |    Autumn Session    |          39.45 |            15.23 |    2.14 | ANOMALY 🟡
2024-08-05   |               1 |    Summer Recess     |          39.45 |            15.23 |   -2.52 | EXTREME ANOMALY 🔴
2024-10-08   |              65 |    Autumn Session    |          39.45 |            15.23 |    1.68 | NOTABLE 🟢

Intelligence Application: Z-score > 2 indicates statistically significant anomalies. High z-scores may indicate critical legislative pushes, deadline-driven activity, or unusual parliamentary sessions. Low z-scores may indicate bottlenecks, political gridlock, or unexpected recesses.

4. Year-over-Year Comparison

Compare current legislative activity to the same period last year to identify trends.

SELECT 
    decision_day,
    daily_decisions AS current_year_decisions,
    decisions_last_year AS prior_year_decisions,
    yoy_decisions_change AS decision_change,
    yoy_decisions_change_pct AS pct_change,
    parliamentary_period,
    CASE 
        WHEN yoy_decisions_change_pct > 20 THEN 'Significant Increase ⬆⬆'
        WHEN yoy_decisions_change_pct > 10 THEN 'Moderate Increase ⬆'
        WHEN yoy_decisions_change_pct > -10 THEN 'Stable ➡'
        WHEN yoy_decisions_change_pct > -20 THEN 'Moderate Decrease ⬇'
        ELSE 'Significant Decrease ⬇⬇'
    END AS yoy_trend
FROM view_decision_temporal_trends
WHERE decision_day >= CURRENT_DATE - INTERVAL '3 months'
  AND decisions_last_year IS NOT NULL
ORDER BY decision_day DESC
LIMIT 20;

Sample Output:

decision_day | current_year | prior_year | decision_change | pct_change | parliamentary_period | yoy_trend
-------------+--------------+------------+-----------------+------------+----------------------+-------------------------
2024-11-15   |           42 |         45 |              -3 |      -6.67 | Autumn Session       | Stable ➡
2024-11-14   |           45 |         38 |               7 |      18.42 | Autumn Session       | Moderate Increase ⬆
2024-11-13   |           48 |         52 |              -4 |      -7.69 | Autumn Session       | Stable ➡
2024-11-12   |           52 |         41 |              11 |      26.83 | Autumn Session       | Significant Increase ⬆⬆

Intelligence Application: Year-over-year comparisons identify whether current legislative activity is above or below historical baselines. Significant deviations may indicate changing political priorities, coalition dynamics, or procedural changes.

5. Forecast Legislative Activity (Simple Moving Average)

Use 30-day moving average to forecast expected decision volumes for upcoming week.

WITH recent_trend AS (
    SELECT 
        ROUND(AVG(ma_30day_decisions), 2) AS forecast_baseline,
        ROUND(STDDEV(daily_decisions), 2) AS forecast_uncertainty
    FROM view_decision_temporal_trends
    WHERE decision_day >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT 
    CURRENT_DATE + INTERVAL '1 day' * generate_series(1, 7) AS forecast_date,
    rt.forecast_baseline AS expected_decisions,
    rt.forecast_baseline - rt.forecast_uncertainty AS lower_bound,
    rt.forecast_baseline + rt.forecast_uncertainty AS upper_bound,
    CASE 
        WHEN EXTRACT(DOW FROM CURRENT_DATE + INTERVAL '1 day' * generate_series(1, 7)) IN (0, 6) THEN 'Weekend (Low Activity)'
        ELSE 'Weekday (Normal Activity)'
    END AS activity_expectation
FROM recent_trend rt;

Sample Output:

forecast_date | expected_decisions | lower_bound | upper_bound | activity_expectation
--------------+--------------------+-------------+-------------+----------------------
2024-11-16    |              41.15 |       26.92 |       55.38 | Weekend (Low Activity)
2024-11-17    |              41.15 |       26.92 |       55.38 | Weekend (Low Activity)
2024-11-18    |              41.15 |       26.92 |       55.38 | Weekday (Normal Activity)
2024-11-19    |              41.15 |       26.92 |       55.38 | Weekday (Normal Activity)
2024-11-20    |              41.15 |       26.92 |       55.38 | Weekday (Normal Activity)

Intelligence Application: Simple forecasting using moving average baseline provides expected decision volumes. Actual volumes significantly outside bounds indicate unusual activity warranting investigation.

6. Parliamentary Session Effectiveness

Compare approval rates across different parliamentary periods to identify procedural patterns.

SELECT 
    parliamentary_period,
    COUNT(*) AS total_days,
    ROUND(AVG(daily_decisions), 2) AS avg_decisions_per_day,
    ROUND(AVG(daily_approval_rate), 2) AS avg_approval_rate,
    ROUND(AVG(ma_30day_approval_rate), 2) AS smoothed_approval_rate,
    ROUND(SUM(daily_decisions), 0) AS total_decisions,
    ROUND(SUM(approved_decisions), 0) AS total_approved,
    ROUND(SUM(rejected_decisions), 0) AS total_rejected
FROM view_decision_temporal_trends
WHERE decision_day >= CURRENT_DATE - INTERVAL '2 years'
GROUP BY parliamentary_period
ORDER BY total_decisions DESC;

Sample Output:

parliamentary_period    | total_days | avg_decisions_per_day | avg_approval_rate | smoothed_approval_rate | total_decisions | total_approved | total_rejected
------------------------+------------+-----------------------+-------------------+------------------------+-----------------+----------------+---------------
Autumn Session         |        245 |                 42.15 |             68.30 |                  68.50 |           10327 |           7053 |           2845
Late Spring Session    |        198 |                 38.72 |             67.80 |                  68.10 |            7667 |           5198 |           2134
Spring Session         |        156 |                 35.45 |             66.50 |                  67.20 |            5530 |           3677 |           1623
Winter Recess          |         45 |                  8.23 |             71.20 |                  71.80 |             370 |            263 |              95
Summer Recess          |         38 |                  5.67 |             69.40 |                  70.10 |             215 |            149 |              58

Intelligence Application: Identifies highest productivity periods (Autumn Session) and approval rate variations by season. Recess periods show higher approval rates but much lower volume, suggesting only consensus items are processed during these periods.

Performance Characteristics

Query Time: 200-500ms (5-year rolling window, daily granularity)
Refresh Frequency: Real-time (recalculates on each query)
Data Volume: ~1,825 rows (5 years × 365 days, filtered to days with decisions)
Optimization: Indexed on document_data.made_public_date for temporal queries

Data Sources

Primary Table: document_proposal_data (proposal decision text)
Join Path: document_proposal_data → document_proposal_container → document_status_container → document_data (dates)
Time Range: Last 5 years (configurable in view definition)

Intelligence Frameworks Applicable