会議録のある地方議会
70 / 1,794
都道府県と市区町村の議会のうち、会議が 1 件以上ある議会
会議
315K
発言
28.9M
議案
134K
賛否
132K
政治家
20K

議会別の収録状況

議会名種別ソース会議数発言数初回会議日最終会議日発言 0 件率政治家数
北海道都道府県pref-hokkaido.gijiroku.com10,740705,0931970-01-062026-07-035.4%241
北海道札幌市市区町村sapporo.gijiroku.com4,095242,8331987-01-192026-07-310.0%76
青森県都道府県pref.aomori.dbsr.jp4,824405,4251983-03-162026-07-2121.7%114
岩手県都道府県iwatekengikai.gijiroku.com2,920315,0091995-02-142026-09-010.0%91
宮城県都道府県kaigiroku.net6,749470,3121947-05-282026-07-060.3%114
宮城県仙台市市区町村city.sendai.miyagi.dbsr.jp1,17861,3842003-01-172026-06-266.4%0
秋田県都道府県kaigiroku.net4,974418,9891999-05-112026-07-0310.5%90
山形県都道府県kaigiroku.net1,50683,1851992-11-272026-06-0111.2%113
福島県都道府県kaigiroku.net1,08438,5792000-02-012026-02-2731.3%8
茨城県都道府県pref.ibaraki.dbsr.jp3,550365,3771989-02-282026-08-193.0%5
栃木県都道府県pref-tochigi.gijiroku.com3,441267,9151999-02-202026-08-044.7%122
群馬県都道府県gijiroku.com1,16383,7241995-05-162026-06-1222.4%118
埼玉県都道府県kaigiroku.net1,539134,5621979-02-152026-02-012.2%288
埼玉県さいたま市市区町村kaigiroku.net3,846502,5742001-05-152026-06-2614.6%59
埼玉県川口市市区町村kaigiroku.net2,68469,2891989-01-072026-06-3066.4%0
千葉県都道府県pref-chiba.gijiroku.com3,504448,9001992-09-222026-07-0724.5%245
千葉県千葉市市区町村city.chiba.chiba.dbsr.jp2,961500,5022003-01-302026-07-143.6%92
東京都都道府県record.gikai.metro.tokyo.lg.jp977153,9781999-02-092026-09-300.0%9
神奈川県都道府県kaigiroku.net96997,6402003-03-202026-05-181.1%295
神奈川県横浜市市区町村giji.city.yokohama.lg.jp6,147841,0991991-02-142026-06-020.0%210
神奈川県川崎市市区町村gijiroku.com4,990648,0231986-03-142026-09-030.0%60
神奈川県相模原市市区町村kaigiroku.net1,094133,1632019-01-092026-07-010.0%9
新潟県都道府県kaigiroku.net5,348355,6281947-05-302026-06-012.7%140
新潟県新潟市市区町村gijiroku.com6,045601,0161999-05-252026-10-010.0%58
富山県都道府県pref.toyama.dbsr.jp2,497149,3801994-03-042026-06-260.0%101
石川県都道府県pref-ishikawa.gijiroku.com3,872220,8751991-05-092026-09-3024.1%96
福井県都道府県pref.fukui.dbsr.jp4,384261,2462000-06-282026-07-2151.5%88
山梨県都道府県pref.yamanashi.dbsr.jp99250,9261992-02-262026-07-060.0%104
長野県都道府県nagano.gijiroku.com3,868303,4041995-02-142026-07-034.1%152
岐阜県都道府県kaigiroku.net1,950110,1221989-03-012026-03-1624.4%104
静岡県都道府県pref.shizuoka.jp76489,8461999-05-192026-06-300.7%173
静岡県静岡市市区町村city.shizuoka.shizuoka.dbsr.jp1,52978,7482003-04-152026-07-095.8%8
静岡県浜松市市区町村kaigiroku.net2,732276,9831995-03-022026-06-237.8%51
愛知県都道府県pref.aichi.dbsr.jp6,340180,8001987-05-262026-07-230.0%259
愛知県名古屋市市区町村kaigiroku.net6,161496,4842003-05-142026-07-060.0%129
愛知県豊橋市市区町村kaigiroku.net2,925205,6431995-05-122026-07-310.0%36
三重県都道府県kensakusystem.jp1,475103,6171989-02-282026-06-304.0%113
滋賀県都道府県shigaken-gikai.jp3,001232,0021987-02-242026-08-106.4%119
京都府都道府県pref.kyoto.dbsr.jp1,84843,9982015-02-132026-07-1045.6%171
京都府京都市市区町村kaigiroku.net6,7481,197,3501991-05-012026-05-295.8%113
大阪府都道府県kaigiroku.net2,167123,9301971-05-312026-07-2252.8%314
大阪府大阪市市区町村kaigiroku.net7,613601,5851966-09-292026-06-119.8%128
大阪府堺市市区町村gijiroku.com3,312438,8751991-01-222026-09-180.0%0
兵庫県都道府県kensakusystem.jp5,876278,7241986-02-222026-09-305.6%244
兵庫県神戸市市区町村city.kobe.hyogo.dbsr.jp3,355233,6412003-01-162026-09-1616.2%0
奈良県都道府県kaigiroku.net1,09769,4111991-02-212026-06-301.9%121
和歌山県都道府県pref.wakayama.lg.jp1,11763,6301988-07-152026-06-260.0%91
鳥取県都道府県pref.tottori.dbsr.jp93869,1512003-02-172026-06-290.0%89
島根県都道府県pref.shimane.dbsr.jp77161,4012015-03-042026-07-022.1%86
岡山県都道府県kaigiroku.net95480,1421989-10-012026-06-090.0%106
岡山県岡山市市区町村kaigiroku.net2,470235,5901983-05-202026-09-110.0%0
広島県都道府県pref.hiroshima.dbsr.jp98246,5722015-01-132026-06-302.7%145
広島県広島市市区町村hiroshima.gijiroku.com4,825382,8931985-02-202026-06-261.7%63
山口県都道府県kaigiroku.net1,547123,8481996-02-282026-06-150.1%112
徳島県都道府県kaigiroku.net1,77695,8861991-05-142026-03-100.2%92
香川県都道府県pref.kagawa.dbsr.jp1,45893,9032000-02-242026-07-130.0%93
愛媛県都道府県kensakusystem.jp2,457176,7001991-06-272026-09-080.0%113
高知県都道府県kaigiroku.net3,900583,6381966-01-012026-07-301.9%103
福岡県都道府県pref.fukuoka.dbsr.jp1,40963,6172015-01-132026-09-177.9%235
福岡県北九州市市区町村kaigiroku.net2,164233,8061997-02-212026-03-250.1%0
福岡県福岡市市区町村city.fukuoka.fukuoka.dbsr.jp2,63368,3312003-01-162026-06-1879.4%0
佐賀県都道府県pref.saga.dbsr.jp1,14683,4102003-02-242026-07-017.6%74
長崎県都道府県kaigiroku.net2,159343,1171996-02-012026-04-231.1%121
熊本県都道府県kaigiroku.net1,97157,8421980-09-042026-06-2436.8%113
熊本県熊本市市区町村kumamoto.gijiroku.com3,672239,5801991-02-262026-08-240.1%76
大分県都道府県kaigiroku.net92679,1351989-03-012025-12-121.2%113
宮崎県都道府県kaigiroku.net1,075104,4872000-02-282026-06-180.7%93
鹿児島県都道府県pref.kagoshima.dbsr.jp806140,7652015-02-202026-07-087.9%142
鹿児島県鹿児島市市区町村kaigiroku.net2,520196,5801994-02-152026-09-1739.1%0
沖縄県都道府県kaigiroku.net1,678377,3082016-02-092026-08-195.7%86

