1565. 按月统计订单数与顾客数 1571. 仓库经理 1596. 每位顾客最经常订购的商品 1607. 没有卖出的卖家

---

1565. 按月统计订单数与顾客数

---

# Write your MySQL query statement below
select substring(order_date,1,7)  month,
    count(order_id)order_count,
    count(distinct customer_id) customer_count
from Orders
where invoice>20
group by substring(order_date,1,7)

---

1571. 仓库经理

---

# Write your MySQL query statement below
select name WAREHOUSE_NAME,
        sum(Width*Length*Height*units) VOLUME
from Warehouse W left join Products P
on W.product_id = P.product_id
group by name

---

1596. 每位顾客最经常订购的商品

---

# Write your MySQL query statement below
select new_table.customer_id,new_table.product_id,product_name
from
(select customer_id ,product_id,
rank()over(partition by customer_id order by count(product_id) desc)paiming
from Orders
group by customer_id ,product_id
)new_table left join Products C
on new_table.product_id = C.product_id
where paiming = 1

---

1607. 没有卖出的卖家

---

# Write your MySQL query statement below
select seller_name
from Seller 
where seller_id not in(
select seller_id 
from Orders
where date_format(sale_date,'%Y') = '2020' 
group by seller_id
having count(*)>=1
)
order by seller_name