# $1159. 市场分析 II
# https://leetcode-cn.com/problems/market-analysis-ii/

# SQL架构
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');

# Write your MySQL query statement below
select
    u.user_id as seller_id,
    if(u.favorite_brand = i.item_brand, 'yes', 'no') as 2nd_item_fav_brand
from
    Users u
    left join(
        select
            -- o1.order_id,
            -- o1.order_date,
            o1.item_id,
            -- o1.buyer_id,
            o1.seller_id
        from
            Orders o1
            join Orders o2 on o1.seller_id = o2.seller_id
            and o1.order_date >= o2.order_date
        group by
            o1.order_date,
            # sql_mode=only_full_group_by
            o1.item_id,
            o1.seller_id
        having
            count(o2.order_date) = 2
    ) tmp on u.user_id = tmp.seller_id
    left join Items i on i.item_id = tmp.item_id;

# clean-up
drop table Users;
drop table Orders;
drop table Items;