-- The 1% of doctors who get two-thirds of industry money
-- Fonteum reproducible query — https://fonteum.com/research/open-payments-recipient-concentration-2024
-- Data as of: 2026-01-23  ·  methodology: open-payments/v1
-- Source datasets: CMS Open Payments
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- Open Payments recipient CONCENTRATION — fully reproducible query.
--
-- Question: among the physicians who receive industry money, how concentrated
-- is it? We measure, over the recipient-NPI universe, what share of all
-- general-payment dollars flows to the top 0.1% / 1% / 5% / 10% / 50% of
-- recipients, the Gini coefficient across recipients, and the median-vs-mean
-- gap. This is the RECIPIENT-side companion to the manufacturer study
-- (payer-side: top 25 of 1,763 companies = 52%) — here the axis is how the
-- money lands on doctors, not which companies send it.
--
-- Source:
--   public.cms_open_payments — CMS Open Payments program-year-2024 release
--     (PGYR2024, published 2026-01-23). One row per reported transfer of value.
--     16,146,544 records total; public, read-only.
--     License: US-Government-Works (17 U.S.C. §105).
--
-- Scope: general (non-research) payments only (record_type = 'general'), the
--   $3.31B consulting / speaking / royalty / food / travel bucket. Research
--   ($8.49B) and ownership ($147.8M) are excluded.
--
-- Recipient universe: records carrying a recipient NPI (an individual-recipient
--   identity). 15,336,988 of 15,385,047 general records carry one; the
--   $675.8M remainder has no NPI and is ~98.5% teaching-hospital payments —
--   excluded because a concentration measure needs a recipient identity.
--   NPI-attributed general dollars = $2,638,034,613 across 979,136 recipients.
--
-- These aggregates run server-side (direct SQL) — a count-distinct + percentile
-- over the 15.4M-row general slice exceeds PostgREST's 8s statement timeout,
-- which is why the page renders point-in-time figures from this frozen
-- 2026-01-23 snapshot rather than reading the table at request time.

-- Per-recipient rollup reused by every query below.
WITH recip AS (
  SELECT recipient_npi,
         sum(total_amount_usd)::numeric AS amt,
         max(recipient_specialty)        AS spec
  FROM public.cms_open_payments
  WHERE record_type = 'general'
    AND program_year = 2024
    AND recipient_npi IS NOT NULL
  GROUP BY recipient_npi
)

-- ============================================================================
-- (1) Headline: cumulative share of NPI-attributed dollars by top band.
--     The top 1% share (66.1%) is the lead figure.
-- ============================================================================
SELECT b.label,
       ceil(max(n) * b.frac)                                           AS recipients_in_band,
       round(sum(amt) FILTER (WHERE rk <= ceil(n * b.frac)))           AS cum_dollars,
       round(sum(amt) FILTER (WHERE rk <= ceil(n * b.frac))
             / max(tot) * 100, 1)                                      AS cum_share_pct,
       round(min(amt) FILTER (WHERE rk <= ceil(n * b.frac)))           AS min_amount_in_band
FROM (
  SELECT amt,
         row_number() OVER (ORDER BY amt DESC) AS rk,
         count(*)     OVER ()                  AS n,
         sum(amt)     OVER ()                  AS tot
  FROM recip
) ranked
CROSS JOIN (VALUES ('Top 0.1%', 0.001), ('Top 1%', 0.01), ('Top 5%', 0.05),
                   ('Top 10%', 0.10), ('Top 25%', 0.25), ('Top 50%', 0.50),
                   ('All 100%', 1.0)) AS b(label, frac)
GROUP BY b.label, b.frac
ORDER BY b.frac;
--  Top 0.1%     980      894,344,652  33.9   277,347
--  Top 1%     9,792    1,742,445,935  66.1    42,735
--  Top 5%    48,957    2,247,880,674  85.2     3,932
--  Top 10%   97,914    2,374,842,739  90.0     1,837
--  Top 25%  244,784    2,528,256,130  95.8       583
--  Top 50%  489,568    2,606,597,404  98.8       164
--  All 100% 979,136    2,638,034,613 100.0         0
--  (bottom 50% therefore split 100.0 - 98.8 = 1.2% of the dollars.)