会議数・発言数・政治家数は Sagebase に収録済みの件数で、議会が公開している総数ではありません。発言 0 件率は、議事録のうち発言を 1 件も切り出せていないものの割合です。高い議会は取り込みの欠落を疑ってください(既知の制限)。ソースは会議録を取得しているサイトのホスト名です。

地域別

色の付いた地域は会議録がある議会、薄い灰色はマスタのみの議会です。色の濃さは会議数を表します。都道府県をクリックすると市区町村の地図に切り替わり、上の表もその都道府県で絞り込まれます。

全国

地図を読み込み中…

マスタのみ 会議録あり: 少多(会議数)

年別の収録量

年 × 会議体の収録状況

読み込み中…

紐付け率

Speaker→Politician 紐付け率8.5%
会議録のある議会の割合 (国会含む全議会比)4%
会派マッピング率15.4%

データの鮮度

データテーブル最終追加日件数
開催主体データgoverning_bodies2026-02-121,795
政治家データpoliticians2026-10-1719,942
発言者データspeakers2026-10-21236,794
議案データproposals2026-10-21134,383
賛否データproposal_judges2026-10-04132,359
会議データmeetings2026-10-21315,252
発言データconversations2026-10-2128,918,644

集計に使った SQL

このページの数値は、BigQuery の公開データセットに対して次の SQL を週次で実行した結果です。

