Advanced Query Search

SELECT sdc.patient_id, sdc.study_drug, sdc.claim_date AS fill_date, sdc.days_supply, LEAD(sdc.claim_date) OVER (PARTITION BY sdc.patient_id, sdc.study_drug ORDER BY sdc.claim_date) AS next_fill_date FROM study_drug_pharm_claims AS sdc JOIN t2d_cvd_clean AS base ON sdc.patient_id = base.patient_id WHERE sdc.claim_date >= base.index_date AND sdc.claim_date <= '2025-12-31'

SELECT c.patient_id, MIN(c.admission_date) AS first_hf_admit FROM inpatient_claims AS c JOIN t2d_cvd_clean AS base ON c.patient_id = base.patient_id WHERE c.dx_code LIKE 'I50%' AND c.admission_date >= base.index_date AND c.admission_date <= '2025-12-31' GROUP BY c.patient_id

SELECT mc.patient_id, SUM(mc.allowed_amount) AS total_medical_cost FROM medical_claims AS mc JOIN t2d_cvd_clean AS base ON mc.patient_id = base.patient_id WHERE mc.service_date >= base.index_date AND mc.service_date <= '2025-12-31' GROUP BY mc.patient_id

SELECT pc.patient_id, SUM(pc.cost) AS total_pharmacy_cost FROM pharmacy_claims AS pc JOIN t2d_cvd_clean AS base ON pc.patient_id = base.patient_id WHERE pc.claim_date >= base.index_date AND pc.claim_date <= '2025-12-31' GROUP BY pc.patient_id

SELECT c.patient_id, MIN(d.claim_date) AS first_fill_date, d.study_drug AS index_drug FROM t2d_cvd_clean AS c JOIN study_drug_pharm_claims AS d ON c.patient_id = d.patient_id WHERE d.claim_date = c.index_date GROUP BY c.patient_id, d.study_drug

SELECT bl.patient_id, bl.index_drug, bl.first_fill_date, tcc.dob, tcc.coverage_type, tcc.index_date FROM baseline_regimen AS bl JOIN t2d_cvd_clean AS tcc ON bl.patient_id = tcc.patient_id

SELECT mp.*, ROW_NUMBER() OVER (PARTITION BY tirz_pt ORDER BY ps_diff) AS rank_within_tirz FROM matched_pairs AS mp

SELECT patient_id, service_date FROM inpatient_claims WHERE service_date < (SELECT MIN(index_date) FROM t2d_cvd_clean WHERE patient_id = inpatient_claims.patient_id)

SELECT patient_id, SUM(gap_days) AS total_gaps FROM (SELECT mp.patient_id, CASE WHEN DATEDIFF(next_fill_date, fill_date) - days_supply > 45 THEN 1 ELSE 0 END AS gap_days FROM medication_persistence AS mp) AS sub GROUP BY patient_id HAVING SUM(gap_days) > 0

SELECT mp.patient_id, CASE WHEN DATEDIFF(next_fill_date, fill_date) - days_supply > 45 THEN 1 ELSE 0 END AS gap_days FROM medication_persistence AS mp

SELECT patient_id, total_cost_post_index FROM total_costs WHERE total_cost_post_index > 1000000

SELECT person_id, MIN(index_date) AS index_date, MIN(therapy_class) AS therapy_class FROM TEMP_COHORT_BASE GROUP BY person_id

SELECT c.person_id, v.visit_start_date, v.visit_concept_id, CASE WHEN v.visit_concept_id = 9201 THEN 'INPATIENT' WHEN v.visit_concept_id = 9203 THEN 'ED' ELSE 'OTHER' END AS visit_type FROM VISIT_OCCURRENCE AS v JOIN TEMP_COHORT_DEDUP AS c ON v.person_id = c.person_id WHERE v.visit_start_date >= c.index_date AND v.visit_start_date <= DATEADD(DAY, 365, c.index_date)

