©2020-2024 ioDraw All rights reserved
1、查询订单明细表（order_detail）中销量（下单件数）排名第二的商品id，如果不存在返回null，如果存在多个排名第二的商品则需要全部返回。
需要用到的表：
订单明细表：order_detail
代码：
select sku_id from ( select sku_id ,sale_num ,dense_rank() over (order by
sale_num desc ) as drp from ( select sku_id ,sum(sku_num) as sale_num from
order_detail group by sku_id )a )b where drp = 2
结果：
2、查询订单信息表(order_info)中最少连续3天下单的用户id，期望结果如下
订单信息表：order_info
order_id
(订单id)
user_id
(用户id)
create_date
(下单日期)
total_amount
(订单金额)
1
101
2021-09-30
29000.00
10
103
2020-10-02
28000.00
代码
select distinct user_id from ( select user_id ,date1 ,case when
(datediff(date2,date1)=1 and datediff(date3,date2)=1 and
datediff(date3,date1)=2) then 1 else 0 end diff from ( select distinct user_id
,create_date as date1 ,lead(create_date) over (partition by user_id order by
create_date) as date2 ,lead(create_date,2) over (partition by user_id order by
create_date) as date3 from (select distinct user_id,create_date from order_info
)a )b )c where diff =1
结果
3、从订单明细表(order_detail)统计各品类销售出的商品种类数及累积销量最好的商品，
期望结果如下：
category_id
<string>
category_name
<string>
sku_id
<string>
name
<string>
order_num
<bigint>
sku_cnt
<bigint>
1
数码
2
手机壳
302
4
2
厨卫
8
微波炉
253
4
3
户外
12
遮阳伞
349
4
需要用到的表
订单明细表：order_detail
order_detail_id
(订单明细id)
order_id
(订单id)
sku_id
(商品id)
create_date
(下单日期)
price
(商品单价)
sku_num
(商品件数)
1
1
1
2021-09-30
2000.00
2
2
1
3
2021-09-30
5000.00
5
22
10
4
2020-10-02
6000.00
1
23
10
5
2020-10-02
500.00
24
24
10
6
2020-10-02
2000.00
5
商品信息表：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
商品分类信息表：category_info
category_id
(分类id)
category_name
(分类名称)
1
数码
2
厨卫
3
户外
代码：
with t1 as ( select a.category_id ,b.category_name ,count(sku_id) as sku_cnt
from sku_info a left join category_info b on a.category_id =b.category_id group
by a.category_id ,b.category_name) , t2 as ( select * from ( select category_id
,sku_id ,name ,order_num ,rank() over(partition by category_id order by
order_num desc) rk from ( select b.category_id ,a.sku_id ,b.name
,sum(a.sku_num) as order_num from order_detail a left join sku_info b on
a.sku_id=b.sku_id group by b.category_id ,a.sku_id ,b.name )a )b where rk='1' )
select t2.category_id ,t1.category_name ,t2.sku_id ,t2.name ,t2.order_num
,t1.sku_cnt from t2 left join t1 on t2.category_id = t1.category_id
结果：
4、从订单信息表(order_info)中统计每个用户截止其每个下单日期的累积消费金额，以及每个用户在其每个下单日期的VIP等级。
用户vip等级根据累积消费金额计算，计算规则如下：
设累积消费总额为X，
若0=<X<10000,则vip等级为普通会员
若10000<=X<30000,则vip等级为青铜会员
若30000<=X<50000,则vip等级为白银会员
若50000<=X<80000,则vip为黄金会员
若80000<=X<100000,则vip等级为白金会员
若X>=100000,则vip等级为钻石会员
期望结果如下：
user_id
<string>
(用户id)
create_date
<string>
(下单日期)
sum_so_far
<decimal(16,2)>
(截至每个下单日期的累计下单金额)
vip_level
<string>
(每个下单日期的VIP等级)
101
2021-09-27
29000.00
青铜会员
101
2021-09-28
99500.00
白金会员
101
2021-09-29
142800.00
钻石会员
101
2021-09-30
143660.00
钻石会员
102
2021-10-01
171680.00
钻石会员
102
2021-10-02
177850.00
钻石会员
103
2021-10-02
69980.00
黄金会员
103
2021-10-03
75890.00
黄金会员
104
2021-10-03
89880.00
白金会员
105
2021-10-04
120100.00
钻石会员
106
2021-10-04
9390.00
普通会员
106
2021-10-05
119150.00
钻石会员
107
2021-10-05
69850.00
黄金会员
107
2021-10-06
124150.00
钻石会员
108
2021-10-06
101070.00
钻石会员
108
2021-10-07
155770.00
钻石会员
109
2020-10-08
24020.00
青铜会员
109
2021-10-07
153500.00
钻石会员
1010
2020-10-08
51950.00
黄金会员
需要用到的表：
订单信息表：order_info
order_id
(订单id)
user_id
(用户id)
create_date
(下单日期)
total_amount
(订单金额)
1
101
2021-09-30
29000.00
10
103
2020-10-02
28000.00
代码
select * ,case when (sum_so_far >=0 and sum_so_far <10000) then '普通会员' when
(sum_so_far >=10000 and sum_so_far <30000) then '青铜会员' when (sum_so_far >=30000
and sum_so_far <50000) then '白银会员' when (sum_so_far >=50000 and sum_so_far
<80000) then '黄金会员' when (sum_so_far >=80000 and sum_so_far <100000) then
'白金会员' else '钻石会员' end vip_level from ( select user_id ,create_date
,sum(sum_so_far) over(partition by user_id order by create_date rows BETWEEN
unbounded preceding and current row ) as sum_so_far from ( select user_id
,create_date ,sum(total_amount) as sum_so_far from order_info group by user_id
,create_date )a )b
5、从订单信息表(order_info)中查询首次下单后第二天仍然下单的用户占所有下单用户的比例，结果保留一位小数，使用百分数显示
期望结果如下：
percentage
<string>
70.0%
需要用到的表：
订单信息表：order_info
order_id (订单id)
user_id (用户id)
create_date (下单日期)
total_amount (订单金额)
1
101
2021-09-30
29000.00
10
103
2020-10-02
28000.00
代码
with t as ( select user_id ,create_date as date1 ,lag(create_date,1,'null')
over(partition by user_id order by create_date ) as date2 ,lead(create_date)
over(partition by user_id order by create_date ) as date3 from (select distinct
user_id,create_date from order_info)a ) select
concat(round(avg(if(datediff(date3,date1)=1,1,0))*100,1),'%') as percentage
from t where date2='null'