coverage
SELECT
            -- 発言者→政治家紐付け率
            (SELECT COUNTIF(politician_id IS NOT NULL) * 100.0 / NULLIF(COUNT(*), 0)
             FROM `sagebase-gcp.sagebase.speakers`) AS speaker_linkage_rate,
            -- 会派マッピング率
            (SELECT COUNT(DISTINCT pgp.parliamentary_group_id) * 100.0
                    / NULLIF(COUNT(DISTINCT pg.id), 0)
             FROM `sagebase-gcp.sagebase.parliamentary_groups` pg
             LEFT JOIN `sagebase-gcp.sagebase.parliamentary_group_parties` pgp
                 ON pg.id = pgp.parliamentary_group_id) AS pg_mapping_rate
            -- 議会の割合は kpi クエリの covered_bodies / governing_bodies_total
freshness
SELECT 'conversations' AS table_name, MAX(created_at) AS last_data_at, COUNT(*) AS record_count FROM `sagebase-gcp.sagebase.conversations` UNION ALL SELECT 'meetings' AS table_name, MAX(created_at) AS last_data_at, COUNT(*) AS record_count FROM `sagebase-gcp.sagebase.meetings` UNION ALL SELECT 'proposals' AS table_name, MAX(created_at) AS last_data_at, COUNT(*) AS record_count FROM `sagebase-gcp.sagebase.proposals` UNION ALL SELECT 'proposal_judges' AS table_name, MAX(created_at) AS last_data_at, COUNT(*) AS record_count FROM `sagebase-gcp.sagebase.proposal_judges` UNION ALL SELECT 'speakers' AS table_name, MAX(created_at) AS last_data_at, COUNT(*) AS record_count FROM `sagebase-gcp.sagebase.speakers` UNION ALL SELECT 'politicians' AS table_name, MAX(created_at) AS last_data_at, COUNT(*) AS record_count FROM `sagebase-gcp.sagebase.politicians` UNION ALL SELECT 'governing_bodies' AS table_name, MAX(created_at) AS last_data_at, COUNT(*) AS record_count FROM `sagebase-gcp.sagebase.governing_bodies`
heatmap_local
SELECT
            gb.name AS body_name,
            EXTRACT(YEAR FROM m.date) AS year,
            COUNT(DISTINCT conv.id) AS cnt
        FROM `sagebase-gcp.sagebase.conversations` conv
        JOIN `sagebase-gcp.sagebase.minutes` mi ON conv.minutes_id = mi.id
        JOIN `sagebase-gcp.sagebase.meetings` m ON mi.meeting_id = m.id
        JOIN `sagebase-gcp.sagebase.conferences` c ON m.conference_id = c.id
        JOIN `sagebase-gcp.sagebase.governing_bodies` gb ON c.governing_body_id = gb.id
        WHERE (gb.type IS NOT NULL AND gb.type != '国')
          AND m.date IS NOT NULL
        GROUP BY body_name, year
        ORDER BY body_name, year
