What are Michigan legislators actually doing?

Row grain: calendar month.

WITH RECURSIVE
bounds AS (
    SELECT
        min(substr(sitting_date, 1, 7) || '-01') AS first_month,
        substr(:end_date, 1, 7) || '-01' AS last_month
    FROM sitting
    WHERE chamber = 'house' AND sitting_date <= :end_date
),
months(month_start) AS (
    SELECT first_month FROM bounds
    UNION ALL
    SELECT date(month_start, '+1 month')
    FROM months, bounds
    WHERE month_start < last_month
)
SELECT
    months.month_start,
    count(sitting.sitting_id) AS sittings,
    coalesce(sum(sitting.quorum_state = 'quorum_confirmed'), 0) AS quorum_confirmed,
    coalesce(sum(sitting.quorum_state = 'no_quorum_confirmed'), 0)
        AS no_quorum_confirmed,
    coalesce(sum(sitting.quorum_state = 'quorum_not_determined'), 0) AS undetermined,
    coalesce(
        round(
            100.0 * sum(sitting.quorum_state = 'no_quorum_confirmed')
                / count(sitting.sitting_id),
            1
        ),
        0.0
    ) AS percentage_all
FROM months
LEFT JOIN sitting
    ON sitting.chamber = 'house'
    AND sitting.sitting_date <= :end_date
    AND substr(sitting.sitting_date, 1, 7) || '-01' = months.month_start
GROUP BY months.month_start
ORDER BY months.month_start

Row grain: member_id and activity_date.

Floor-vote rule: At least one activity row with activity_type = floor_vote.

Other-activity rule: At least one attributable activity row with an activity_type other than floor_vote.

WITH member_days AS (
    SELECT
        member_id,
        activity_date,
        max(activity_type = 'floor_vote') AS has_floor_vote,
        max(activity_type <> 'floor_vote') AS has_other
    FROM activity
    WHERE activity_date BETWEEN '2019-01-01' AND '2025-12-31'
    GROUP BY member_id, activity_date
)
SELECT
    substr(activity_date, 1, 4) AS year,
    count(*) AS observed_member_days,
    sum(has_floor_vote) AS floor_vote_days,
    sum(has_other AND NOT has_floor_vote) AS non_vote_only_days,
    sum(has_other AND has_floor_vote) AS both_days,
    sum(has_floor_vote AND NOT has_other) AS vote_only_days
FROM member_days
GROUP BY year
ORDER BY year

Aggregation: Pooled numerator divided by pooled eligible denominator across member services within each calendar year.

SELECT
    substr(window_id, 10) AS year,
    metric_id,
    min(label) AS label,
    min(definition_version) AS definition_version,
    min(interpretation) AS interpretation,
    count(*) AS member_services,
    sum(numerator) AS numerator,
    sum(denominator) AS denominator,
    sum(unknown_count) AS unknown_count,
    sum(excluded_count) AS excluded_count
FROM metric
WHERE window_id BETWEEN 'calendar:2019' AND 'calendar:2025'
GROUP BY year, metric_id
ORDER BY metric_id, year

Aggregation: Completed observed units divided by collected units for each source surface, chamber, and biennium.

Floor sitting with journal link (collected sitting)

The denominator is collected sitting rows, not every sitting that may have occurred outside the collected calendar.

Floor sitting with linked media (collected sitting)

A link shows that at least one recording is associated with the sitting; it does not establish recording completeness.

Committee meeting with minutes link (collected committee meeting)

A minutes URL is source availability, not proof that the document was acquired, parsed, or contains a member roster.

Committee meeting with linked media (collected committee meeting)

A media link is not proof that every proceeding or the entire meeting was recorded.

Media asset with duration (dated chamber media asset)

Duration metadata supports triage and playback but does not prove that a recording is complete.

Media asset with transcript (dated chamber media asset)

A machine transcript may contain recognition or speaker errors and does not imply human review.

WITH coverage_units AS (
    SELECT
        sitting_date AS observation_date,
        chamber,
        'floor_journal_link' AS source_surface,
        journal_object_name IS NOT NULL AS completed
    FROM sitting
    UNION ALL
    SELECT
        sitting_date,
        chamber,
        'floor_media_link',
        linked_media_count > 0
    FROM sitting
    UNION ALL
    SELECT
        meeting_date,
        committee.chamber,
        'committee_minutes_link',
        minutes_url IS NOT NULL
    FROM committee_meeting
    JOIN committee USING (committee_id)
    WHERE committee.chamber IN ('house', 'senate')
    UNION ALL
    SELECT
        meeting_date,
        committee.chamber,
        'committee_media_link',
        media_count > 0
    FROM committee_meeting
    JOIN committee USING (committee_id)
    WHERE committee.chamber IN ('house', 'senate')
    UNION ALL
    SELECT
        media.recorded_date,
        coalesce(media.chamber, committee.chamber),
        'media_duration_recorded',
        media.duration_seconds IS NOT NULL
    FROM media
    LEFT JOIN committee_meeting
        ON committee_meeting.meeting_id = media.related_meeting_id
    LEFT JOIN committee ON committee.committee_id = committee_meeting.committee_id
    WHERE media.recorded_date IS NOT NULL
        AND coalesce(media.chamber, committee.chamber) IN ('house', 'senate')
    UNION ALL
    SELECT
        media.recorded_date,
        coalesce(media.chamber, committee.chamber),
        'media_transcript_available',
        EXISTS (
            SELECT 1 FROM transcript WHERE transcript.media_id = media.media_id
        )
    FROM media
    LEFT JOIN committee_meeting
        ON committee_meeting.meeting_id = media.related_meeting_id
    LEFT JOIN committee ON committee.committee_id = committee_meeting.committee_id
    WHERE media.recorded_date IS NOT NULL
        AND coalesce(media.chamber, committee.chamber) IN ('house', 'senate')
),
scoped AS (
    SELECT
        printf(
            '%d-%d',
            cast(substr(observation_date, 1, 4) AS integer)
                - ((cast(substr(observation_date, 1, 4) AS integer) + 1) % 2),
            cast(substr(observation_date, 1, 4) AS integer)
                - ((cast(substr(observation_date, 1, 4) AS integer) + 1) % 2) + 1
        ) AS biennium,
        chamber,
        source_surface,
        completed
    FROM coverage_units
    WHERE observation_date BETWEEN '2019-01-01' AND '2026-07-27'
)
SELECT
    biennium,
    chamber,
    source_surface,
    sum(completed) AS completed,
    count(*) AS total
FROM scoped
GROUP BY biennium, chamber, source_surface
ORDER BY source_surface, biennium, chamber

SELECT
    'committee_vote' AS record_type,
    committee_vote_id AS record_id,
    meeting_id AS parent_id,
    validation_state,
    motion_text AS label
FROM committee_vote
WHERE validation_state IN (
    'source_total_conflict',
    'source_member_choice_conflict'
)
UNION ALL
SELECT
    'committee_document' AS record_type,
    document_id AS record_id,
    related_meeting_id AS parent_id,
    validation_state,
    title AS label
FROM committee_document
WHERE validation_state = 'source_kind_mismatch'
UNION ALL
SELECT
    'meeting_participation' AS record_type,
    participation_id AS record_id,
    meeting_id AS parent_id,
    review_state AS validation_state,
    source_label AS label
FROM meeting_participation
WHERE review_state = 'source_placeholder'
ORDER BY record_type, validation_state, record_id