SELECT c.person_id, SUM(cost.total_charge) AS total_12mo_charge FROM TEMP_COHORT_DEDUP AS c JOIN COST AS cost ON c.person_id = cost.person_id WHERE cost.cost_event_date >= c.index_date AND cost.cost_event_date <= DATEADD(DAY, 365, c.index_date) GROUP BY c.person_id

SELECT c.person_id, c.index_date, c.therapy_class AS index_therapy_class, CASE WHEN NOT d.discontinuation_date IS NULL AND d.discontinuation_date <= DATEADD(DAY, 365, c.index_date) THEN DATEDIFF(DAY, c.index_date) ELSE NULL END AS time_to_discontinuation, CASE WHEN NOT s.switch_date IS NULL THEN DATEDIFF(DAY, c.index_date) ELSE NULL END AS time_to_switch, s.switched_to, (SELECT COUNT(*) FROM TEMP_VISITS AS v WHERE v.person_id = c.person_id AND v.visit_type = 'INPATIENT') AS hosp_count, (SELECT COUNT(*) FROM TEMP_VISITS AS v WHERE v.person_id = c.person_id AND v.visit_type = 'ED') AS ed_count, CASE WHEN NOT i.infection_date IS NULL THEN 1 ELSE 0 END AS infection_flag, COALESCE(t.total_12mo_charge, 0) AS total_12mo_charge FROM TEMP_COHORT_DEDUP AS c LEFT JOIN TEMP_DISCONTINUATION AS d ON c.person_id = d.person_id LEFT JOIN TEMP_SWITCHES AS s ON c.person_id = s.person_id LEFT JOIN TEMP_INFECTIONS AS i ON c.person_id = i.person_id LEFT JOIN TEMP_TOTAL_COSTS AS t ON c.person_id = t.person_id

SELECT n.patient_id, MIN(tx.fill_date) AS index_date, (CASE WHEN MIN(tx.therapy_class) = 'KRAS_G12C' THEN 'KRAS_G12C' WHEN MIN(tx.therapy_class) = 'EGFR_INHIB' THEN 'EGFR_INHIB' WHEN MIN(tx.therapy_class) = 'OTHER_TARGETED' THEN 'OTHER_TARGETED' ELSE 'UNKNOWN' END) AS index_therapy_class FROM TEMP_NSCLC_BASE AS n JOIN TEMP_TARGETED_RX AS tx ON n.patient_id = tx.patient_id GROUP BY n.patient_id

SELECT rx.patient_id, rx.fill_date, rx.ndc, rx.days_supply, LEAD(rx.fill_date) OVER (PARTITION BY rx.patient_id ORDER BY rx.fill_date) AS next_fill_date FROM pharmacy_claims AS rx JOIN TEMP_COHORT_CLEAN AS c ON rx.patient_id = c.patient_id WHERE rx.fill_date >= c.index_date AND rx.fill_date <= DATEADD(DAY, 365, c.index_date)

SELECT index_therapy_class, COUNT(*) AS n_patients, AVG(age_at_index) AS avg_age, STDDEV(age_at_index) AS std_age, 100.0 * SUM(CASE WHEN sex = 'F' THEN 1 ELSE 0 END) / COUNT(*) AS pct_female, 100.0 * SUM(CASE WHEN sex = 'M' THEN 1 ELSE 0 END) / COUNT(*) AS pct_male, 100.0 * SUM(CASE WHEN plan_type = 'COMMERCIAL' THEN 1 ELSE 0 END) / COUNT(*) AS pct_commercial, 100.0 * SUM(CASE WHEN plan_type = 'MEDICARE' THEN 1 ELSE 0 END) / COUNT(*) AS pct_medicare FROM FINAL_NSCLC_STUDY GROUP BY index_therapy_class

