12、查询所有商品(sku_info表)截至到2021年10月01号的最新商品价格(需要结合价格修改表进行分析)
期望结果如下:
sku_id | price |
1 | 2000.00 |
2 | 10.00 |
3 | 5000.00 |
4 | 6000.00 |
5 | 500.00 |
6 | 2000.00 |
7 | 100.00 |
8 | 600.00 |
9 | 1000.00 |
10 | 90.00 |
11 | 66.00 |
12 | 20.00 |
需要用到的表:
商品信息表:sku_info
sku_id(商品id) | name(商品名称) | category_id(分类id) | from_date(上架日期) | price(商品价格) |
1 | xiaomi 10 | 1 | 2020-01-01 | 2000 |
6 | 洗碗机 | 2 | 2020-02-01 | 2000 |
9 | 自行车 | 3 | 2020-01-01 | 1000 |
商品价格变更明细表:sku_price_modify_detail
sku_id(商品id) | new_price(本次变更之后的价格) | change_date(变更日期) |
1 | 1900.00 | 2021-09-25 |
1 | 2000.00 | 2021-09-26 |
2 | 80.00 | 2021-09-29 |
2 | 10.00 | 2021-09-30 |
代码
select a.sku_id,b.new_price as price
from (select * from sku_info where from_date <='2021-10-01') a
left join (select * from (select sku_id,new_price,change_date,rank() over(partition by sku_id order by change_date desc) as rk from sku_price_modify_detail where change_date <= '2021-10-01')c where rk=1 )b on a.sku_id=b.sku_id
13、订单配送中,如果期望配送日期和下单日期相同,称为即时订单,如果期望配送日期和下单日期不同,称为计划订单。 请从配送信息表(delivery_info)中求出每个用户的首单(用户的第一个订单)中即时订单的比例,保留两位小数,以小数形式显示。
期望结果如下:
percentage |
0.50 |
需要用到的表:
配送信息表:delivery_info
delivery_id (运单id) | order_id (订单id) | user_id (用户 id ) | order_date (下单日期) | custom_date (期望配送日期) |
1 | 1 | 101 | 2021-09-27 | 2021-09-29 |
2 | 2 | 101 | 2021-09-28 | 2021-09-28 |
3 | 3 | 101 | 2021-09-29 | 2021-09-30 |
代码
select cast (avg(if(delivery_cat='即时订单',1,0)) as decimal(10,2)) as percentage
from (select
user_id
,order_date
,custom_date
,if(order_date=custom_date ,'即时订单','计划订单') as delivery_cat
,delivery_id
,rank() over(partition by user_id order by order_date,delivery_id ) as rk from delivery_info
)a
where rk=1
14、 现需要请向所有用户推荐其朋友收藏但是用户自己未收藏的商品,请从好友关系表(friendship_info)和收藏表(favor_info)中查询出应向哪位用户推荐哪些商品。
期望结果如下:
user_id | sku_id |
101 | 2 |
101 | 4 |
101 | 7 |
101 | 9 |
101 | 8 |
101 | 11 |
101 | 1 |
需要用到的表:
好友关系表:friendship_info
user1_id(用户1 id) | user2_id(用户2 id) |
101 | 1010 |
101 | 108 |
101 | 106 |
收藏表:favor_info
user_id(用户id) | sku_id(商品id) | create_date(收藏日期) |
101 | 3 | 2021-09-23 |
101 | 12 | 2021-09-23 |
101 | 6 | 2021-09-25 |
代码
--列出用户,所以是user1_id
select distinct A.user1_id as user_id ,A.sku_id
from
(
--用户->朋友->收藏 , 列出所有有收藏的朋友,因为有些朋友没有收藏,所以用inner
select A.user1_id,A.user2_id,B.sku_id
from
friendship_info A
inner join
favor_info B
on A.user2_id=B.user_id
)A
-- 用户->收藏 ,用left join 以及 null 筛选 这些朋友有收藏但是用户本身没有收藏的用户
left join
favor_info B on A.user1_id=B.user_id and A.sku_id=B.sku_id
where B.sku_id is null
order by A.user1_id,A.sku_id
15、从登录明细表(user_login_detail)中查询出,所有用户的连续登录两天及以上的日期区间,以登录时间(login_ts)为准。
期望结果如下:
user_id | start_date | end_date |
101 | 2021-09-27 | 2021-09-30 |
102 | 2021-10-01 | 2021-10-02 |
106 | 2021-10-04 | 2021-10-05 |
107 | 2021-10-05 | 2021-10-06 |
需要用到的表:
登录明细表:user_login_detail
user_id(用户id) | ip_address(ip地址) | login_ts(登录时间) | logout_ts(登出时间) |
101 | 180.149.130.161 | 2021-09-21 08:00:00 | 2021-09-27 08:30:00 |
102 | 120.245.11.2 | 2021-09-22 09:00:00 | 2021-09-27 09:30:00 |
103 | 27.184.97.3 | 2021-09-23 10:00:00 | 2021-09-27 10:30:00 |
代码
select * from (
select
user_id,min(login_ts) as start_date,max(login_ts) as end_date
from
(
select
-- 用户 开始日期 结束日期 下一个登录日期
user_id,
substr(login_ts, 1, 10) as login_ts,
substr(logout_ts, 1, 10) as logout_ts,
lead(substr(login_ts, 1, 10)) over(partition by user_id order by login_ts ) next_login_ts
from
user_login_detail
) a
where datediff(next_login_ts,login_ts)=1 or next_login_ts is null
group by user_id
order by user_id
)b where datediff(end_date,start_date)>=1