Как считаются показатели

-- Stage 4 metrics, step 1: the votes the metrics count.
-- Runs in PostgreSQL over the tables of sql/schema.sql (scripts/03-load.ts). Tables created here start with m_.
-- {{PERIOD_START}} is PERIOD_START from config.ts: a date such as '2023-10-07', or null for the whole term.

create index m_idx_result_vote_mk on kns_plenum_vote_result (vote_id, mk_id);
create index m_idx_result_mk on kns_plenum_vote_result (mk_id);

-- One row per vote of the 25th Knesset (the load keeps one copy of a repeated vote header).
-- vote_day is the Israel calendar date: the API timestamp carries the Israel offset, so it is the first 10 characters.
-- has_rows: the API has at least one per-member row for the vote. Secret votes and votes by show of hands have none.
create table m_vote as
select v.id as vote_id,
       substr(v.vote_date_time, 1, 10) as vote_day,
       v.vote_method_id,
       exists (select 1 from kns_plenum_vote_result r where r.vote_id = v.id) as has_rows
from kns_plenum_vote v
where {{PERIOD_START}}::text is null
   or substr(v.vote_date_time, 1, 10) >= {{PERIOD_START}};

-- Stage 4 metrics, step 2: terms of office and faction membership.

-- Terms of office: KNS_PersonToPosition rows of the 25th Knesset with PositionID 43 (חבר הכנסת) or 61 (חברת הכנסת).
-- Dates are compared as Israel calendar dates; the time of day that five rows carry is not used.
create table m_mk_period as
select id as position_row_id,
       person_id,
       substr(start_date, 1, 10) as start_day,
       substr(finish_date, 1, 10) as finish_day
from kns_person_to_position
where knesset_num = 25 and position_id in (43, 61);

-- The participation denominator: for each member, the votes with per-member rows held strictly after the day a
-- term started and strictly before the day it finished. The API gives these dates as calendar days (the few rows with
-- a time of day are compared by date only), so on the day one member leaves and another arrives it is not known which
-- votes came before the change; that day is left out for both of them.
create table m_mk_vote as
select distinct p.person_id, v.vote_id
from m_mk_period p
join m_vote v
  on v.has_rows
 and v.vote_day > p.start_day
 and (p.finish_day is null or v.vote_day < p.finish_day);

-- Members in office on a vote day, for vote pages: from the start day up to, but not including, the finish day.
-- With this rule there are exactly 120 members on every vote day of the term.
create table m_in_office as
select distinct p.person_id, d.vote_day
from m_mk_period p
join (select distinct vote_day from m_vote) d
  on d.vote_day >= p.start_day
 and (p.finish_day is null or d.vote_day < p.finish_day);

-- Faction membership: rows with PositionID 54.
create table m_faction_period as
select id as position_row_id,
       person_id,
       faction_id,
       substr(start_date, 1, 10) as start_day,
       substr(finish_date, 1, 10) as finish_day
from kns_person_to_position
where knesset_num = 25 and position_id = 54;

-- Every (member, vote day) that needs a faction: the member has a row that day or is in office that day.
create table m_person_day as
select r.mk_id as person_id, v.vote_day
from kns_plenum_vote_result r
join m_vote v on v.vote_id = r.vote_id
union
select person_id, vote_day from m_in_office;

