Translate Clinical Inquiries into Standard OMOP SQL

-- OMOP SQL Agent Synthesized Cohort Query (OMOP CDM v5.4)
WITH t2d_diagnosis AS (
    SELECT co.person_id, co.condition_start_date AS index_date
    FROM `{`project.id`.dataset_id.}`condition_occurrence co
    JOIN `{`project.id`.dataset_id.}`concept_ancestor ca ON co.condition_concept_id = ca.descendant_concept_id
    WHERE ca.ancestor_concept_id = 201826 -- Type 2 diabetes mellitus
),
metformin_initiation AS (
    SELECT de.person_id, de.drug_exposure_start_date
    FROM `{`project.id`.dataset_id.}`drug_exposure de
    JOIN `{`project.id`.dataset_id.}`concept_ancestor ca ON de.drug_concept_id = ca.descendant_concept_id
    WHERE ca.ancestor_concept_id = 1503297 -- Metformin
),
qualified_cohort AS (
    SELECT DISTINCT d.person_id, d.index_date
    FROM t2d_diagnosis d
    JOIN `{`project.id`.dataset_id.}`person p ON d.person_id = p.person_id
    JOIN `{`project.id`.dataset_id.}`observation_period op 
      ON d.person_id = op.person_id
     AND d.index_date BETWEEN op.observation_period_start_date AND op.observation_period_end_date
     AND DATE_DIFF(d.index_date, op.observation_period_start_date, DAY) >= 365 -- 365d continuous observation
    JOIN metformin_initiation m 
      ON d.person_id = m.person_id
     AND DATE_DIFF(m.drug_exposure_start_date, d.index_date, DAY) BETWEEN 0 AND 30
    WHERE (EXTRACT(YEAR FROM d.index_date) - p.year_of_birth) >= 18 -- Adults aged 18+
)
SELECT COUNT(DISTINCT person_id) AS total_cohort_patients
FROM qualified_cohort;

/* OMOP SQL Agent Synthesized Cohort Query (OMOP CDM v5.4) */
WITH t2d_diagnosis AS (
  SELECT
    co.person_id,
    co.condition_start_date AS index_date
  FROM {schema}.condition_occurrence AS co
  JOIN {schema}.concept_ancestor AS ca
    ON co.condition_concept_id = ca.descendant_concept_id
  WHERE
    ca.ancestor_concept_id = 201826 /* Type 2 diabetes mellitus */
), metformin_initiation AS (
  SELECT
    de.person_id,
    de.drug_exposure_start_date
  FROM {schema}.drug_exposure AS de
  JOIN {schema}.concept_ancestor AS ca
    ON de.drug_concept_id = ca.descendant_concept_id
  WHERE
    ca.ancestor_concept_id = 1503297 /* Metformin */
), qualified_cohort AS (
  SELECT DISTINCT
    d.person_id,
    d.index_date
  FROM t2d_diagnosis AS d
  JOIN {schema}.person AS p
    ON d.person_id = p.person_id
  JOIN {schema}.observation_period AS op
    ON d.person_id = op.person_id
    AND d.index_date BETWEEN op.observation_period_start_date AND op.observation_period_end_date
    AND (
      CAST(d.index_date AS DATE) - CAST(op.observation_period_start_date AS DATE)
    ) >= 365 /* 365d continuous observation */
  JOIN metformin_initiation AS m
    ON d.person_id = m.person_id
    AND (
      CAST(m.drug_exposure_start_date AS DATE) - CAST(d.index_date AS DATE)
    ) BETWEEN 0 AND 30
  WHERE
    (
      EXTRACT(YEAR FROM d.index_date) - p.year_of_birth
    ) >= 18 /* Adults aged 18+ */
)
SELECT
  COUNT(DISTINCT person_id) AS total_cohort_patients
FROM qualified_cohort;

/* OMOP SQL Agent Synthesized Cohort Query (OMOP CDM v5.4) */
WITH t2d_diagnosis AS (
  SELECT
    co.person_id,
    co.condition_start_date AS index_date
  FROM {database.schema}.condition_occurrence AS co
  JOIN {database.schema}.concept_ancestor AS ca
    ON co.condition_concept_id = ca.descendant_concept_id
  WHERE
    ca.ancestor_concept_id = 201826 /* Type 2 diabetes mellitus */
), metformin_initiation AS (
  SELECT
    de.person_id,
    de.drug_exposure_start_date
  FROM {database.schema}.drug_exposure AS de
  JOIN {database.schema}.concept_ancestor AS ca
    ON de.drug_concept_id = ca.descendant_concept_id
  WHERE
    ca.ancestor_concept_id = 1503297 /* Metformin */
), qualified_cohort AS (
  SELECT DISTINCT
    d.person_id,
    d.index_date
  FROM t2d_diagnosis AS d
  JOIN {database.schema}.person AS p
    ON d.person_id = p.person_id
  JOIN {database.schema}.observation_period AS op
    ON d.person_id = op.person_id
    AND d.index_date BETWEEN op.observation_period_start_date AND op.observation_period_end_date
    AND DATEDIFF(DAY, op.observation_period_start_date, d.index_date) >= 365 /* 365d continuous observation */
  JOIN metformin_initiation AS m
    ON d.person_id = m.person_id
    AND DATEDIFF(DAY, d.index_date, m.drug_exposure_start_date) BETWEEN 0 AND 30
  WHERE
    (
      DATE_PART(YEAR, d.index_date) - p.year_of_birth
    ) >= 18 /* Adults aged 18+ */
)
SELECT
  COUNT(DISTINCT person_id) AS total_cohort_patients
FROM qualified_cohort;