-- ============================================================================
-- (2) Distribution shape — median vs mean, thresholds, and the long tail.
-- ============================================================================
SELECT
  count(*)                                                        AS recipients,
  round(avg(amt))                                                 AS mean_recipient,
  round(percentile_cont(0.5)  WITHIN GROUP (ORDER BY amt))        AS median_recipient,
  round(percentile_cont(0.9)  WITHIN GROUP (ORDER BY amt))        AS p90_recipient,
  round(percentile_cont(0.99) WITHIN GROUP (ORDER BY amt))        AS p99_recipient,
  round(max(amt))                                                 AS max_recipient,
  count(*) FILTER (WHERE amt >= 1000000)                          AS recipients_ge_1m,
  count(*) FILTER (WHERE amt >= 100000)                           AS recipients_ge_100k,
  count(*) FILTER (WHERE amt <  100)                              AS recipients_lt_100
FROM recip;
--  recipients 979,136 · mean $2,694 · median $164 · p90 $1,837 · p99 $42,731
--  max_recipient $91,082,706 (one recipient = the single largest general
--    payment in the file, a dental-device acquisition transfer)
--  >=$1M 156 · >=$100k 4,027 · <$100 363,598

-- ============================================================================
-- (3) Gini coefficient across recipients + bottom-50% share.
--     Gini = (2 * sum(i * amt) / (n * sum(amt))) - (n + 1) / n,
--     with i = ascending rank. 0 = perfectly equal, 1 = one recipient takes all.
-- ============================================================================
WITH ord AS (
  SELECT amt, row_number() OVER (ORDER BY amt) AS i FROM recip
),
agg AS (
  SELECT count(*)::numeric AS n,
         sum(amt)          AS tot,
         sum(i * amt)      AS wsum,
         sum(amt) FILTER (WHERE i <= (SELECT count(*) FROM recip) * 0.5) AS bottom50
  FROM ord
)
SELECT round((2.0 * wsum / (n * tot) - (n + 1.0) / n)::numeric, 3) AS gini,
       round((bottom50 / tot * 100)::numeric, 2)                  AS bottom50_share_pct
FROM agg;
--  gini 0.927 · bottom50_share 1.19   (US household-income Gini ≈ 0.41)

-- ============================================================================
-- (4) Who sits in the top 1% — specialty mix of the 9,792 highest-paid
--     recipients (most-specific CMS taxonomy segment). No recipient named.
-- ============================================================================
WITH ranked AS (
  SELECT amt, spec,
         row_number() OVER (ORDER BY amt DESC) AS rk,
         count(*)     OVER ()                  AS n
  FROM recip
),
top1 AS (
  SELECT amt,
         trim(split_part(spec, '|', array_length(string_to_array(spec, '|'), 1))) AS leaf
  FROM ranked WHERE rk <= ceil(n * 0.01)
)
SELECT leaf AS specialty,
       count(*)                                              AS recipients_in_top1pct,
       round(sum(amt))                                       AS dollars,
       round(sum(amt) / (SELECT sum(amt) FROM top1) * 100, 1) AS pct_of_top1_dollars
FROM top1
GROUP BY leaf
ORDER BY dollars DESC
LIMIT 8;
--  Orthopaedic Surgery                        767  329,637,642  18.9
--  Endodontics                                 27  101,248,496   5.8
--  Neurology                                  433   66,929,859   3.8
--  Neurological Surgery                       287   66,169,260   3.8
--  Orthopaedic Surgery of the Spine           249   57,584,583   3.3
--  Psychiatry                                 321   56,761,647   3.3
--  Dermatology                                272   49,419,678   2.8
--  Adult Reconstructive Orthopaedic Surgery   140   47,240,658   2.7
--  (orthopedic family — surgery + spine + adult-reconstructive — = 1,156
--   recipients and 24.9% of all top-1% dollars.)

-- ============================================================================
-- (5) Universe reconciliation — where the non-NPI general dollars go.
-- ============================================================================
SELECT
  round(sum(total_amount_usd) FILTER (WHERE recipient_npi IS NOT NULL))                       AS npi_dollars,
  round(sum(total_amount_usd) FILTER (WHERE recipient_npi IS NULL))                           AS non_npi_dollars,
  round(sum(total_amount_usd) FILTER (WHERE recipient_npi IS NULL
        AND recipient_type ILIKE '%Teaching Hospital%'))                                      AS teaching_hospital_dollars,
  round(sum(total_amount_usd))                                                                AS general_total
FROM public.cms_open_payments
WHERE record_type = 'general' AND program_year = 2024;
--  npi_dollars 2,638,034,613 · non_npi 675,767,124 · teaching_hosp 665,901,119
--  general_total 3,313,801,737  (NPI-attributed = 79.6% of the all-recipient total)