heatmap_national
SELECT
            CONCAT(gb.name, ' ', c.name) AS body_name,
            EXTRACT(YEAR FROM m.date) AS year,
            COUNT(DISTINCT conv.id) AS cnt
        FROM `sagebase-gcp.sagebase.conversations` conv
        JOIN `sagebase-gcp.sagebase.minutes` mi ON conv.minutes_id = mi.id
        JOIN `sagebase-gcp.sagebase.meetings` m ON mi.meeting_id = m.id
        JOIN `sagebase-gcp.sagebase.conferences` c ON m.conference_id = c.id
        JOIN `sagebase-gcp.sagebase.governing_bodies` gb ON c.governing_body_id = gb.id
        WHERE gb.type = '国'
          AND m.date IS NOT NULL
        GROUP BY body_name, year
        ORDER BY body_name, year
kpi
SELECT
            (SELECT COUNT(*) FROM `sagebase-gcp.sagebase.conversations`) AS total_conversations,
            (SELECT COUNT(*) FROM `sagebase-gcp.sagebase.meetings`) AS total_meetings,
            (SELECT COUNT(*) FROM `sagebase-gcp.sagebase.proposals`) AS total_proposals,
            (SELECT COUNT(*) FROM `sagebase-gcp.sagebase.proposal_judges`) AS total_judges,
            (SELECT COUNT(*) FROM `sagebase-gcp.sagebase.politicians`) AS total_politicians,
            (SELECT COUNT(*) FROM `sagebase-gcp.sagebase.governing_bodies` gb
             WHERE (gb.type IS NOT NULL AND gb.type != '国')
            ) AS total_governing_bodies,
            (SELECT COUNT(*) FROM `sagebase-gcp.sagebase.governing_bodies`) AS total_governing_bodies_all,
            (SELECT COUNT(DISTINCT gb.id)
             FROM `sagebase-gcp.sagebase.meetings` m
             JOIN `sagebase-gcp.sagebase.conferences` c ON m.conference_id = c.id
             JOIN `sagebase-gcp.sagebase.governing_bodies` gb
                 ON c.governing_body_id = gb.id) AS covered_bodies
