-- Where clinical trials stop: the Phase 2 valley, 2026
-- Fonteum reproducible query — https://fonteum.com/research/clinical-trial-discontinuation-2026
-- Data as of: 2026-06-14  ·  methodology: clinicaltrials/v1
-- Source datasets: ClinicalTrials.gov
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- How often clinical trials STOP before completing — and where the attrition
-- concentrates. Fully reproducible query.
--
-- Question: of the studies registered on ClinicalTrials.gov, how many end in
-- discontinuation (terminated, withdrawn, or suspended) rather than completion,
-- and how does that rate vary by trial phase and by who sponsors the trial?
-- The lead figure: of the 373,998 trials that have reached a settled outcome
-- (completed OR stopped), 51,959 — 13.9%, about 1 in 7 — ended in
-- discontinuation rather than completion. Discontinuation is a study-lifecycle
-- signal, NOT a quality, fraud, or wrongdoing signal of any kind, and says
-- nothing about any investigator, sponsor, or provider.
--
-- Source:
--   public.clinical_trials — ClinicalTrials.gov Data API v2 (the U.S. National
--     Library of Medicine registry of interventional and observational
--     studies). 589,453 registered studies; snapshot 2026-06-14. Public,
--     read-only. License: ClinicalTrials.gov Terms & Conditions (NLM public
--     data). methodology_version = 'clinicaltrials/v1'.
--
-- Universe: this study reads the published registry AS A WHOLE — every row is a
--   study CMS/NLM lists on ClinicalTrials.gov as of the 2026-06-14 snapshot.
--   "Settled" = a study that has reached a final lifecycle outcome (COMPLETED,
--   TERMINATED, WITHDRAWN, or SUSPENDED); ongoing statuses (RECRUITING,
--   ACTIVE_NOT_RECRUITING, etc.) and the legacy UNKNOWN status are excluded from
--   rate denominators because they have not yet settled.
--
-- Counting note: 589,453 distinct NCT IDs, one row each; no row is duplicated.
--   No individual investigator, sponsor, or provider is named in the study.

-- ============================================================================
-- (1) Universe reconciliation — the registry at a glance.
-- ============================================================================
SELECT
  count(*)                                                          AS studies,
  count(DISTINCT nct_id)                                            AS distinct_nct,
  count(DISTINCT lead_sponsor_name)                                 AS sponsors,
  count(*) FILTER (WHERE study_type = 'INTERVENTIONAL')             AS interventional,
  count(*) FILTER (WHERE study_type = 'OBSERVATIONAL')              AS observational,
  max(ingested_at)::date                                            AS snapshot
FROM public.clinical_trials;
--  studies 589,453 · distinct_nct 589,453 · sponsors 50,756
--  interventional 449,800 (76.3%) · observational 137,624 (23.3%) · snapshot 2026-06-14

-- ============================================================================
-- (2) HEADLINE: the discontinuation rate. Of all 589,453 registered studies,
--     51,959 (8.8%) are discontinued. Restricted to the 373,998 that have
--     SETTLED (reached a final outcome), the discontinuation rate is 13.9% —
--     about 1 in 7. Discontinuation = TERMINATED (stopped after starting),
--     WITHDRAWN (stopped before the first participant), or SUSPENDED (paused).
-- ============================================================================
SELECT
  count(*)                                                          AS all_studies,
  count(*) FILTER (WHERE overall_status IN
    ('TERMINATED','WITHDRAWN','SUSPENDED'))                         AS discontinued,
  round(100.0 * count(*) FILTER (WHERE overall_status IN
    ('TERMINATED','WITHDRAWN','SUSPENDED')) / count(*), 1)          AS disc_pct_of_all,
  count(*) FILTER (WHERE overall_status IN
    ('COMPLETED','TERMINATED','WITHDRAWN','SUSPENDED'))             AS settled,
  round(100.0 * count(*) FILTER (WHERE overall_status IN
    ('TERMINATED','WITHDRAWN','SUSPENDED')) / count(*) FILTER
    (WHERE overall_status IN
    ('COMPLETED','TERMINATED','WITHDRAWN','SUSPENDED')), 1)         AS disc_pct_of_settled,
  count(*) FILTER (WHERE overall_status = 'TERMINATED')             AS terminated,
  count(*) FILTER (WHERE overall_status = 'WITHDRAWN')              AS withdrawn,
  count(*) FILTER (WHERE overall_status = 'SUSPENDED')              AS suspended
FROM public.clinical_trials;
--  all_studies 589,453 · discontinued 51,959 · disc_pct_of_all 8.8%
--  settled 373,998 · disc_pct_of_settled 13.9%
--  terminated 33,776 · withdrawn 16,459 · suspended 1,724

-- ============================================================================
-- (3) WHERE attrition concentrates — discontinuation rate by trial phase,
--     among settled trials. Phase 2 is the high-attrition phase (23.0%); the
--     combined Phase 1/Phase 2 designs are worse (27.2%). Phase 3 (15.3%) and
--     Phase 1 (14.7%) are markedly lower. This is the classic efficacy-stage
--     "valley" — the point where a candidate must show it works.
-- ============================================================================
SELECT
  coalesce(phase, '(none)')                                         AS phase,
  count(*)                                                          AS settled,
  count(*) FILTER (WHERE overall_status IN
    ('TERMINATED','WITHDRAWN','SUSPENDED'))                         AS discontinued,
  round(100.0 * count(*) FILTER (WHERE overall_status IN
    ('TERMINATED','WITHDRAWN','SUSPENDED')) / count(*), 1)          AS disc_pct