SELECT CONCAT(YEAR(index_date), '-Q', CASE WHEN MONTH(index_date) BETWEEN 1 AND 3 THEN 1 WHEN MONTH(index_date) BETWEEN 4 AND 6 THEN 2 WHEN MONTH(index_date) BETWEEN 7 AND 9 THEN 3 ELSE 4 END) AS index_quarter, index_therapy_class, COUNT(*) AS initiators FROM FINAL_NSCLC_STUDY GROUP BY CONCAT(YEAR(index_date), '-Q', CASE WHEN MONTH(index_date) BETWEEN 1 AND 3 THEN 1 WHEN MONTH(index_date) BETWEEN 4 AND 6 THEN 2 WHEN MONTH(index_date) BETWEEN 7 AND 9 THEN 3 ELSE 4 END), index_therapy_class ORDER BY 1, 2

SELECT index_therapy_class, COUNT(*) AS total_patients, COUNT(ttd_days) AS discontinue_events, AVG(ttd_days) AS avg_ttd_days FROM FINAL_NSCLC_STUDY GROUP BY index_therapy_class

SELECT index_therapy_class, COUNT(*) AS total_patients, COUNT(ttnt_days) AS switch_events, AVG(ttnt_days) AS avg_ttnt_days FROM FINAL_NSCLC_STUDY GROUP BY index_therapy_class

SELECT index_therapy_class, COUNT(*) AS n_patients, AVG(total_cost_6m) AS avg_cost_6m, MEDIAN(total_cost_6m) AS median_cost_6m FROM FINAL_NSCLC_STUDY GROUP BY index_therapy_class

SELECT index_therapy_class, AVG(ed_visits) AS avg_ed_visits, AVG(inpatient_stays) AS avg_inpatient_stays, AVG(outpatient_visits) AS avg_op_visits FROM FINAL_NSCLC_STUDY GROUP BY index_therapy_class

SELECT index_therapy_class, 100.0 * SUM(pneumo_flag) / COUNT(*) AS pct_pneumonitis, 100.0 * SUM(liver_flag) / COUNT(*) AS pct_hepatotoxicity, 100.0 * SUM(other_ae_flag) / COUNT(*) AS pct_otherAE FROM FINAL_NSCLC_STUDY GROUP BY index_therapy_class

SELECT CASE WHEN age_at_index < 65 THEN 'Under65' ELSE '65plus' END AS age_group, index_therapy_class, AVG(total_cost_6m) AS avg_6m_cost, COUNT(*) AS n FROM FINAL_NSCLC_STUDY GROUP BY CASE WHEN age_at_index < 65 THEN 'Under65' ELSE '65plus' END, index_therapy_class

SELECT hb.patient_id, hb.hemo_type, hb.first_hemo_date, op.obs_start_date, op.obs_end_date FROM TEMP_HEMO_BASE AS hb JOIN OBSERVATION_PERIOD AS op ON hb.patient_id = op.patient_id WHERE DATEDIFF(DAY, op.obs_start_date) >= 365

SELECT it.patient_id, it.hemo_type, MIN(it.therapy_start_date) AS index_date, CASE WHEN MIN(it.therapy_class) LIKE '%HemA%' THEN 'HemA_novel' WHEN MIN(it.therapy_class) LIKE '%HemB%' THEN 'HemB_novel' ELSE 'UNKNOWN' END AS index_therapy_class FROM TEMP_INDEX_THERAPY AS it GROUP BY it.patient_id, it.hemo_type HAVING index_therapy_class <> 'UNKNOWN'

SELECT c.patient_id, c.hemo_type, c.index_date, c.index_therapy_class, p.date_of_birth, o.obs_start_date, o.obs_end_date FROM TEMP_INDEX_COHORT AS c JOIN OBSERVATION_PERIOD AS o ON c.patient_id = o.patient_id JOIN PATIENT AS p ON c.patient_id = p.patient_id WHERE o.obs_start_date <= DATEADD(DAY, -365, c.index_date) AND o.obs_end_date >= DATEADD(DAY, 180, c.index_date)

