Leetcode SQL (#1454, #1459, #1468, #1479)

select distinct 
l.id, 
a.name
from(
    select 
    id, 
    grp, 
    min(login_date) as min_date, 
    max(login_date) as max_date
    from(
        select 
        id, 
        login_date, 
        r, 
        start_flag, 
        sum(start_flag) over(partition by id order by login_date) as grp
        from(
            select 
            id, 
            login_date, 
            r,
            case 
                when datediff(login_date, lag_login_date) > 1 or datediff(login_date, lag_login_date) is null then r 
                else 0 
            end as start_flag
            from(
                select 
                id, 
                login_date, 
                lag(login_date,1) over(partition by id order by login_date) as lag_login_date,
                row_number() over(partition by id order by login_date) as r
                from (
                    select distinct 
                    l.id, 
                    l.login_date
                    from(
                        select distinct 
                        id, 
                        login_date
                        from logins
                    ) l
                    left join (
                        select distinct 
                        id, 
                        login_date
                        from logins
                    ) l2 
                    on (l.id = l2.id and l.login_date <= date_add(l2.login_date, interval 1 day) )
                ) l
            ) l
        ) l
    ) l
    group by 1,2
    having datediff(max(login_date),min(login_date) ) >= 4
) l
left join accounts a 
    on l.id = a.id
order by l.id

select 
p1.id as p1, 
p2.id as p2, 
abs(p1.x_value - p2.x_value) * abs(p1.y_value - p2.y_value) as area
from Points p1
    cross join Points p2
where (
abs(p1.x_value - p2.x_value) * abs(p1.y_value - p2.y_value) > 0
and p1.id < p2.id
)
order by 3 desc, 1, 2

select 
s.company_id, 
s.employee_id, 
s.employee_name, 
round(s.salary - s.salary * s1.tax_rate, 0) as salary
from Salaries s
left join (
    select 
    s.company_id, 
    case 
        when s.max_salary < 1000 then 0 
        when s.max_salary >= 1000 and s.max_salary<=10000 then 0.24 
        when s.max_salary > 10000 then 0.49 
    end as tax_rate
    from (
        select 
        company_id, 
        max(salary) as max_salary 
        from Salaries 
        group by 1
    ) s
) s1 
    on s.company_id = s1.company_id

select 
item_category as category, 
sum(case when weekname = 'Monday' then quantity else 0 end) as Monday,
sum(case when weekname = 'Tuesday' then quantity else 0 end) as Tuesday,
sum(case when weekname = 'Wednesday' then quantity else 0 end) as Wednesday,
sum(case when weekname = 'Thursday' then quantity else 0 end) as Thursday,
sum(case when weekname = 'Friday' then quantity else 0 end) as Friday,
sum(case when weekname = 'Saturday' then quantity else 0 end) as Saturday,
sum(case when weekname = 'Sunday' then quantity else 0 end) as Sunday
from(
    select 
    i.item_category, 
    i.item_id, 
    o.order_date, 
    o.quantity,
    case 
        when weekday(o.order_date) = 0 then 'Monday'
        when weekday(o.order_date)=1 then 'Tuesday'
        when weekday(o.order_date) = 2 then 'Wednesday'
        when weekday(o.order_date) = 3 then 'Thursday'
        when weekday(o.order_date) = 4 then 'Friday'
        when weekday(o.order_date) = 5 then 'Saturday'
        when weekday(o.order_date) = 6 then 'Sunday'
    end as weekname
    from items i
        left join orders o 
            on i.item_id = o.item_id
) s
group by 1
order by 1