snowflake071.sql

with enrollments_select as (
    select
        year,
        unitid,
        unitid_name,
        enrollments unitid_enrollments_total
    from
        CORELMI_US_V2.DAT_ENROLLMENTS_SAMPLE
    where
        raceid = '0'
        and genderid = '0'
        and enrlevelid = '1'
),
completions_select as (
    select
        year,
        unitid,
        unitid_name,
        programid,
        programid_name,
        sum(completions) programid_completions_sum
    from
        CORELMI_US_V2.DAT_COMPLETIONS_DEMOGRAPHICS_SAMPLE
    where
        awlevelid = '0'
        and raceid = '0'
        and genderid = '0'
    group by
        year,
        unitid,
        unitid_name,
        programid,
        programid_name
)
select
    enrollments_select.year,
    enrollments_select.unitid,
    enrollments_select.unitid_name,
    unitid_enrollments_total,
    programid,
    programid_name,
    programid_completions_sum,
    (
        programid_completions_sum / unitid_enrollments_total
    ) * 100 as programid_completions_percentage_of_enrollments
from
    enrollments_select
    join completions_select on enrollments_select.year = completions_select.year
    and enrollments_select.unitid = completions_select.unitid
order by
    year desc,
    unitid,
    unitid_name,
    unitid_enrollments_total,
    programid,
    programid_name,
    programid_completions_sum
limit 100;