Day: August 29, 2019

select 
employee
from(
    select 
        e.Name as employee, 
        e.salary, 
        e.managerid, 
        ifnull(m.salary,0) as manager_salary
    from employee e
        left join employee m 
            on e.managerid = m.id
    where e.salary > m.salary
) e

select 
distinct e.email
from(
    select 
    email, 
    count(email) over(partition by lower(email) ) as cnt
    from person
) e
where e.cnt > 1

select 
name as customers
from customers 
where Id not in (
    select 
    customerid 
    from orders 
    group by 1
) 

select 
a.department, 
a.employee, 
a.salary
from(
    select 
    d.name as department, 
    e.name as employee, 
    ifnull(e.salary,0) as salary,
    max(ifnull(e.salary,0)) over(partition by d.name order by ifnull(e.salary,0) desc) as max_salary
    from department d
        join employee e 
            on e.departmentid = d.id
    order by d.id
) a
where a.salary = a.max_salary

select 
d.name as department, 
e.employee, 
e.salary
from(
    select 
    departmentid, 
    name as employee, 
    salary,
    dense_rank() over(partition by departmentid order by salary desc) as r
    from employee
) e
join department d 
    on e.departmentid = d.id
where e.r <= 3