SELECT c.patient_id, MIN(l.result_date) AS first_inhibitor_date FROM TEMP_COHORT_CLEAN AS c JOIN LAB_RESULT AS l ON c.patient_id = l.patient_id WHERE l.lab_code = 'INHIBITOR_TITER' AND l.result_value >= 0.6 AND l.result_date > c.index_date AND l.result_date <= DATEADD(YEAR, 2, c.index_date) GROUP BY c.patient_id

SELECT index_therapy_class, hemo_type, COUNT(*) AS n_patients, AVG(age_at_index) AS mean_age, STDDEV(age_at_index) AS stddev_age, AVG(mean_factor_level) AS avg_factor_level FROM FINAL_HEMO_STUDY GROUP BY index_therapy_class, hemo_type

SELECT index_therapy_class, hemo_type, COUNT(*) AS n_patients, AVG(abr_annualized) AS avg_abr, MEDIAN(abr_annualized) AS med_abr FROM FINAL_HEMO_STUDY GROUP BY index_therapy_class, hemo_type

SELECT index_therapy_class, hemo_type, SUM(inhibitor_flag_2y) AS total_inhibitors, 100.0 * SUM(inhibitor_flag_2y) / COUNT(*) AS inhibitor_rate_pct FROM FINAL_HEMO_STUDY GROUP BY index_therapy_class, hemo_type

SELECT index_therapy_class, hemo_type, AVG(inpatient_visits_6m) AS mean_inpatient_6m, AVG(er_visits_6m) AS mean_er_6m, AVG(outpatient_visits_6m) AS mean_outpatient_6m, AVG(total_cost_6m) AS avg_cost_6m FROM FINAL_HEMO_STUDY GROUP BY index_therapy_class, hemo_type

SELECT r.patient_id, r.first_rsv_date, e.plan_start_date, e.plan_end_date FROM TEMP_RSV_DIAGNOSIS AS r JOIN ENROLLMENT AS e ON r.patient_id = e.patient_id WHERE e.plan_start_date <= DATEADD(MONTH, -12, r.first_rsv_date) AND e.plan_end_date >= DATEADD(MONTH, 1, r.first_rsv_date)

SELECT e.patient_id, e.first_rsv_date AS index_date FROM TEMP_RSV_ELIGIBLE AS e GROUP BY e.patient_id, e.first_rsv_date

SELECT index_visit_pos, COUNT(*) AS count_pos, 100.0 * COUNT(*) / (SELECT COUNT(*) FROM FINAL_RSV_STUDY) AS pct_pos FROM FINAL_RSV_STUDY GROUP BY index_visit_pos

SELECT CASE WHEN age_at_index >= 60 THEN '>=60' ELSE '<60' END AS age_group, COUNT(*) AS n, AVG(cost_30d) AS avg_cost_30d FROM FINAL_RSV_STUDY GROUP BY CASE WHEN age_at_index >= 60 THEN '>=60' ELSE '<60' END

SELECT d.patient_id, MIN(d.service_date) AS first_diab_dx_date, CASE WHEN MIN(CASE WHEN d.dx_code LIKE 'E10%' THEN 1 END) = 1 THEN 'T1D' WHEN MIN(CASE WHEN d.dx_code LIKE 'E11%' THEN 1 END) = 1 THEN 'T2D' ELSE 'OTHER' END AS diab_type FROM DIAGNOSIS AS d WHERE d.dx_code LIKE 'E1%' GROUP BY d.patient_id HAVING diab_type IN ('T1D', 'T2D')

SELECT b.patient_id, b.diab_type, b.first_diab_dx_date, e.coverage_start, e.coverage_end FROM TEMP_DIAB_BASE AS b JOIN ENROLLMENT AS e ON b.patient_id = e.patient_id WHERE e.coverage_start <= b.first_diab_dx_date AND e.coverage_end >= b.first_diab_dx_date