municipalities
WITH muni_meeting_rows AS (
            SELECT
                gb.id AS gb_id,
                m.id AS meeting_id,
                m.date AS meeting_date,
                COALESCE(
    NULLIF(
        REGEXP_REPLACE(
            REGEXP_REPLACE(
                REGEXP_REPLACE(
                    LOWER(COALESCE(m.url, '')),
                    r'^https?://',
                    ''
                ),
                r'(?s)[:/].*',
                ''
            ),
            r'^(www[0-9]*|ssp)\.',
            ''
        ),
        ''
    ),
    'unknown'
) AS host
            FROM `sagebase-gcp.sagebase.governing_bodies` gb
            JOIN `sagebase-gcp.sagebase.conferences` c ON gb.id = c.governing_body_id
            JOIN `sagebase-gcp.sagebase.meetings` m ON c.id = m.conference_id
            WHERE (gb.type IS NOT NULL AND gb.type != '国')
        ),
        muni_meetings AS (
            SELECT
                gb_id,
                COUNT(*) AS meeting_count,
                MIN(meeting_date) AS first_meeting_date,
                MAX(meeting_date) AS last_meeting_date
            FROM muni_meeting_rows
            GROUP BY gb_id
        ),
        muni_hosts AS (
            SELECT gb_id, host, COUNT(*) AS n
            FROM muni_meeting_rows
            WHERE host != 'unknown'
            GROUP BY gb_id, host
        ),
        muni_source_host AS (
            SELECT
                gb_id,
                ARRAY_AGG(host ORDER BY n DESC, host LIMIT 1)[OFFSET(0)]
                    AS source_host
            FROM muni_hosts
            GROUP BY gb_id
        ),
        conv_per_minutes AS (
            SELECT minutes_id, COUNT(*) AS n
            FROM `sagebase-gcp.sagebase.conversations`
            GROUP BY minutes_id
        ),
        muni_minutes AS (
            SELECT
                mr.gb_id,
                SUM(cv.n) AS conversation_count,
                COUNTIF(mi.skip_reason IS NULL) AS minutes_count,
                COUNTIF(mi.skip_reason IS NULL AND cv.n IS NULL)
                    AS minutes_without_conversations
            FROM muni_meeting_rows mr
            JOIN `sagebase-gcp.sagebase.minutes` mi ON mr.meeting_id = mi.meeting_id
            LEFT JOIN conv_per_minutes cv ON mi.id = cv.minutes_id
            GROUP BY mr.gb_id
        ),
        muni_pols AS (
            SELECT
                gb.id AS gb_id,
                COUNT(DISTINCT pol_gb.politician_id) AS politician_count
            FROM (
            SELECT cm.politician_id AS politician_id,
                   c.governing_body_id AS governing_body_id
            FROM `sagebase-gcp.sagebase.conference_members` cm
            JOIN `sagebase-gcp.sagebase.conferences` c ON cm.conference_id = c.id
            WHERE cm.politician_id IS NOT NULL
            UNION DISTINCT
            SELECT em.politician_id AS politician_id,
                   e.governing_body_id AS governing_body_id
            FROM `sagebase-gcp.sagebase.election_members` em
            JOIN `sagebase-gcp.sagebase.elections` e ON em.election_id = e.id
            WHERE em.politician_id IS NOT NULL
        ) pol_gb
            JOIN `sagebase-gcp.sagebase.governing_bodies` gb
                ON pol_gb.governing_body_id = gb.id
            WHERE (gb.type IS NOT NULL AND gb.type != '国')
            GROUP BY gb.id
        )
        SELECT
            gb.prefecture,
            gb.name AS municipality_name,
            gb.organization_code,
            COALESCE(mmi.conversation_count, 0) AS conversation_count,
            COALESCE(mm.meeting_count, 0) AS meeting_count,
            COALESCE(mp.politician_count, 0) AS politician_count,
            mm.first_meeting_date,
            mm.last_meeting_date,
            COALESCE(mmi.minutes_count, 0) AS minutes_count,
            COALESCE(mmi.minutes_without_conversations, 0)
                AS minutes_without_conversations,
            msh.source_host
        FROM `sagebase-gcp.sagebase.governing_bodies` gb
        LEFT JOIN muni_meetings mm ON gb.id = mm.gb_id
        LEFT JOIN muni_pols mp ON gb.id = mp.gb_id
        LEFT JOIN muni_minutes mmi ON gb.id = mmi.gb_id
        LEFT JOIN muni_source_host msh ON gb.id = msh.gb_id
        WHERE (gb.type IS NOT NULL AND gb.type != '国')
        ORDER BY gb.prefecture, gb.name
