Example of CROSS JOIN UNNEST in Quality Measure use case

[donna1up]

Cross join unnest and Lateral View Explode w Left joins have simplified querying the nested data for me.

`-------------------------------------------------------------------
 --Patient & Coverage Demographics --------------------------------
-------------------------------------------------------------------- 
create or replace view vw_dqs_patient_coverage as
    SELECT
        c.id,
        split_part(c.beneficiary.reference, '/', 2) AS patient,
        p.birthDate,
        --p.gender,
        t.cls.name AS coverage_plan,
        c.period."start" as startdate,
        c.period."end" as enddate
    FROM main.fhir_r4_coverage c
        CROSS JOIN UNNEST(c.class) t(cls)
    INNER JOIN main.fhir_r4_patient p
        ON p.id = split_part(c.beneficiary.reference, '/', 2) AND
        c.private_client_id = p.private_client_id
        ----these class values are an examples to identify different coverage plans - but the values may vary
        t.cls.name IN ('MedicarePlan', 'MedicaidPlan');
-------------------------------------------------------------------
 --continuous enrollment stage--------------------------------------
--------------------------------------------------------------------     
create or replace view vw_dqs_ce as       
SELECT d.patient
     --, d.coverage_plan
     , MIN(startdate) as startdate
     , MAX(enddate) as enddate
FROM (
  SELECT *, DATE_ADD('day', 0 - SUM(1 + date_diff('day', startdate, enddate))
                                           OVER(PARTITION BY patient ORDER BY startdate)
                                 , enddate) as Grouper
  FROM vw_dqs_patient_coverage
) AS d
GROUP BY d.patient, d.Grouper
ORDER BY d.patient, MIN(d.startdate);
--------------------------------------------------------------------
 --allowable gap stage patients with gaps >=45 days-----------------
--------------------------------------------------------------------  
create or replace view vw_dqs_gaps
with 
CTE1 AS (SELECT ROW_NUMBER() OVER (PARTITION BY patient ORDER BY startdate) as RN, * FROM vw_patient_coverage),
CTE2 AS (SELECT ROW_NUMBER() OVER (PARTITION BY patient ORDER BY startdate) as RN, * FROM vw_patient_coverage)
SELECT
CTE1.*,
CTE2.startdate AS nextenrollmentdate,
DATE_DIFF('day', CTE1.enddate, CTE2.startdate) AS diff
FROM CTE1
INNER JOIN CTE2 ON
CTE1.patient = CTE2.patient
AND CTE1.RN + 1 = CTE2.RN
WHERE
DATE_DIFF('day', CTE1.enddate, CTE2.startdate) >= 45; 
-----------------------------------------------------------------  
--Hospice or Palliative Care-------------------------------------
-----------------------------------------------------------------
--claims for --Hospice Encounter, Hospice Intervention, Palliative Care Encounter
create or replace view vw_dqs_hospice_palliative as
SELECT
        id as claim_id,
        private_client_id,
        split_part(patient.reference, '/', 2) AS patient,
        billablePeriod.start AS billablePeriod_start,
        billablePeriod."end" AS billablePeriod_end
    FROM main.fhir_r4_explanationofbenefit
       CROSS JOIN UNNEST(item) as t(item_x) 
       CROSS JOIN UNNEST(t.item_x.productOrService.coding) as t2(item_productOrService_coding_x)
       CROSS JOIN UNNEST(t.item_x.revenue.coding) as t2(item_revenue_coding_x)
    where t2.item_productOrService_coding_x.code in ('G9473' ,'G9474' ,'G9475' ,'G9476' ,'G9477' ,'G9478' ,'G9479' ,'Q5003' ,'Q5004' ,'Q5005' ,'Q5006' ,'Q5007' ,'Q5008' ,'Q5010' ,'S9126' ,'T2042' ,'T2043' ,'T2044' ,'T2045' ,'T2046' ,'99377' ,'99378' ,'G0182' ,'G9054' ,'M1017') 
    or t2.item_revenue_coding_x.code in ('115' ,'125' ,'135' ,'145' ,'155' ,'235' ,'650' ,'651' ,'652' ,'655' ,'656' ,'657' ,'658' ,'659')
group by 1,2,3,4,5;
-----------------------------------------------------------------  
--Initial Population BCSE   -------------------------------------
-----------------------------------------------------------------
-- Women 52–74 years of age by the end of the measurement period who also meet the criteria for participation.
--update to measurement period parameters -----------------------
create or replace view vw_dqs_bcs_initialpopulation as
select *
from vw_dqs_patient_coverage
where date_diff('year', birthDate, CURRENT_DATE) >= 52 
and date_diff('year', birthDate, CURRENT_DATE) <= 74
and gender = 'female';
-----------------------------------------------------------------  
-- Exclusions BCSE ----------------------------------------------
-----------------------------------------------------------------
--hospice & Palliative claims vw_dqs_bcs_exclusions
--claims--
--Bilateral Mastectomy Value Set or Unilateral Mastectomy Value Set AND Bilateral Modifier Value Set
create or replace view vw_dqs_pilot_bcse_exclusions as
SELECT
        id as claim_id,
        private_client_id,
        split_part(patient.reference, '/', 2) AS patient,
        billablePeriod.start AS billablePeriod_start,
        billablePeriod."end" AS billablePeriod_end
    FROM main.fhir_r4_explanationofbenefit
       CROSS JOIN UNNEST(procedure) as t(procedure_x) 
       CROSS JOIN UNNEST(item) as t1(item_x) 
       CROSS JOIN UNNEST(t.item_x.productOrService.coding) as t2(item_productOrService_coding_x)
       CROSS JOIN UNNEST(t.item_x.modifier.coding) as t3(item_modifier_coding_x)
    where t.procedure_x.code in ('0HTV0ZZ' , '85.42' , '85.44' , '85.46' , '85.48') 
    or (t2.item_productOrService_coding_x.code in ('19180' ,'19200' ,'19220' ,'19240' ,'19303' ,'19304' ,'19305' ,'19306' ,'19307') 
    and t3.item_modifier_coding_x.code in ('50'))
group by 1,2,3,4,5;
--`