Untitled

create table cdm_stage.lesson_presence_log
(
    subject_pk         varchar              not null,
    id                 bigint               not null,
    lesson_presence_id bigint               not null,
    type               varchar(32)          not null,
    sent_at            timestamp(0)         not null,
    sent_at_ms         bigint               not null,
    created_at         timestamp(0)         not null,
    updated_at         timestamp(0)         not null,
    mt_proc_id         bigint               not null,
    mt_ins_dttm        timestamp            not null,
    mt_hash            bigint               not null,
    mt_actual_flag     boolean default true not null
)
    with (appendonly = true, orientation = column, compresstype = zstd, compresslevel = 5)
    distributed by (lesson_presence_id);



with lesson_presence_log as (
        select
            'math' as subject_pk,
            *
        from    raw_math_580.lesson_presence_log
        where   mt_actual_flag

            union all

        select
            'russian',
            *
        from    raw_skysmart_russian_760.lesson_presence_log
        where   mt_actual_flag

            union all

        select
            'physics',
            *
        from    raw_skysmart_physics_770.lesson_presence_log
        where   mt_actual_flag

            union all

        select
            'chess',
            *
        from    raw_skysmart_chess_780.lesson_presence_log
        where   mt_actual_flag

            union all

        select
            'social_science',
            *
        from    raw_skysmart_social_science_790.lesson_presence_log
        where   mt_actual_flag

            union all

        select
            'preschool',
            *
        from    raw_skysmart_preschool_800.lesson_presence_log
        where   mt_actual_flag

            union all

        select
            'homeschool',
            *
        from    raw_skysmart_homeschool_810.lesson_presence_log
        where   mt_actual_flag

            union all

        select
            'english',
            *
        from    raw_skysmart_english_820.lesson_presence_log
        where   mt_actual_flag

            union all

        select
            'mnemonics',
            *
        from    raw_skysmart_mnemonics_830.lesson_presence_log
        where   mt_actual_flag

            union all

        select
            'chemistry',
            *
        from    raw_skysmart_chemistry_840.lesson_presence_log
        where   mt_actual_flag

            union all

        select
            'career_guidance',
            *
        from    raw_skysmart_career_guidance_850.lesson_presence_log
        where   mt_actual_flag

            union all

        select
            'literature',
            *
        from    raw_skysmart_literature_860.lesson_presence_log
        where   mt_actual_flag

        union all

        select
            'computer_science',
            *
        from    raw_skysmart_computer_science_870.lesson_presence_log
        where   mt_actual_flag

        union all

        select
            'history',
            *
        from    raw_skysmart_history_880.lesson_presence_log
        where   mt_actual_flag

        union all

        select
            'geography',
            *
        from    raw_skysmart_geography_890.lesson_presence_log
        where   mt_actual_flag

        union all

        select
            'biology',
            *
        from    raw_skysmart_biology_900.lesson_presence_log
        where   mt_actual_flag
    )
select
    subject_pk,
    id,
    lesson_presence_id,
    type,
    sent_at,
    sent_at_ms,
    created_at,
    updated_at
from lesson_presence_log