officer_total_comp (view)

Data license: Public Domain (IRS data)

46,735,262 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Next page

CREATE VIEW officer_total_comp AS
        SELECT
            o.id, o.object_id, o.ein, o.person_name, o.title, o.avg_hours_per_week,
            o.reportable_comp_filing_org,
            o.reportable_comp_related_org,
            o.other_compensation,
            o.benefits,
            o.expense_account,
            r.return_type, r.tax_year,
            -- total_comp: cross-form normalized "all compensation" rollup.
            -- For Form 990: rcfo + rcro + other_comp (the 3 IRS columns).
            -- For 990-EZ/990-PF: rcfo + benefits + expense_account (the 3 IRS columns).
            -- The 5-way COALESCE works because Form 990 has benefits/expense_account NULL,
            -- and 990-EZ/PF have rcro/other_comp NULL — only the populated columns sum.
            -- See decisions_log §59 caveats: may double-count related-org W-2 if related org
            -- also files its own 990 listing same person; for form-specific analysis use
            -- underlying columns directly.
            COALESCE(o.reportable_comp_filing_org, 0)
              + COALESCE(o.reportable_comp_related_org, 0)
              + COALESCE(o.other_compensation, 0)
              + COALESCE(o.benefits, 0)
              + COALESCE(o.expense_account, 0) AS total_comp
        FROM officers o
        JOIN returns r ON r.object_id = o.object_id;