Assigning authors to institutions

Excerpts from build/upstream/03_impute_affiliations.py. The thresholds --s-strong=0.75, --p-strong=4, --s-weak=0.50, --p-weak=2, and --window=3 are the production defaults.

Step 1 — author-year-institution counts (the raw vote tally each author carries with them):

-- one row per (author, year, institution): how many of the author's
-- papers in that year were raw-tagged with that institution.
CREATE TABLE ayi AS
SELECT
    AuthorID,
    year AS obs_year,
    InstitutionID,
    COUNT(DISTINCT PaperID) AS n_papers
FROM raw_obs                        -- restricted to type='education'
GROUP BY AuthorID, year, InstitutionID;

Step 2 — for each paper-author slot, the ±3-year window vote:

-- pool the author's per-year counts across [year-3, year+3]
CREATE TABLE window_evidence AS
SELECT s.PaperID, s.AuthorID, s.year,
       ay.InstitutionID,
       SUM(ay.n_papers) AS n_papers_w
FROM slots s
INNER JOIN ayi ay
  ON s.AuthorID = ay.AuthorID
 AND ay.obs_year BETWEEN s.year - 3 AND s.year + 3
GROUP BY s.PaperID, s.AuthorID, s.year, ay.InstitutionID;

-- pick the winning institution per slot (window-modal) and compute its share
CREATE TABLE slot_window AS
WITH totals AS (
    SELECT PaperID, AuthorID, year, SUM(n_papers_w) AS total_papers_w
    FROM window_evidence GROUP BY PaperID, AuthorID, year
),
ranked AS (
    SELECT we.*,
           ROW_NUMBER() OVER (
               PARTITION BY PaperID, AuthorID, year
               ORDER BY n_papers_w DESC, InstitutionID
           ) AS rn
    FROM window_evidence we
)
SELECT r.PaperID, r.AuthorID, r.year,
       r.InstitutionID AS modal_inst,
       r.n_papers_w     AS modal_papers,
       r.n_papers_w * 1.0 / NULLIF(t.total_papers_w, 0) AS modal_share
FROM ranked r JOIN totals t USING (PaperID, AuthorID, year)
WHERE r.rn = 1;

Step 3 — the priority cascade (strong-window → raw → weak-window → long-interp):

SELECT s.PaperID, s.AuthorID, s.year,
    CASE
        -- (a) strong-window rule: >= 75% share AND >= 4 papers
        WHEN sw.modal_share >= 0.75 AND sw.modal_papers >= 4
            THEN sw.modal_inst
        -- (b) raw paper-author-institution row from OpenAlex
        WHEN srp.raw_pick_inst IS NOT NULL
            THEN srp.raw_pick_inst
        -- (c) weak-window fill: no raw row, >= 50% AND >= 2 papers
        WHEN sw.modal_share >= 0.50 AND sw.modal_papers >= 2
            THEN sw.modal_inst
        -- (d) long interpolation: nearest pre and post year agree
        WHEN li.interp_inst IS NOT NULL
            THEN li.interp_inst
        ELSE NULL
    END AS InstitutionID,
    CASE
        WHEN sw.modal_share >= 0.75 AND sw.modal_papers >= 4 THEN
            CASE
                WHEN srp.raw_pick_inst = sw.modal_inst THEN 0   -- agreed with raw
                WHEN srp.raw_pick_inst IS NULL         THEN 1   -- filled missing
                ELSE 3                                          -- overrode raw
            END
        WHEN srp.raw_pick_inst IS NOT NULL                 THEN 0
        WHEN sw.modal_share >= 0.50 AND sw.modal_papers >= 2 THEN 1
        WHEN li.interp_inst IS NOT NULL                    THEN 2
        ELSE NULL
    END AS imputed
FROM slots s
LEFT JOIN slot_window      sw  USING (PaperID, AuthorID, year)
LEFT JOIN slot_raw_picked  srp USING (PaperID, AuthorID, year)
LEFT JOIN long_interp      li  USING (PaperID, AuthorID, year);

The long_interp helper is also a SQL block: it finds, per slot, the largest observation year strictly before the paper and the smallest strictly after, then keeps the slot only if both years' modal university match.

-- nearest pre/post observation years
CREATE TABLE slot_brackets AS
SELECT s.PaperID, s.AuthorID, s.year,
       MAX(CASE WHEN ay.obs_year < s.year THEN ay.obs_year END) AS before_year,
       MIN(CASE WHEN ay.obs_year > s.year THEN ay.obs_year END) AS after_year
FROM slots s
INNER JOIN ayi ay ON s.AuthorID = ay.AuthorID
GROUP BY s.PaperID, s.AuthorID, s.year;

-- assign only when both sides exist AND agree
CREATE TABLE long_interp AS
SELECT sb.PaperID, sb.AuthorID, sb.year, bm.modal_inst AS interp_inst
FROM slot_brackets sb
INNER JOIN ayi_modal bm ON sb.AuthorID = bm.AuthorID AND bm.obs_year = sb.before_year
INNER JOIN ayi_modal am ON sb.AuthorID = am.AuthorID AND am.obs_year = sb.after_year
WHERE sb.before_year IS NOT NULL
  AND sb.after_year  IS NOT NULL
  AND bm.modal_inst = am.modal_inst;