SELECT n.patient_id, p.fill_date, CASE WHEN p.ndc_code IN ('60429-111-01', '51655-550-25', '70010-063-01', '50742-634-10', '62584-259-01') THEN 'METFORMIN' WHEN p.ndc_code IN ('0310-6205-30', '0310-6210-30', '50458-140-30', '50458-141-30', '0597-0152-30') THEN 'SGLT2' WHEN p.ndc_code IN ('0169-4136', '0002-1433-01', '0169-4060-12', '0310-6512-01', '0169-2800-15') THEN 'GLP1' WHEN p.ndc_code IN ('0002-7715-01', '0002-8501-01', '00169-1837-11', '0002-8798-01', '0002-7510-01') THEN 'INSULIN' ELSE 'OTHER' END AS therapy_class, ROW_NUMBER() OVER (PARTITION BY n.patient_id ORDER BY p.fill_date) AS tx_sequence FROM TEMP_T2D_NEW_DIAG AS n JOIN PHARMACY_CLAIMS AS p ON n.patient_id = p.patient_id WHERE p.fill_date >= n.index_date AND p.fill_date <= DATEADD(MONTH, 12, n.index_date)

SELECT l1.patient_id, l1.fill_date AS l1_start, l2.fill_date AS l2_start, DATEDIFF(DAY, l1.fill_date) AS days_to_2L FROM TEMP_T2D_LINES AS l1 JOIN TEMP_T2D_LINES AS l2 ON l1.patient_id = l2.patient_id WHERE l1.line_of_therapy = '1L' AND l2.line_of_therapy = '2L' AND l2.fill_date > l1.fill_date

SELECT prx.patient_id, prx.provider_id, prx.fill_date, CASE WHEN p.ndc_code IN ('60429-111-01', '51655-550-25', '70010-063-01', '50742-634-10', '62584-259-01') THEN 'METFORMIN' WHEN p.ndc_code IN ('0310-6205-30', '0310-6210-30', '50458-140-30', '50458-141-30', '0597-0152-30') THEN 'SGLT2' WHEN p.ndc_code IN ('0169-4136', '0002-1433-01', '0169-4060-12', '0310-6512-01', '0169-2800-15') THEN 'GLP1' WHEN p.ndc_code IN ('0002-7715-01', '0002-8501-01', '00169-1837-11', '0002-8798-01', '0002-7510-01') THEN 'INSULIN' ELSE 'OTHER' END AS therapy_class FROM TEMP_T2D_PROVIDER_RX AS prx JOIN TEMP_DIAB_ENR AS enr ON prx.patient_id = enr.patient_id WHERE enr.diab_type = 'T2D'

SELECT r.provider_id, r.therapy_class, COUNT(*) AS script_count FROM TEMP_T2D_RX_LINK AS r GROUP BY r.provider_id, r.therapy_class

SELECT pt.*, NTILE(10) OVER ( ORDER BY pt.t2d_script_count DESC) AS decile FROM TEMP_PROVIDER_TARGETING AS pt

SELECT d.patient_id, MIN(d.service_date) AS first_ad_dx_date FROM DIAGNOSIS AS d WHERE d.dx_code LIKE 'L20%' GROUP BY d.patient_id

SELECT b.patient_id, b.first_ad_dx_date, e.coverage_start, e.coverage_end FROM TEMP_AD_BASE AS b JOIN ENROLLMENT AS e ON b.patient_id = e.patient_id WHERE e.coverage_start <= b.first_ad_dx_date AND e.coverage_end >= b.first_ad_dx_date

SELECT l1.patient_id, l1.fill_date AS first_line_start, l2.fill_date AS second_line_start, DATEDIFF(DAY, l1.fill_date) AS days_to_2L FROM TEMP_AD_LINES AS l1 JOIN TEMP_AD_LINES AS l2 ON l1.patient_id = l2.patient_id WHERE l1.line_of_therapy = '1L_ADV' AND l2.line_of_therapy = '2L_ADV' AND l2.fill_date > l1.fill_date

SELECT p.*, NTILE(10) OVER ( ORDER BY p.adv_ad_script_count DESC) AS decile FROM TEMP_PROVIDER_TARGETING_AD AS p

