1731. 每位经理的下属员工数量 1747. 应该被禁止的 Leetflex 账户 1777. 每家商店的产品价格 1783. 大满贯数量 1789. 员工的直属部门

---

1789. 员工的直属部门

---

with new_table as(
    select *,
    #SQL架构中显示primary_flag ENUM('Y','N') 
    #意味着primary_flag的顺序是Y在前N在后
    #而非字母顺序。这是由于ENUM这种数据结构引起的
    rank()over(partition by employee_id order by primary_flag asc) diyibushi_Y
    from Employee
)

select  employee_id , department_id
from new_table
where primary_flag = 'Y' or diyibushi_Y = 1

---

1731. 每位经理的下属员工数量

---

# Write your MySQL query statement below
select new_table.reports_to employee_id,
        name, 
        reports_count,
        average_age
from(
select reports_to ,count(*)  reports_count,round(avg(age),0) average_age
from Employees 
where reports_to <> 'null' 
group by reports_to)new_table ,Employees E
where E.employee_id = new_table.reports_to
order by employee_id 

---

1747. 应该被禁止的 Leetflex 账户

---

# Write your MySQL query statement below
select distinct L2.account_id
from LogInfo L1,LogInfo L2
where L1.account_id = L2.account_id and
      L1.ip_address <> L2.ip_address and
      L1.logout >= L2.login and 
      L2.login >= L1.login

---

1777. 每家商店的产品价格

---

select product_id ,
        sum(if(store = 'store1',price,null)) 'store1' , 
        sum(if(store = 'store2',price,null)) 'store2' , 
        sum(if(store = 'store3',price,null)) 'store3' 
from Products
group by product_id

---

1783. 大满贯数量

---

# Write your MySQL query statement below
select new_table.player_id , player_name,count(new_table.player_id) grand_slams_count
from (
select  Wimbledon  player_id from Championships
union all
select  Fr_open from Championships
union all
select  US_open from Championships
union all
select  Au_open from Championships)new_table,Players P
where new_table.player_id = P.player_id
group by player_id