Real-world evidence, at population scale.

-- adult T2DM, paired baseline + 6mo A1c
WITH cohort AS (
  SELECT person_id, condition_start_date AS dx
  FROM omop.condition_occurrence
  WHERE concept_id = 201826  -- T2DM
    AND person_age >= 18
), baseline AS (
  SELECT c.person_id, AVG(a1c) AS a1c_0
  FROM cohort c JOIN omop.measurement m
    ON m.person_id = c.person_id
   AND m.measurement_date BETWEEN c.dx - 30 AND c.dx + 30
   AND m.concept_id = 3004410  -- A1c
  GROUP BY c.person_id
), pv AS (
  -- CH extension: daily axis scores
  SELECT person_id, AVG(pv_score) AS pv_avg
  FROM ch.axis_daily
  WHERE axis = 'PV'
    AND date BETWEEN dx AND dx + 180
  GROUP BY person_id
)
SELECT pv_quartile, AVG(a1c_delta), k_anon(person_id)
FROM joined GROUP BY pv_quartile;