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'

技术
下载桌面版
GitHub
百度网盘(提取码:draw)
Gitee
云服务器优惠
阿里云优惠券
腾讯云优惠券
华为云优惠券
站点信息
问题反馈
邮箱:ixiaoyang8@qq.com
QQ群:766591547
关注微信