Leetcode SQL (#1412, #1421, #1435, #1440, #1445)

select 
s.student_id, 
s.student_name
from Student s
/* exclude student_ids with the highest or lowest scores */
where s.student_id not in (
    select distinct 
    a.student_id
    from (
        select 
        e1.exam_id, 
        e1.student_id, 
        e1.score, 
        e2.low_score, 
        e2.high_score
        from Exam e1
            left join(
                select 
                exam_id, 
                min(score) as low_score, 
                max(score) as high_score
                from Exam
                group by 1
            ) e2 
                on e1.exam_id = e2.exam_id
    where (e1.score = low_score 
           or e1.score = high_score )
    ) a
)
/* student_ids in all exams */
and s.student_id in (
    select distinct 
    student_id 
    from Exam 
    where exam_id is not null )
order by s.student_id

select 
q.id, 
q.year, 
ifnull(n.npv,0) as npv
from Queries q
    left join NPV n 
        on (q.id = n.id and q.year = n.year )

select 
b.bin, 
ifnull(s.total,0) as total
from (
    select '[0-5>' as bin
    union
    select '[5-10>' as bin
    union
    select '[10-15>' as bin
    union
    select '15 or more' as bin
) b
left join(
    select 
    bin, 
    count(distinct session_id) as total
    from(
        select 
        session_id, 
        duration,
        case 
            when duration/60 >= 0 and duration/60 < 5 then '[0-5>'
            when duration/60 >= 5 and duration/60 < 10 then '[5-10>'
            when duration/60 >= 10 and duration/60 < 15 then '[10-15>'
            when duration/60 >= 15 then '15 or more' 
            else null 
        end as bin
        from sessions
    ) s
    group by 1
) s
    on b.bin = s.bin

select 
a.left_operand, 
a.operator, 
a.right_operand, 
case 
    when a.operator = a.result then 'true' 
    else 'false' 
end as value
from(
    select 
    e.left_operand, 
    x.value as left_value, 
    e.operator, 
    e.right_operand, 
    y.value as right_value,
    case 
        when x.value = y.value then '='
        when x.value > y.value then '>'
        when x.value < y.value then '<'
    end as result
    from Expressions e
        left join Variables x 
            on e.left_operand = x.name
        left join Variables y 
            on e.right_operand = y.name
) a

select 
a.sale_date, 
(a.sold_num - o.sold_num) as diff
from (
    select 
    sale_date, 
    fruit, 
    sold_num
    from Sales
    where fruit = 'apples'
) a
left join (
    select 
    sale_date, 
    fruit, 
    sold_num
    from Sales
    where fruit = 'oranges'
) o 
    on a.sale_date = o.sale_date
order by 1