SELECT pr.provider_id, CASE WHEN p.ndc_code IN ('0024-5914-01', '0024-5914-02', '0024-5914-03') THEN 'DUPILUMAB' WHEN p.ndc_code IN ('50222-346-01', '50222-346-02', '50222-346-04') THEN 'TRALOKINUMAB' WHEN p.ndc_code IN ('0069-0235-30', '0069-0335-30', '0069-0435-30') THEN 'ABROCITINIB' WHEN p.ndc_code IN ('0074-2306-30', '0074-2307-30', '0074-2308-30') THEN 'UPADACITINIB' WHEN p.ndc_code IN ('0003-2337-11', '0003-2338-11', '0003-2341-11') THEN 'BARICITINIB' ELSE 'Other_Adv' END AS adv_therapy_class, COUNT(*) AS script_count FROM TEMP_AD_PROVIDER_RX AS pr GROUP BY pr.provider_id, CASE WHEN p.ndc_code IN ('0024-5914-01', '0024-5914-02', '0024-5914-03') THEN 'DUPILUMAB' WHEN p.ndc_code IN ('50222-346-01', '50222-346-02', '50222-346-04') THEN 'TRALOKINUMAB' WHEN p.ndc_code IN ('0069-0235-30', '0069-0335-30', '0069-0435-30') THEN 'ABROCITINIB' WHEN p.ndc_code IN ('0074-2306-30', '0074-2307-30', '0074-2308-30') THEN 'UPADACITINIB' WHEN p.ndc_code IN ('0003-2337-11', '0003-2338-11', '0003-2341-11') THEN 'BARICITINIB' ELSE 'Other_Adv' END

SELECT d.patient_id, MIN(d.service_date) AS first_lc_dx_date FROM DIAGNOSIS AS d WHERE d.dx_code LIKE 'C34%' GROUP BY d.patient_id

SELECT b.patient_id, b.first_lc_dx_date, e.coverage_start, e.coverage_end FROM TEMP_NSCLC_BASE AS b JOIN ENROLLMENT AS e ON b.patient_id = e.patient_id WHERE e.coverage_start <= b.first_lc_dx_date AND e.coverage_end >= b.first_lc_dx_date

SELECT e.patient_id, e.first_lc_dx_date, MIN(d2.service_date) AS first_metastatic_date FROM TEMP_NSCLC_ENROLL AS e JOIN DIAGNOSIS AS d2 ON e.patient_id = d2.patient_id WHERE d2.dx_code LIKE 'C77%' OR d2.dx_code LIKE 'C78%' OR d2.dx_code LIKE 'C79%' GROUP BY e.patient_id, e.first_lc_dx_date

SELECT e.patient_id, e.first_lc_dx_date, MIN(d2.service_date) AS first_metastatic_date FROM (SELECT b.patient_id, b.first_lc_dx_date, en.coverage_start, en.coverage_end FROM (SELECT d.patient_id, MIN(d.service_date) AS first_lc_dx_date FROM DIAGNOSIS AS d WHERE d.dx_code LIKE 'C34%' GROUP BY d.patient_id) AS b JOIN ENROLLMENT AS en ON b.patient_id = en.patient_id WHERE en.coverage_start <= b.first_lc_dx_date AND en.coverage_end >= b.first_lc_dx_date) AS e JOIN DIAGNOSIS AS d2 ON e.patient_id = d2.patient_id WHERE d2.dx_code LIKE 'C7[78|9].%' GROUP BY e.patient_id, e.first_lc_dx_date

SELECT b.patient_id, b.first_lc_dx_date, en.coverage_start, en.coverage_end FROM (SELECT d.patient_id, MIN(d.service_date) AS first_lc_dx_date FROM DIAGNOSIS AS d WHERE d.dx_code LIKE 'C34%' GROUP BY d.patient_id) AS b JOIN ENROLLMENT AS en ON b.patient_id = en.patient_id WHERE en.coverage_start <= b.first_lc_dx_date AND en.coverage_end >= b.first_lc_dx_date