prefectures
WITH pref_meetings AS (
        SELECT
            gb.prefecture,
            COUNT(DISTINCT m.id) AS meeting_count,
            MIN(m.date) AS earliest_date,
            MAX(m.date) AS latest_date
        FROM `sagebase-gcp.sagebase.governing_bodies` gb
        JOIN `sagebase-gcp.sagebase.conferences` c ON gb.id = c.governing_body_id
        JOIN `sagebase-gcp.sagebase.meetings` m ON c.id = m.conference_id
        WHERE (gb.type IS NOT NULL AND gb.type != '国')
          AND gb.prefecture IS NOT NULL
        GROUP BY gb.prefecture
    ),
    pref_conversations AS (
        SELECT
            gb.prefecture,
            COUNT(DISTINCT conv.id) AS conversation_count
        FROM `sagebase-gcp.sagebase.governing_bodies` gb
        JOIN `sagebase-gcp.sagebase.conferences` c ON gb.id = c.governing_body_id
        JOIN `sagebase-gcp.sagebase.meetings` m ON c.id = m.conference_id
        JOIN `sagebase-gcp.sagebase.minutes` mi ON m.id = mi.meeting_id
        JOIN `sagebase-gcp.sagebase.conversations` conv ON mi.id = conv.minutes_id
        WHERE (gb.type IS NOT NULL AND gb.type != '国')
          AND gb.prefecture IS NOT NULL
        GROUP BY gb.prefecture
    ),
    pref_gb AS (
        SELECT
            gb.prefecture,
            COUNT(DISTINCT gb.id) AS governing_body_count
        FROM `sagebase-gcp.sagebase.governing_bodies` gb
        WHERE (gb.type IS NOT NULL AND gb.type != '国')
          AND gb.prefecture IS NOT NULL
        GROUP BY gb.prefecture
    ),
    pref_politicians AS (
        -- politicians.prefecture を直接集計(m:n 中間テーブル経由は採用しない)。
        -- 「全国区」(参議院全国比例) と空文字 (不明分類) は 47都道府県集計から除外。
        SELECT
            pol.prefecture,
            COUNT(DISTINCT pol.id) AS politician_count
        FROM `sagebase-gcp.sagebase.politicians` pol
        WHERE pol.prefecture IS NOT NULL
          AND pol.prefecture != ''
          AND pol.prefecture != '全国区'
        GROUP BY pol.prefecture
    ),
    pref_speakers AS (
        -- conversations 粒度のまま COUNTIF すると分子が発言数になり 100% を超える。
        -- speaker 単位に DISTINCT してから数える。
        SELECT
            prefecture,
            COUNT(*) AS speaker_count,
            COUNTIF(politician_id IS NOT NULL) AS matched_speaker_count
        FROM (
            SELECT DISTINCT gb.prefecture, s.id, s.politician_id
            FROM `sagebase-gcp.sagebase.speakers` s
            JOIN `sagebase-gcp.sagebase.conversations` conv ON s.id = conv.speaker_id
            JOIN `sagebase-gcp.sagebase.minutes` mi ON conv.minutes_id = mi.id
            JOIN `sagebase-gcp.sagebase.meetings` m ON mi.meeting_id = m.id
            JOIN `sagebase-gcp.sagebase.conferences` c ON m.conference_id = c.id
            JOIN `sagebase-gcp.sagebase.governing_bodies` gb ON c.governing_body_id = gb.id
            WHERE (gb.type IS NOT NULL AND gb.type != '国')
              AND gb.prefecture IS NOT NULL
        )
        GROUP BY prefecture
    ),
    pref_proposals AS (
        SELECT
            gb.prefecture,
            COUNT(DISTINCT p.id) AS proposal_count
        FROM `sagebase-gcp.sagebase.proposals` p
        JOIN `sagebase-gcp.sagebase.governing_bodies` gb ON p.governing_body_id = gb.id
        WHERE (gb.type IS NOT NULL AND gb.type != '国')
          AND gb.prefecture IS NOT NULL
        GROUP BY gb.prefecture
    )
    SELECT
        g.prefecture,
        g.governing_body_count,
        COALESCE(pc.conversation_count, 0) AS conversation_count,
        COALESCE(pm.meeting_count, 0) AS meeting_count,
        COALESCE(pp.politician_count, 0) AS politician_count,
        COALESCE(ps.speaker_count, 0) AS speaker_count,
        COALESCE(ps.matched_speaker_count, 0) AS matched_speaker_count,
        COALESCE(pr.proposal_count, 0) AS proposal_count,
        pm.earliest_date,
        pm.latest_date
    FROM pref_gb g
    LEFT JOIN pref_meetings pm ON g.prefecture = pm.prefecture
    LEFT JOIN pref_conversations pc ON g.prefecture = pc.prefecture
    LEFT JOIN pref_politicians pp ON g.prefecture = pp.prefecture
    LEFT JOIN pref_speakers ps ON g.prefecture = ps.prefecture
    LEFT JOIN pref_proposals pr ON g.prefecture = pr.prefecture
    ORDER BY g.prefecture
