#1159.市场分析 ||

这题要求卖家用户卖出的第二单,是不是他们最喜欢的品牌,Users表记录的是他们最喜欢的品牌,而Items表是记录品牌id,让Users表与Items表相连接就可以得出,他们最喜欢的品牌id,即

select distinct user_id,join_date,favorite_brand,item_id

from users u join items i on u.favorite_brand=i.item_brand

因为是要得出他们第二单是不是他们最喜欢的品牌,所以要对订单表的他们继续分组按照销售日期排序

    select *,

row_number() over(partition by seller_id order by order_date)  r1

from orders

让这俩个结果按照用户连接并且筛选处第二单,且让item_id=item_id的数据输出yes,否则就输出no,即可得出第二单是否是最喜欢品牌的结果

select distinct user_id as seller_id,if(a.item_id=b.item_id,'yes','no')       as 2nd_item_fav_brand

from a left join b

on a.user_id = b.seller_id and r1 = 2  order by user_id

但是连接后会参数很多数据冗余让结果产生了多种可能结果,需要让同时存在yes和no的结果只输出yes,所以要对结果排序并且只输出排序为1的结果就可得出最终结果即

with a as

(select distinct user_id,join_date,favorite_brand,item_id

from users u join items i on u.favorite_brand=i.item_brand),

b as (

    select *,

row_number() over(partition by seller_id order by order_date)  r1

from orders

),

c as (select distinct user_id as seller_id,if(a.item_id=b.item_id,'yes','no')       as 2nd_item_fav_brand

from a left join b

on a.user_id = b.seller_id and r1 = 2  order by user_id)

select seller_id,2nd_item_fav_brand from (select *

,row_number() over(partition by seller_id order by 2nd_item_fav_brand desc) r1

 from c) d where  r1 =1 ;