Who owns the medical industry: the Open Payments file nobody opens

-- Physician OWNERSHIP and investment interest in the medical industry — the
-- untouched third slice of CMS Open Payments. Fully reproducible query.
--
-- Question: when a physician (or an immediate family member) holds an ownership
-- or investment interest in a company that makes drugs or devices, the Sunshine
-- Act requires that company to disclose it. What does the resulting ownership
-- file look like — how much value does it hold, who holds it, and how evenly is
-- it spread? The lead figure: the top 1% of records (46 of 4,591) hold 62.3% of
-- the file's $1.34 billion in reported value of interest; the median stake is
-- just $19,440. Ownership is a coverage/disclosure record, NOT a quality, fraud,
-- or wrongdoing signal of any kind.
--
-- Source:
--   public.cms_open_payments WHERE record_type = 'ownership' — the OP_DTL_OWNRSHP
--     disclosure file of CMS Open Payments (Sunshine Act §6002), published
--     annually at openpaymentsdata.cms.gov. 4,591 ownership records for program
--     year 2024; payment_publication_date 2026-01-23. Public, read-only.
--     License: US-Government-Works (17 U.S.C. Sec. 105).
--     methodology_version = 'open-payments/v1'.
--
-- Universe: this study reads the ownership slice of program year 2024 AS A WHOLE
--   — every row is one disclosed physician/immediate-family ownership or
--   investment interest in a reporting company. value_of_interest_usd is the
--   reported worth of the interest; total_amount_usd on ownership rows is the
--   Total_Amount_Invested_USDollars (dollars the physician actually put in).
--   The two are different measures and are reported separately below.
--
-- Counting note: 3,965 distinct NPIs across 4,591 records (a physician can hold
--   interests in more than one company). 2 rows carry no NPI. No individual is
--   named, ranked, or scored in the study; every figure is an aggregate.

-- ============================================================================
-- (1) Universe reconciliation — the ownership file at a glance.
-- ============================================================================
SELECT
  count(*)                                                          AS records,
  count(DISTINCT recipient_npi)                                     AS distinct_npi,
  count(*) FILTER (WHERE recipient_npi IS NULL OR recipient_npi = '') AS null_npi,
  count(DISTINCT manufacturer_id)                                   AS companies,
  count(DISTINCT recipient_specialty)                              AS specialties,
  count(DISTINCT recipient_state)                                   AS states,
  round(sum(value_of_interest_usd)::numeric, 0)                     AS value_of_interest,
  round(sum(total_amount_usd)::numeric, 0)                          AS amount_invested,
  max(payment_publication_date)                                     AS published
FROM public.cms_open_payments
WHERE record_type = 'ownership';
--  records 4,591 · distinct_npi 3,965 · null_npi 2 · companies 209
--  specialties 121 · states 50 · value_of_interest 1,336,244,024
--  amount_invested 147,781,836 · published 2026-01-23

-- ============================================================================
-- (2) HEADLINE: concentration of value. The file is a power law — the largest
--     single record is 19.5% of all value, the top 10 records 43.5%, and the
--     top 1% of records (46) hold 62.3%. The bottom 90% hold 11.5%. Mean value
--     ($291,057) is 15x the median ($19,440): a short head, a very long tail.
-- ============================================================================
WITH r AS (
  SELECT value_of_interest_usd AS v,
    row_number() OVER (ORDER BY value_of_interest_usd DESC NULLS LAST) AS rn,
    sum(value_of_interest_usd) OVER ()                               AS total
  FROM public.cms_open_payments
  WHERE record_type = 'ownership'
)
SELECT
  round(100.0 * sum(v) FILTER (WHERE rn = 1)   / max(total), 1)      AS top1_record_pct,
  round(100.0 * sum(v) FILTER (WHERE rn <= 10) / max(total), 1)      AS top10_record_pct,
  round(100.0 * sum(v) FILTER (WHERE rn <= 46) / max(total), 1)      AS top1pct_pct,    -- 1% of 4,591
  round(100.0 * sum(v) FILTER (WHERE rn <= 459) / max(total), 1)     AS top10pct_pct,   -- 10% of 4,591
  round(percentile_cont(0.5) WITHIN GROUP (ORDER BY v)::numeric, 0)  AS median_value,
  round(avg(v)::numeric, 0)                                          AS mean_value
FROM r;
--  top1_record 19.5% · top10_record 43.5% · top1pct 62.3% · top10pct 88.5%
--  median_value 19,440 · mean_value 291,057   (bottom 90% of records = 11.5%)

-- ============================================================================
-- (3) Value of INTEREST vs amount INVESTED. The file reports $1.34B of value
--     against $147.8M actually invested — a 9.0x ratio — and 73.4% of records
--     (3,371) report a value of interest with ZERO dollars invested that year.
--     These are standing equity stakes whose worth is disclosed, not fresh
--     capital that changed hands in 2024.
-- ============================================================================
SELECT
  round(sum(value_of_interest_usd)::numeric, 0)                     AS value_of_interest,
  round(sum(total_amount_usd)::numeric, 0)                          AS amount_invested,
  round(sum(value_of_interest_usd)::numeric
        / nullif(sum(total_amount_usd), 0), 1)                      AS value_to_invest_ratio,
  count(*) FILTER (WHERE (total_amount_usd IS NULL OR total_amount_usd = 0)
                     AND value_of_interest_usd > 0)                 AS value_no_invest,
  round(100.0 * count(*) FILTER (WHERE (total_amount_usd IS NULL OR total_amount_usd = 0)
                     AND value_of_interest_usd > 0) / count(*), 1)  AS value_no_invest_pct