-- The faction on that day: the faction row covering the day from its start day up to, but not including, its
-- finish day. If none covers it (the member's last day in office), the faction row that finishes that day.
-- covering_rows counts the rows of the first kind; the metrics script stops if it is ever above 1.
create table m_person_day_faction as
with candidate as (
  select d.person_id, d.vote_day, f.faction_id, 1 as priority
  from m_person_day d
  join m_faction_period f
    on f.person_id = d.person_id
   and f.start_day <= d.vote_day
   and (f.finish_day is null or d.vote_day < f.finish_day)
  union all
  select d.person_id, d.vote_day, f.faction_id, 2 as priority
  from m_person_day d
  join m_faction_period f
    on f.person_id = d.person_id
   and f.finish_day = d.vote_day
)
select person_id,
       vote_day,
       (array_agg(faction_id order by priority, faction_id))[1] as faction_id,
       (count(*) filter (where priority = 1))::int as covering_rows
from candidate
group by person_id, vote_day;

-- Stage 4 metrics, step 3: participation in votes.
--
--   participation = votes_recorded / votes_in_denominator
--
-- votes_in_denominator: votes with per-member rows held while the person was a member (m_mk_vote, step 2).
-- votes_recorded: those of them where the person has a row with ResultCode 7 (בעד, «за»), 8 (נגד, «против») or
-- 9 (נמנע, «воздержался»). A row with ResultCode 6 (נוכח, «присутствует») is not counted as a recorded vote and is
-- shown separately. No row means the API has no record of the person in that vote.
create table m_participation as
select p.person_id,
       count(e.vote_id)::int as votes_in_denominator,
       (count(r.id) filter (where r.result_code in (7, 8, 9)))::int as votes_recorded,
       (count(r.id) filter (where r.result_code = 7))::int as for_rows,
       (count(r.id) filter (where r.result_code = 8))::int as against_rows,
       (count(r.id) filter (where r.result_code = 9))::int as abstain_rows,
       (count(r.id) filter (where r.result_code = 6))::int as present_rows,
       (count(r.id) filter (where r.result_code not in (6, 7, 8, 9)))::int as other_code_rows,
       (count(e.vote_id) - count(r.id))::int as votes_without_row
from (select distinct person_id from m_mk_period) p
left join m_mk_vote e on e.person_id = p.person_id
left join kns_plenum_vote_result r on r.vote_id = e.vote_id and r.mk_id = e.person_id
group by p.person_id;

-- The same two counts by calendar year of the vote.
create table m_participation_by_year as
select e.person_id,
       substr(v.vote_day, 1, 4) as year,
       count(*)::int as votes_in_denominator,
       (count(r.id) filter (where r.result_code in (7, 8, 9)))::int as votes_recorded
from m_mk_vote e
join m_vote v on v.vote_id = e.vote_id
left join kns_plenum_vote_result r on r.vote_id = e.vote_id and r.mk_id = e.person_id
group by e.person_id, substr(v.vote_day, 1, 4);

-- Rows that fall outside the person's denominator (expected: only rows on the first or last day of a term).
create table m_rows_outside_denominator as
select r.id as result_id, r.vote_id, r.mk_id as person_id, r.result_code, v.vote_day
from kns_plenum_vote_result r
join m_vote v on v.vote_id = r.vote_id
left join m_mk_vote e on e.vote_id = r.vote_id and e.person_id = r.mk_id
where e.vote_id is null;

-- Stage 4 metrics, step 4: voting differently from the faction.

-- Rows with «за», «против» or «воздержался» inside the person's participation denominator (m_mk_vote, step 2), with
-- the person's faction on the vote day. Rows from the first or last day of a term are left out here as well, so
-- divergence, unity and participation count the same votes.
create table m_row_faction as
select r.id as result_id, r.vote_id, r.mk_id as person_id, r.result_code, d.faction_id
from kns_plenum_vote_result r
join m_vote v on v.vote_id = r.vote_id
join m_mk_vote e on e.vote_id = r.vote_id and e.person_id = r.mk_id
left join m_person_day_faction d on d.person_id = r.mk_id and d.vote_day = v.vote_day
where r.result_code in (7, 8, 9);

-- Each faction in each vote: how many of its members are recorded and how. majority_code is the position taken by
-- more than half of the recorded members; null if no position has more than half.
create table m_faction_vote as
select vote_id,
       faction_id,
       count(*)::int as recorded,
       (count(*) filter (where result_code = 7))::int as for_rows,
       (count(*) filter (where result_code = 8))::int as against_rows,
       (count(*) filter (where result_code = 9))::int as abstain_rows,
       case
         when 2 * count(*) filter (where result_code = 7) > count(*) then 7
         when 2 * count(*) filter (where result_code = 8) > count(*) then 8
         when 2 * count(*) filter (where result_code = 9) > count(*) then 9
       end as majority_code
from m_row_faction
where faction_id is not null
group by vote_id, faction_id;

-- Divergence from the faction, per person:
--
--   divergence = diverged_votes / comparable_votes
--
-- comparable_votes: votes where the person is recorded («за», «против», «воздержался») and the person's faction has at
-- least 3 recorded members (the person included) and a majority position.
-- diverged_votes: those of them where the person's record differs from the majority position; «воздержался» against
-- a majority «за» counts as different.
create table m_divergence as
select rf.person_id,
       (count(*) filter (where fv.recorded >= 3 and fv.majority_code is not null))::int as comparable_votes,
       (count(*) filter (where fv.recorded >= 3 and fv.majority_code is not null and rf.result_code <> fv.majority_code))::int as diverged_votes,
       (count(*) filter (where fv.recorded >= 3 and fv.majority_code is null))::int as votes_faction_without_majority,
       (count(*) filter (where fv.recorded < 3))::int as votes_faction_under_3_recorded
from m_row_faction rf
join m_faction_vote fv on fv.vote_id = rf.vote_id and fv.faction_id = rf.faction_id
group by rf.person_id;

-- The rows counted as diverged_votes, for the member pages.
create table m_diverged_row as
select rf.result_id, rf.vote_id, rf.person_id, rf.faction_id, rf.result_code,
       fv.majority_code, fv.recorded, fv.for_rows, fv.against_rows, fv.abstain_rows
from m_row_faction rf
join m_faction_vote fv on fv.vote_id = rf.vote_id and fv.faction_id = rf.faction_id
where fv.recorded >= 3 and fv.majority_code is not null and rf.result_code <> fv.majority_code;

-- Stage 4 metrics, step 5: bills and parliamentary questions.

-- Bills of the 25th Knesset where the person is listed as an initiator (KNS_BillInitiator.IsInitiator = true).
-- Rows with IsInitiator = false (members who joined a bill) are not counted.
-- KNS_Bill has no submission date, so PERIOD_START does not narrow this count.
create table m_bills as
select bi.person_id, count(distinct bi.bill_id)::int as bills_initiated
from kns_bill_initiator bi
join kns_bill b on b.id = bi.bill_id
where b.knesset_num = 25 and bi.is_initiator
group by bi.person_id;

-- Parliamentary questions (שאילתות, KNS_Query) of the 25th Knesset submitted by the person, by type.
create table m_queries as
select person_id,
       type_id,
       trim(type_desc) as type_desc,
       count(*)::int as queries
from kns_query
where knesset_num = 25
  and ({{PERIOD_START}}::text is null or substr(submit_date, 1, 10) >= {{PERIOD_START}})
group by person_id, type_id, trim(type_desc);

-- Stage 4 metrics, step 6: faction aggregates. A person counts for the faction they belonged to on the day.

-- Participation of the faction's members: every (member, vote) of a member's denominator counts for the member's
-- faction on the vote day.
create table m_faction_participation as
select d.faction_id,
       count(*)::int as member_votes_in_denominator,
       (count(r.id) filter (where r.result_code in (7, 8, 9)))::int as member_votes_recorded,
       count(distinct e.person_id)::int as members
from m_mk_vote e
join m_vote v on v.vote_id = e.vote_id
join m_person_day_faction d on d.person_id = e.person_id and d.vote_day = v.vote_day
left join kns_plenum_vote_result r on r.vote_id = e.vote_id and r.mk_id = e.person_id
group by d.faction_id;

-- Unity: votes where at least 3 members of the faction are recorded, and those where all of them took the same
-- position.
create table m_faction_unity as
select faction_id,
       (count(*) filter (where recorded >= 3))::int as votes_3_or_more_recorded,
       (count(*) filter (where recorded >= 3 and greatest(for_rows, against_rows, abstain_rows) = recorded))::int as votes_all_same
from m_faction_vote
group by faction_id;

-- Divergence of the faction's members from the faction majority, summed over members (see step 4).
create table m_faction_divergence as
select rf.faction_id,
       (count(*) filter (where fv.recorded >= 3 and fv.majority_code is not null))::int as comparable_member_votes,
       (count(*) filter (where fv.recorded >= 3 and fv.majority_code is not null and rf.result_code <> fv.majority_code))::int as diverged_member_votes
from m_row_faction rf
join m_faction_vote fv on fv.vote_id = rf.vote_id and fv.faction_id = rf.faction_id
group by rf.faction_id;

-- Questions counted for the faction the person belonged to on the submission day (same rule as step 2).
-- Bills are not aggregated by faction: KNS_Bill has no submission date to tell which faction the initiator was in.
create table m_query_faction as
with q as (
  select id, person_id, substr(submit_date, 1, 10) as day
  from kns_query
  where knesset_num = 25
    and ({{PERIOD_START}}::text is null or substr(submit_date, 1, 10) >= {{PERIOD_START}})
),
candidate as (
  select q.id, f.faction_id, 1 as priority
  from q join m_faction_period f
    on f.person_id = q.person_id and f.start_day <= q.day and (f.finish_day is null or q.day < f.finish_day)
  union all
  select q.id, f.faction_id, 2 as priority
  from q join m_faction_period f
    on f.person_id = q.person_id and f.finish_day = q.day
)
select q.id as query_id, q.person_id, q.day, (array_agg(c.faction_id order by c.priority, c.faction_id))[1] as faction_id
from q
left join candidate c on c.id = q.id
group by q.id, q.person_id, q.day;

-- Stage 4 metrics, step 7: per-vote sums of the per-member rows. These are sums of rows in the API, not an announced
-- result: the API has no field with the result of a vote.
create table m_vote_tally as
select v.vote_id,
       v.vote_day,
       v.has_rows,
       count(r.id)::int as rows,
       (count(r.id) filter (where r.result_code = 7))::int as for_rows,
       (count(r.id) filter (where r.result_code = 8))::int as against_rows,
       (count(r.id) filter (where r.result_code = 9))::int as abstain_rows,
       (count(r.id) filter (where r.result_code = 6))::int as present_rows,
       (select count(*) from m_in_office o where o.vote_day = v.vote_day)::int as in_office
from m_vote v
left join kns_plenum_vote_result r on r.vote_id = v.vote_id
group by v.vote_id, v.vote_day, v.has_rows;