Leetcode SQL (#1327, #1336, #1341, #1350)

select 
p.product_name, 
o.unit
from Products p
    join ( 
        select 
        product_id, 
        sum(unit) as unit
        from Orders 
        where year(order_date) = 2020 
        and month(order_date) = 2 
        group by 1
        having sum(unit) >=100
    ) o
        on p.product_id = o.product_id
group by 1,2

select 
tt.transactions_count, 
sum(tt.visits_count) as visits_count
from (
    /* calculate visits based on existing transactions */
    select b.transactions as transactions_count, count(user_id) as visits_count
    from (
        select 
        a.user_id, 
        visit_date, 
        transactions
        from (
            select 
            v.user_id, 
            v.visit_date, 
            s.transaction_date, 
            ifnull(s.transactions,0) as transactions
            from Visits v
                left join (
                    select 
                    user_id, 
                    transaction_date, 
                    count(user_id) as transactions 
                    from Transactions 
                    group by 1,2 
                ) s 
                    on (v.user_id = s.user_id and v.visit_date = s.transaction_date )
        ) a
        order by 3,2
    ) b
    group by 1
    union
    /* create rows < max_transaction, including rows where there are transactions with 0 visits */
    select 
    txn_row_num as transactions_count, 
    0 as visits_count
    from (
        select 
        row_number() over (order by transaction_date) -1 as txn_row_num
        from Transactions 
    ) t
    join (
        select 
        /* find the max_transaction with > 0 visits */
        max(transactions) as max_txn 
        from(
            select 
            v.user_id, 
            v.visit_date, 
            s.transaction_date, 
            ifnull(s.transactions,0) as transactions
            from Visits v
                left join (
                    select 
                    user_id, 
                    transaction_date, 
                    count(user_id) as transactions 
                    from Transactions 
                    group by 1,2
                ) s 
                    on (v.user_id = s.user_id and v.visit_date = s.transaction_date )
        ) a
    ) m 
        on t.txn_row_num <= m.max_txn     
) tt
group by 1
order by 1

select 
results
from (
    /* users who have rated the greatest number of movies */
    select 
    u2.name as results
    from (
        select 
        u.user_id, 
        u.name, 
        u.movies, 
        rank() over(order by movies desc, name) as r
        from (
            select 
            r.user_id, 
            u.name, 
            count(distinct movie_id) as movies
            from Movie_Rating r
                left join Users u 
                    on r.user_id = u.user_id
            group by 1,2
        ) u
    ) u2
    where u2.r = 1
    union
    /* movies with highest average rating in 2020-02 */
    select m2.title as results
    from(
        select 
        m.movie_id, 
        m.title, 
        m.avg_rating, 
        rank() over(order by avg_rating desc, title) as r
        from(
            select 
            r.movie_id, 
            m.title, 
            avg(r.rating) as avg_rating
            from Movie_Rating r
                left join Movies m 
                    on r.movie_id = m.movie_id
            where substr(r.created_at,1,7) = '2020-02'
            group by 1,2
        ) m
    ) m2
    where m2.r = 1
) a

select 
id, 
name
from Students 
where department_id not in (
    select distinct 
    id 
    from Departments
)