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

– 需求一：
–请写出一条SQL语句以查询每个用户的注册日期和在 2019*年作为买家的订单总数。
– 展示效果：

–	buyer_id	join_date	orders_in_2019
–	1	2018-01-01	1
–	2	2018-02-09	2
–	3	2018-01-19	0
–	4	2018-05-21	0

Create table If Not Exists 54_Users (user_id int, join_date DATETIME , favorite_brand STRING );
create table if not exists 54_Orders (order_id int, order_date DATETIME, item_id int, buyer_id int, seller_id int);
create table if not exists 54_Items (item_id int, item_brand STRING);
Truncate table 54_Users;
insert into 54_Users (user_id, join_date, favorite_brand) values (1, ‘2018-01-01 00:00:00’, ‘Lenovo’);
insert into 54_Users (user_id, join_date, favorite_brand) values (2, ‘2018-02-09 00:00:00’, ‘Samsung’);
insert into 54_Users (user_id, join_date, favorite_brand) values (3, ‘2018-01-19 00:00:00’, ‘LG’);
insert into 54_Users (user_id, join_date, favorite_brand) values (4, ‘2018-05-21 00:00:00’, ‘HP’);
Truncate table 54_Orders;
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (1, ‘2019-08-01 00:00:00’, 4, 1, 2);
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (2, ‘2018-08-02 00:00:00’, 2, 1, 3);
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (3, ‘2019-08-03 00:00:00’, 3, 2, 3);
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (4, ‘2018-08-04 00:00:00’, 1, 4, 2);
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (5, ‘2018-08-04 00:00:00’, 1, 3, 4);
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (6, ‘2019-08-05 00:00:00’, 2, 2, 4);
Truncate table 54_Items;
insert into 54_Items (item_id, item_brand) values (1, ‘Samsung’);
insert into 54_Items (item_id, item_brand) values (2, ‘Lenovo’);
insert into 54_Items (item_id, item_brand) values (3, ‘LG’);
SELECT * from 54_Users order by user_id LIMIT 10; SELECT * from 54_Orders order by order_id LIMIT 10;select* from 54_Items order by item_id LIMIT 10;

–sql
SELECT
t2.user_id user_id ,
t2.join_date,
t1.cnt
FROM
(
SELECT
buyer_id,
COUNT(*) cnt
from 54_Orders
where year(order_date)=2019
group by buyer_id

)t1
join 54_Users t2 WHERE
t1.buyer_id=t2.user_id;
– 需求二： 写一个 SQL 查询确定每一个用户按日期顺序卖出的第二件商品的品牌是否是他们最喜爱的品牌。如果一个用户卖出少于两件商品，查询的结果是 no 。
– 展示效果：

–	product_id	2nd_item_fav_brand
–	1	no
–	2	no
–	3	yes
–	4	no

– 方法一： 如果一个用户卖出少于两件商品，查询的结果是 no user_id为1 的呢？
SELECT
t.seller_id user_id,
if(t.favorite_brand=t.item_brand,‘yes’,‘no’) 2nd_item_fav_brand
from
(SELECT
o.item_id,
o.seller_id,
u.favorite_brand,
i.item_brand,
RANK()OVER (PARTITION BY seller_id order by order_date) rk
from
54_Orders o
left join 54_Users u
on o.seller_id=u.user_id
left join 54_Items i
on o.item_id=i.item_id)t
where t.rk=2;

– 方法二：
select
u.seller_id,
if(favorite_brand = item_brand, ‘yes’, ‘no’) as 2nd_item_fav_brand
from
(select user_id as seller_id from 54_Users) u
left join
(select
*
from
(select
o.order_date,
o.seller_id,

n by o.seller_id order by o.order_date) rnk
from
54_Orders o
left join
54_Users u
on
o.seller_id = u.user_id
left join
54_Items i
on
o.item_id = i.item_id
) t1
where rnk = 2
) t2
on
u.seller_id = t2.seller_id
order by t2.seller_id LIMIT 10;