SELECT adv.patient_id, pc.fill_date AS tx_date, pc.ndc_code, CASE WHEN p.ndc_code IN ('50242-200-01', '50242-200-02', '50242-200-03') THEN 'SOTORASIB' WHEN p.ndc_code IN ('72493-100-01', '72493-100-02', '72493-100-03') THEN 'ADAGRASIB' WHEN p.ndc_code IN ('0003-7000-11', '0003-7000-12', '0003-7000-13') THEN 'MOBOCERTINIB' WHEN p.ndc_code IN ('57894-0501-01', '57894-0501-02', '57894-0501-03') THEN 'AMIVANTAMAB' WHEN pc.ndc_code IN ('0006-3026-02', '0006-3026-03', '0006-3927-01', '0003-3774-12', '0003-3774-13', '0003-3774-14', '0310-4500-20', '0310-4500-30', '0310-4611-20', '50242-917-01', '50242-917-02', '50242-917-03', '71858-0006-01', '71858-0006-02', '71858-0006-03') THEN 'IO_THERAPY' ELSE 'OTHER_RX' END AS regimen_class FROM TEMP_ADV_NSCLC AS adv JOIN PHARMACY_CLAIMS AS pc ON adv.patient_id = pc.patient_id WHERE pc.fill_date >= adv.first_lc_dx_date AND pc.fill_date <= DATEADD(YEAR, 3, adv.first_lc_dx_date)

SELECT adv.patient_id, ch.service_date AS tx_date, CASE WHEN ch.proc_code IN ('J9271', 'J9999') THEN 'PEMBROLIZUMAB' WHEN ch.proc_code IN ('J9299') THEN 'ATEZOLIZUMAB' WHEN ch.proc_code IN ('J9998') THEN 'DURVALUMAB' WHEN ch.proc_code LIKE 'CHEMO_CODE%' THEN 'CHEMO_IV' ELSE 'OTHER_INFUSION' END AS regimen_class FROM TEMP_ADV_NSCLC AS adv JOIN CLAIM_HEADER AS ch ON adv.patient_id = ch.patient_id WHERE ch.service_date >= adv.first_lc_dx_date AND ch.service_date <= DATEADD(YEAR, 3, adv.first_lc_dx_date)

SELECT s.patient_id, s.tx_date, s.regimen_class, s.tx_sequence, LAG(s.tx_date) OVER (PARTITION BY s.patient_id ORDER BY s.tx_sequence) AS prev_tx_date, LAG(s.regimen_class) OVER (PARTITION BY s.patient_id ORDER BY s.tx_sequence) AS prev_regimen FROM TEMP_NSCLC_SORTED_TX AS s

