1159.(Hard)市场分析 II

表: Users

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| join_date      | date    |
| favorite_brand | varchar |
+----------------+---------+
user_id 是该表的主键
表中包含一位在线购物网站用户的个人信息，用户可以在该网站出售和购买商品。

表: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| item_id       | int     |
| buyer_id      | int     |
| seller_id     | int     |
+---------------+---------+
order_id 是该表的主键
item_id 是 Items 表的外键
buyer_id 和 seller_id 是 Users 表的外键

表: Items

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| item_id       | int     |
| item_brand    | varchar |
+---------------+---------+
item_id 是该表的主键

写一个 SQL 查询确定每一个用户按日期顺序卖出的第二件商品的品牌是否是他们最喜爱的品牌。如果一个用户卖出少于两件商品，查询的结果是 no 。

题目保证没有一个用户在一天中卖出超过一件商品

下面是查询结果格式的例子：

Users table:
+---------+------------+----------------+
| user_id | join_date  | favorite_brand |
+---------+------------+----------------+
| 1       | 2019-01-01 | Lenovo         |
| 2       | 2019-02-09 | Samsung        |
| 3       | 2019-01-19 | LG             |
| 4       | 2019-05-21 | HP             |
+---------+------------+----------------+

Orders table:
+----------+------------+---------+----------+-----------+
| order_id | order_date | item_id | buyer_id | seller_id |
+----------+------------+---------+----------+-----------+
| 1        | 2019-08-01 | 4       | 1        | 2         |
| 2        | 2019-08-02 | 2       | 1        | 3         |
| 3        | 2019-08-03 | 3       | 2        | 3         |
| 4        | 2019-08-04 | 1       | 4        | 2         |
| 5        | 2019-08-04 | 1       | 3        | 4         |
| 6        | 2019-08-05 | 2       | 2        | 4         |
+----------+------------+---------+----------+-----------+

Items table:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1       | Samsung    |
| 2       | Lenovo     |
| 3       | LG         |
| 4       | HP         |
+---------+------------+

Result table:
+-----------+--------------------+
| seller_id | 2nd_item_fav_brand |
+-----------+--------------------+
| 1         | no                 |
| 2         | yes                |
| 3         | yes                |
| 4         | no                 |
+-----------+--------------------+

id 为 1 的用户的查询结果是 no，因为他什么也没有卖出
id为 2 和 3 的用户的查询结果是 yes，因为他们卖出的第二件商品的品牌是他们自己最喜爱的品牌
id为 4 的用户的查询结果是 no，因为他卖出的第二件商品的品牌不是他最喜爱的品牌

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/market-analysis-ii

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

Solution

SELECT user_id seller_id,
    (case 
    when count(1)=1 then 'no'
    when MAX(IF(rk=2 and favorite_brand=item_brand,1,0))=1 then 'yes'
    else 'no' end) 2nd_item_fav_brand
from (
    SELECT *, RANK() over (PARTITION BY user_id ORDER BY order_date) rk
    FROM Users u 
        LEFT JOIN Orders o on u.user_id=o.seller_id 
        LEFT JOIN Items USING(item_id)) t
GROUP BY user_id;

之前的solution(好复杂)

1. 先用sub query找出每个卖家按日排序卖出的第二件商品是什么 （表o)

2. 和另外两张表join

3. 表o中卖出的第二件商品 (o.item_2nd) 有非空值即为 yes

踩坑： 部分测试用例中，用户最喜欢的品牌favorite_brand可对应多个item_id，所以最后要对user_id做一次聚合，而不是直接对o.item_2nd 做ifnull判断。

select 
    u.user_id seller_id,
    if(count(o.item_2nd)=0,'no','yes') 2nd_item_fav_brand
from Users u left join 
     Items i on u.favorite_brand = i.item_brand left join
        (select o1.seller_id, 
            o1.item_id item_2nd
        from Orders o1
        where 1 = (select count(distinct o2.order_id)
                from Orders o2
                where o1.order_date > o2.order_date
                    and o1.seller_id = o2.seller_id)) o 
    on u.user_id = o.seller_id and o.item_2nd = i.item_id
group by u.user_id

Schema

Create table If Not Exists Users (user_id int, join_date date, favorite_brand varchar(10));
create table if not exists Orders (order_id int, order_date date, item_id int, buyer_id int, seller_id int);
create table if not exists Items (item_id int, item_brand varchar(10));
Truncate table Users;
insert into Users (user_id, join_date, favorite_brand) values ('1', '2019-01-01', 'Lenovo');
insert into Users (user_id, join_date, favorite_brand) values ('2', '2019-02-09', 'Samsung');
insert into Users (user_id, join_date, favorite_brand) values ('3', '2019-01-19', 'LG');
insert into Users (user_id, join_date, favorite_brand) values ('4', '2019-05-21', 'HP');
Truncate table Orders;
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('1', '2019-08-01', '4', '1', '2');
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('2', '2019-08-02', '2', '1', '3');
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('3', '2019-08-03', '3', '2', '3');
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('4', '2019-08-04', '1', '4', '2');
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('5', '2019-08-04', '1', '3', '4');
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('6', '2019-08-05', '2', '2', '4');
Truncate table Items;
insert into Items (item_id, item_brand) values ('1', 'Samsung');
insert into Items (item_id, item_brand) values ('2', 'Lenovo');
insert into Items (item_id, item_brand) values ('3', 'LG');
insert into Items (item_id, item_brand) values ('4', 'HP');