Leetcode SQL (#1264, #1270, #1280, #1285)

select distinct 
l.page_id as recommended_page
from Likes l
/* user_id = 1's friend user_ids */
where l.user_id in (
    select distinct user_id
    from(
        select 
        user2_id as user_id
        from Friendship
        where user1_id = 1
        union
        select 
        user1_id as user_id
        from Friendship
        where user2_id = 1
    ) u
)
/* exclude pages that user_id = 1 like */
and l.page_id not in (
    select distinct 
    page_id
    from Likes
    where user_id = 1
)

select distinct
a.employee_id
from(
    select 
    e.employee_id, 
    e.employee_name, 
    e.manager_id, 
    m.manager_id as manager_id2, 
    m2.manager_id as manager_id3
    from Employees e
        left join (
            select distinct 
            employee_id, 
            manager_id 
            from Employees
        ) m 
            on e.manager_id = m.employee_id
        left join (
            select distinct 
            employee_id, 
            manager_id 
            from Employees
        ) m2 
            on m.manager_id = m2.employee_id
    /* exclude head of the company */
    where (m2.manager_id = 1 
           and e.employee_id <> 1)
) a
order by 1

select 
s.student_id, 
s.student_name, 
s.subject_name, 
ifnull(b.attended_exams,0) as attended_exams
from (
    select 
    s.student_id, 
    s.student_name, 
    sub.subject_name
    from Students s
        cross join Subjects sub 
)s
left join (
    select 
    student_id, 
    student_name, 
    subject_name, 
    sum(exam) as attended_exams
    from(
        select 
        e.student_id, 
        s.student_name, 
        e.subject_name, 
        1 as exam
        from Examinations e
            left join Students s 
                on e.student_id = s.student_id
    ) a
    group by 1,2,3
) b 
    on (s.student_id = b.student_id 
        and s.subject_name = b.subject_name)
order by 1,3

select 
l2.start_id, 
l2.end_id
from(
    select 
    grp, 
    min(log_id) as start_id, 
    max(log_id) as end_id
    from(
        select 
        log_id, 
        row_number() over(order by log_id) as r,
        /* for consecutive numbers, their row numbers will be the same as the log_id */
        log_id - (row_number() over(order by log_id)) as grp
        from Logs
        ) l
    group by 1
) l2
order by 1