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 | user_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 | category_name | sku_id | name | order_num | sku_cnt |
1 | 数码 | 2 | 手机壳 | 302 | 4 |
2 | 厨卫 | 8 | 微波炉 | 253 | 4 |
3 | 户外 | 12 | 遮阳伞 | 349 | 4 |
需要用到的表
订单明细表:order_detail
order_detail_id | order_id | sku_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 | name | category_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 | 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 | create_date | sum_so_far | vip_level |
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 | user_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 |
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'