Untitled

create table cdm_stage.lesson_presence
(
    subject_pk          varchar              not null,
    id                  bigint,
    room_hash           varchar(14)          not null,
    user_id             bigint               not null,
    last_presence_at    timestamp(0),
    created_at          timestamp(0)         not null,
    updated_at          timestamp(0)         not null,
    last_joined_at      timestamp(0),
    last_presence_at_ms bigint,
    last_joined_at_ms   bigint,
    source              varchar(32),
    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 (id);


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

        union all

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

        union all

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

        union all

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

        union all

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

        union all

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

        union all

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

        union all

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

        union all

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

        union all

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

        union all

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

        union all

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

        union all

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

        union all

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

        union all

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

        union all

        select
            'biology',
            *
        from    raw_skysmart_biology_900.lesson_presence  
        where   mt_actual_flag
    )
select
    subject_pk,
    id,
    room_hash,
    user_id,
    last_presence_at,
    created_at,
    updated_at,
    last_joined_at,
    last_presence_at_ms,
    last_joined_at_ms,
    source
from lesson_presence