SELECT l.*, SUM(new_line_flag) OVER (PARTITION BY l.patient_id ORDER BY l.tx_date, l.regimen_class ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS line_num FROM TEMP_NSCLC_LOT AS l

SELECT f.patient_id, f.line_num, MIN(f.tx_date) AS line_start_date, MAX(f.tx_date) AS line_end_date FROM TEMP_NSCLC_LOT_FINAL AS f GROUP BY f.patient_id, f.line_num

SELECT lf.patient_id, lf.regimen_class FROM TEMP_NSCLC_LOT_FINAL AS lf JOIN TEMP_NSCLC_LINE_DURATION AS ld ON lf.patient_id = ld.patient_id AND lf.line_num = ld.line_num WHERE ld.line_num = 1

SELECT t.*, NTILE(10) OVER ( ORDER BY t.total_advanced_tx_claims DESC) AS decile FROM TEMP_NSCLC_HCP_TARGETING AS t

SELECT tx.provider_id, tx.regimen_class, COUNT(*) AS tx_count FROM TEMP_NSCLC_PROVIDER_TX AS tx GROUP BY tx.provider_id, tx.regimen_class

SELECT b.patient_id, b.first_hemo_date, b.hemo_type, e.coverage_start, e.coverage_end FROM TEMP_HEMO_BASE AS b JOIN ENROLLMENT AS e ON b.patient_id = e.patient_id WHERE e.coverage_start <= b.first_hemo_date AND e.coverage_end >= DATEADD(MONTH, 6, b.first_hemo_date)

SELECT COUNT(*) AS other_factor_claims FROM TEMP_HEMO_THERAPIES WHERE therapy_class = 'OTHER_FACTOR'

SELECT s.*, LAG(s.fill_date) OVER (PARTITION BY s.patient_id ORDER BY s.tx_sequence) AS prev_fill_date, LAG(s.therapy_class) OVER (PARTITION BY s.patient_id ORDER BY s.tx_sequence) AS prev_class FROM TEMP_SORTED_PROPHY AS s

SELECT lf.*, SUM(new_line_flag) OVER (PARTITION BY lf.patient_id ORDER BY lf.fill_date, lf.therapy_class ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS line_num FROM TEMP_LOT_FLAGS AS lf

SELECT d.patient_id, d.line_num, MIN(d.fill_date) AS line_start, MAX(d.fill_date) AS line_end FROM TEMP_LINE_DEFINITIONS AS d GROUP BY d.patient_id, d.line_num

SELECT l.patient_id, l.line_num, l.prophylaxis_type, COUNT(*) AS claim_count FROM TEMP_LINE_DEFINITIONS AS l GROUP BY l.patient_id, l.line_num, l.prophylaxis_type

SELECT c4.*, ROW_NUMBER() OVER (PARTITION BY c4.patient_id, c4.line_num ORDER BY c4.claim_count DESC) AS rn FROM cte4 AS c4

SELECT line_therapy, AVG(time_on_line) AS avg_days, MEDIAN(time_on_line) AS median_days FROM TEMP_FINAL_LINE_DETAIL GROUP BY line_therapy ORDER BY avg_days DESC

SELECT t.*, CASE WHEN t.specialty_code IN ('HEM', 'HEM_ONC', 'PEDS_HEM') THEN 1 ELSE 0 END AS likely_hem_specialist FROM TEMP_PROVIDER_HEMO_TARGETING AS t WHERE t.hemophilia_pt_count > 0 OR t.likely_hem_specialist = 1 ORDER BY t.therapy_claim_count DESC

SELECT hl.*, NTILE(10) OVER ( ORDER BY hl.therapy_claim_count DESC) AS decile_claim FROM TEMP_HCP_RELEVANT_LIST AS hl

SELECT h.*, RANK() OVER ( ORDER BY h.total_hemo_tx_claims DESC) AS volume_rank FROM TEMP_HCO_SUMMARY AS h

SELECT b.patient_id, b.first_rsv_date, e.coverage_start, e.coverage_end FROM TEMP_RSV_BASE AS b JOIN ENROLLMENT AS e ON b.patient_id = e.patient_id WHERE e.coverage_start <= b.first_rsv_date AND e.coverage_end >= b.first_rsv_date

SELECT s.*, LAG(s.care_setting) OVER (PARTITION BY s.patient_id ORDER BY s.claim_seq) AS prev_setting FROM TEMP_RSV_SORTED_CLAIMS AS s

SELECT sc.patient_id, sc.service_date AS outpt_date FROM TEMP_RSV_SORTED_CLAIMS AS sc WHERE sc.care_setting = 'OUTPATIENT'

SELECT rp.*, NTILE(10) OVER ( ORDER BY (rp.rsv_claim_count + rp.proph_claim_count) DESC) AS decile FROM TEMP_RELEVANT_RSV_PROVIDERS AS rp

SELECT h.*, RANK() OVER ( ORDER BY (h.total_rsv_claims + h.total_proph_claims) DESC) AS usage_rank FROM TEMP_HCO_SUMMARY AS h