create or replace schema test_case;

use test_case;

create table registration
(
    student_id          bigint,
    program_code        varchar(2),
    primary key(student_id, program_code)
);

create table attendance
(
    student_id          bigint,
    program_code        varchar(2),
    arrival_date        decimal(8,0),
    arrival_time        decimal(6,0),
    primary key(student_id, program_code, arrival_date, arrival_time)
);

insert
into registration
(
    student_id,
    program_code
)
values
(12345678,  '10'),
(12345678,  '11');

insert
into attendance
(
    student_id,
    program_code,
    arrival_date,
    arrival_time
)
values
(12345678,  '11',   19840101, 101010);

select
    r1.student_id,
    r1.program_code,
    exists
    (
        select *
        from
            registration r2
        where
            r2.student_id   = r1.student_id
        and r2.program_code <> r1.program_code
        and ifnull
            (
                (
                    select
                        max(timestamp(cast(a.arrival_date as date), cast(a.arrival_time as time)))
                    from
                        attendance a
                    where
                        a.student_id    = r2.student_id
                    and a.program_code  = r2.program_code
                ),
                '1900-01-01'
            )
            >
            ifnull
            (
                (
                    select
                        max(timestamp(cast(a.arrival_date as date), cast(a.arrival_time as time)))
                    from
                        attendance a
                    where
                        a.student_id    = r1.student_id
                    and a.program_code  = r1.program_code
                ),
                '1900-01-01'
            )
    ) as incorrect_behaviour,
    exists
    (
        select *
        from
            registration r2
        where
            r2.student_id   = r1.student_id
        and r2.program_code <> r1.program_code
        and ifnull
            (
                (
                    select
                        /* random comment */
                        max(timestamp(cast(a.arrival_date as date), cast(a.arrival_time as time)))
                    from
                        attendance a
                    where
                        a.student_id    = r2.student_id
                    and a.program_code  = r2.program_code
                ),
                '1900-01-01'
            )
            >
            ifnull
            (
                (
                    select
                        max(timestamp(cast(a.arrival_date as date), cast(a.arrival_time as time)))
                    from
                        attendance a
                    where
                        a.student_id    = r1.student_id
                    and a.program_code  = r1.program_code
                ),
                '1900-01-01'
            )
    ) as correct_behaviour
from
    registration r1;