FROM public.clinical_trials
WHERE overall_status IN ('COMPLETED','TERMINATED','WITHDRAWN','SUSPENDED')
GROUP BY phase
ORDER BY settled DESC;
--  NA            141,021 · 15,474 · 11.0%
--  (none)         77,779 ·  7,663 ·  9.9%
--  PHASE2         43,693 · 10,031 · 23.0%   <- the valley
--  PHASE1         37,713 ·  5,526 · 14.7%
--  PHASE3         30,539 ·  4,680 · 15.3%
--  PHASE4         24,966 ·  4,067 · 16.3%
--  PHASE1/PHASE2  10,141 ·  2,761 · 27.2%
--  PHASE2/PHASE3   4,762 ·    986 · 20.7%
--  EARLY_PHASE1    3,384 ·    771 · 22.8%

-- ============================================================================
-- (4) WHO stops — discontinuation rate by lead-sponsor class, among settled
--     trials. Headline: industry-sponsored trials discontinue slightly more
--     often than everyone else (15.3% vs 13.4%). But that is a phase-mix
--     artifact: industry runs disproportionately many phased drug trials, which
--     fail more. Hold phase constant (next query) and the order REVERSES.
-- ============================================================================
SELECT
  coalesce(lead_sponsor_class, '(none)')                            AS sponsor_class,
  count(*)                                                          AS settled,
  count(*) FILTER (WHERE overall_status IN
    ('TERMINATED','WITHDRAWN','SUSPENDED'))                         AS discontinued,
  round(100.0 * count(*) FILTER (WHERE overall_status IN
    ('TERMINATED','WITHDRAWN','SUSPENDED')) / count(*), 1)          AS disc_pct
FROM public.clinical_trials
WHERE overall_status IN ('COMPLETED','TERMINATED','WITHDRAWN','SUSPENDED')
GROUP BY sponsor_class
ORDER BY settled DESC;
--  OTHER     247,117 · 33,364 · 13.5%   INDUSTRY 101,598 · 15,559 · 15.3%
--  NIH         9,753 ·  1,327 · 13.6%   OTHER_GOV  7,849 ·    633 ·  8.1%
--  FED         3,776 ·    469 · 12.4%   NETWORK    3,456 ·    541 · 15.7%
--  industry 15.3% vs all-other 13.4% overall (the phase-mix headline)

-- ============================================================================
-- (4b) The reversal, holding phase constant: among PHASED interventional
--      trials (Early Phase 1 .. Phase 4), NON-industry sponsors discontinue
--      MORE than industry (21.0% vs 15.8%) — and the gap holds inside Phase 2
--      specifically (24.7% non-industry vs 20.6% industry). Better-resourced
--      industry monitoring, not a quality verdict on academic science.
-- ============================================================================
WITH p AS (
  SELECT
    CASE WHEN lead_sponsor_class = 'INDUSTRY' THEN 'industry'
         ELSE 'non-industry' END                                   AS grp,
    phase, overall_status
  FROM public.clinical_trials
  WHERE overall_status IN ('COMPLETED','TERMINATED','WITHDRAWN','SUSPENDED')
    AND phase IN ('EARLY_PHASE1','PHASE1','PHASE1/PHASE2','PHASE2',
                  'PHASE2/PHASE3','PHASE3','PHASE4')
)
SELECT
  grp,
  count(*)                                                          AS settled,
  count(*) FILTER (WHERE overall_status IN
    ('TERMINATED','WITHDRAWN','SUSPENDED'))                         AS discontinued,
  round(100.0 * count(*) FILTER (WHERE overall_status IN
    ('TERMINATED','WITHDRAWN','SUSPENDED')) / count(*), 1)          AS disc_pct,
  count(*) FILTER (WHERE phase = 'PHASE2')                          AS phase2_settled,
  round(100.0 * count(*) FILTER (WHERE phase = 'PHASE2'
    AND overall_status IN ('TERMINATED','WITHDRAWN','SUSPENDED'))
    / nullif(count(*) FILTER (WHERE phase = 'PHASE2'), 0), 1)       AS phase2_disc_pct
FROM p
GROUP BY grp
ORDER BY settled DESC;
--  non-industry  81,817 · 17,202 · 21.0% · phase2 25,509 · 24.7%
--  industry      73,381 · 11,620 · 15.8% · phase2 18,184 · 20.6%

-- ============================================================================
-- (5) TWO kinds of stopping — withdrawn vs terminated by enrollment profile.
--     A WITHDRAWN trial stops before enrolling its first participant; a
--     TERMINATED trial stops after enrolling some. The data confirm the
--     distinction: 98.8% of withdrawn trials enrolled zero participants
--     (median 0), against a median of 21 for terminated trials.
-- ============================================================================
SELECT
  overall_status,
  count(*)                                                          AS trials,
  count(*) FILTER (WHERE enrollment_count = 0)                      AS zero_enrolled,
  round(100.0 * count(*) FILTER (WHERE enrollment_count = 0)
    / count(*), 1)                                                  AS zero_enroll_pct,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY enrollment_count)     AS median_enrolled
FROM public.clinical_trials
WHERE overall_status IN ('WITHDRAWN','TERMINATED')
  AND enrollment_count IS NOT NULL
GROUP BY overall_status
ORDER BY trials DESC;
--  TERMINATED 33,529 · zero 29 · 0.1% · median_enrolled 21
--  WITHDRAWN  16,441 · zero 16,237 · 98.8% · median_enrolled 0