FROM public.cms_open_payments
WHERE record_type = 'ownership';
--  value_of_interest 1,336,244,024 · amount_invested 147,781,836 · ratio 9.0x
--  value_no_invest 3,371 · value_no_invest_pct 73.4%

-- ============================================================================
-- (4) TWO PATTERNS in one file. Ranked by RECORD COUNT the leaders are
--     physician-group / GPO entities where hundreds of physician-owners each
--     report a small or token interest; ranked by VALUE the leaders are
--     device and biotech makers where a few owners hold multimillion-dollar
--     equity. Unified Women's Healthcare: 875 records, $875 of value in total
--     (~$1 each). The two rankings barely overlap.
-- ============================================================================
-- By record count:
SELECT manufacturer_name,
  count(*)                                                          AS records,
  round(sum(value_of_interest_usd)::numeric, 0)                     AS value_of_interest
FROM public.cms_open_payments
WHERE record_type = 'ownership'
GROUP BY manufacturer_name
ORDER BY records DESC
LIMIT 6;
--  Unified Women's Healthcare, LP    875        875
--  GastroGPO, LLC                    686 15,123,999
--  SN Holdings, LLC                  480 32,734,155
--  US Retina LLC                     191 30,870,471
--  Cornerstone Specialty Network LLC 124    735,294
--  Spinal Simplicity, LLC            117 36,390,846

-- By value of interest (209 companies in the file; top 10 firms hold 60.1% of
--  records, the top firm holds 875 records / 19.1% of all records):
SELECT manufacturer_name,
  count(*)                                                          AS records,
  round(sum(value_of_interest_usd)::numeric, 0)                     AS value_of_interest,
  round(100.0 * sum(value_of_interest_usd)
        / sum(sum(value_of_interest_usd)) OVER (), 1)               AS pct_of_value
FROM public.cms_open_payments
WHERE record_type = 'ownership'
GROUP BY manufacturer_name
ORDER BY value_of_interest DESC
LIMIT 6;
--  Midmark Corporation            3 385,901,836 28.9%
--  TriMed, Inc.                   2 110,000,000  8.2%
--  Innovation Technologies Inc   71  75,247,808  5.6%
--  Imperative Care, Inc          21  39,887,900  3.0%
--  Scientia Vascular             32  39,081,890  2.9%
--  Spinal Simplicity, LLC       117  36,390,846  2.7%

-- ============================================================================
-- (5) Ownership is a procedural/surgical-physician story. 85.9% of records sit
--     with procedural and surgical specialties; obstetrics & gynecology,
--     gastroenterology, urology and orthopaedic surgery alone are 54.7% of the
--     file by record count.
-- ============================================================================
WITH g AS (
  SELECT
    CASE WHEN recipient_specialty ILIKE '%Surgery%' OR recipient_specialty ILIKE '%Orthopaedic%'
           OR recipient_specialty ILIKE '%Urology%' OR recipient_specialty ILIKE '%Gastroenterology%'
           OR recipient_specialty ILIKE '%Ophthalmology%' OR recipient_specialty ILIKE '%Cardiolog%'
           OR recipient_specialty ILIKE '%Cardiovascular%' OR recipient_specialty ILIKE '%Obstetrics%'
           OR recipient_specialty ILIKE '%Otolaryngology%' OR recipient_specialty ILIKE '%Dermatology%'
           OR recipient_specialty ILIKE '%Radiology%' OR recipient_specialty ILIKE '%Pain%'
         THEN 'procedural/surgical' ELSE 'other' END                AS grp,
    value_of_interest_usd                                           AS v
  FROM public.cms_open_payments
  WHERE record_type = 'ownership'
)
SELECT grp,
  count(*)                                                          AS records,
  round(100.0 * count(*) / sum(count(*)) OVER (), 1)                AS pct_records,
  round(100.0 * sum(v) / sum(sum(v)) OVER (), 1)                    AS pct_value
FROM g GROUP BY grp ORDER BY records DESC;
--  procedural/surgical 3,943 · 85.9% records · 56.3% value
--  other                 648 · 14.1% records · 43.7% value (value skewed by outlier records)

-- Top specialties by record count (the hero-chart bars):
SELECT recipient_specialty,
  count(*)                                                          AS records,
  round(100.0 * count(*) / sum(count(*)) OVER (), 1)                AS pct_records
FROM public.cms_open_payments
WHERE record_type = 'ownership'
GROUP BY recipient_specialty
ORDER BY records DESC
LIMIT 6;
--  Obstetrics & Gynecology              816  17.8%
--  Internal Medicine|Gastroenterology   686  14.9%
--  Urology                              593  12.9%
--  Orthopaedic Surgery                  417   9.1%
--  Ophthalmology                        208   4.5%
--  Neurological Surgery                 163   3.6%

-- ============================================================================
-- (6) WHERE the owners are — top states by record count, each with its own
--     share of the file's value. Texas leads by count (13.5%); Florida leads
--     among the large states by value (11.3%).
-- ============================================================================
SELECT
  recipient_state,
  count(*)                                                          AS records,
  round(100.0 * count(*) / sum(count(*)) OVER (), 1)                AS pct_records,
  round(100.0 * sum(value_of_interest_usd)
        / sum(sum(value_of_interest_usd)) OVER (), 1)               AS pct_value
FROM public.cms_open_payments
WHERE record_type = 'ownership' AND recipient_state IS NOT NULL
GROUP BY recipient_state
ORDER BY records DESC
LIMIT 8;
--  TX 619 13.5% val 5.9% · FL 525 11.4% val 11.3% · CA 377 8.2% val 6.8%
--  IL 235 5.1% val 1.9% · NC 220 4.8% val 0.9% · MD 207 4.5% val 1.1%
--  NJ 207 4.5% val 1.1% · NY 203 4.4% val 4.5%