yearly_conversations
SELECT
            EXTRACT(YEAR FROM m.date) AS year,
            CASE
            WHEN gb.type = '国'
                 AND c.name LIKE '衆議院%' THEN 'shugi'
            WHEN gb.type = '国'
                 AND c.name LIKE '参議院%' THEN 'sangi'
            WHEN (gb.type IS NOT NULL AND gb.type != '国') THEN 'local'
            ELSE 'other'
        END AS chamber,
            COUNT(DISTINCT conv.id) AS cnt
        FROM `sagebase-gcp.sagebase.conversations` conv
        JOIN `sagebase-gcp.sagebase.minutes` mi ON conv.minutes_id = mi.id
        JOIN `sagebase-gcp.sagebase.meetings` m ON mi.meeting_id = m.id
        JOIN `sagebase-gcp.sagebase.conferences` c ON m.conference_id = c.id
        JOIN `sagebase-gcp.sagebase.governing_bodies` gb ON c.governing_body_id = gb.id
        WHERE m.date IS NOT NULL
        GROUP BY year, chamber
        ORDER BY year
yearly_judges
SELECT
            EXTRACT(YEAR FROM p.voted_date) AS year,
            CASE
            WHEN gb.type = '国'
                 AND c.name LIKE '衆議院%' THEN 'shugi'
            WHEN gb.type = '国'
                 AND c.name LIKE '参議院%' THEN 'sangi'
            WHEN (gb.type IS NOT NULL AND gb.type != '国') THEN 'local'
            ELSE 'other'
        END AS chamber,
            COUNT(pj.id) AS cnt
        FROM `sagebase-gcp.sagebase.proposal_judges` pj
        JOIN `sagebase-gcp.sagebase.proposals` p ON pj.proposal_id = p.id
        JOIN `sagebase-gcp.sagebase.governing_bodies` gb ON p.governing_body_id = gb.id
        LEFT JOIN `sagebase-gcp.sagebase.conferences` c ON p.conference_id = c.id
        WHERE p.voted_date IS NOT NULL
        GROUP BY year, chamber
        ORDER BY year
yearly_meetings
SELECT
            EXTRACT(YEAR FROM m.date) AS year,
            CASE
            WHEN gb.type = '国'
                 AND c.name LIKE '衆議院%' THEN 'shugi'
            WHEN gb.type = '国'
                 AND c.name LIKE '参議院%' THEN 'sangi'
            WHEN (gb.type IS NOT NULL AND gb.type != '国') THEN 'local'
            ELSE 'other'
        END AS chamber,
            COUNT(DISTINCT m.id) AS cnt
        FROM `sagebase-gcp.sagebase.meetings` m
        JOIN `sagebase-gcp.sagebase.conferences` c ON m.conference_id = c.id
        JOIN `sagebase-gcp.sagebase.governing_bodies` gb ON c.governing_body_id = gb.id
        WHERE m.date IS NOT NULL
        GROUP BY year, chamber
        ORDER BY year
yearly_proposals
SELECT
            EXTRACT(YEAR FROM p.submitted_date) AS year,
            CASE
            WHEN gb.type = '国'
                 AND c.name LIKE '衆議院%' THEN 'shugi'
            WHEN gb.type = '国'
                 AND c.name LIKE '参議院%' THEN 'sangi'
            WHEN (gb.type IS NOT NULL AND gb.type != '国') THEN 'local'
            ELSE 'other'
        END AS chamber,
            COUNT(*) AS cnt
        FROM `sagebase-gcp.sagebase.proposals` p
        JOIN `sagebase-gcp.sagebase.governing_bodies` gb ON p.governing_body_id = gb.id
        LEFT JOIN `sagebase-gcp.sagebase.conferences` c ON p.conference_id = c.id
        WHERE p.submitted_date IS NOT NULL
        GROUP BY year, chamber
        ORDER BY year