26、从商品信息表（sku_info）求出各分类商品价格的中位数，如果一个分类下的商品个数为偶数则输出中间两个值的平均值，如果是奇数，则输出中间数即可
结果如下：
| category_id |
medprice |
| 1 |
3500.00 |
| 2 |
550.00 |
| 3 |
75.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 |
代码
with t1 as ( -- 类别 商品 排名 各类别商品总数 select category_id ,sku_id ,price ,row_number() over (partition by category_id order by price ) as rn ,count(1) over (partition by category_id rows between unbounded preceding and unbounded following ) as cnt from sku_info ) select category_id ,cast(avg(price) as decimal(10,2) ) as medprice from t1 -- 偶数取中间， 奇数取中间值 where if(cnt %2 = 0,rn in(cnt/2,cnt/2+1), rn = (cnt+1)/2) group by category_id
27、从订单详情表（order_detail）中找出销售额连续3天超过100的商品
结果如下：
| sku_id |
| 1 |
| 10 |
| 11 |
| 12 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
需要用到的表：
订单明细表：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 |
with t as ( -- 商品 日期 销售额 下一行日期 下下一行日期 select sku_id ,create_date ,sum(price*sku_num) as sale_all ,lead(create_date,1,'null') over(partition by sku_id order by create_date) as next_date ,lead(create_date,2,'null') over(partition by sku_id order by create_date) as next_next_date from order_detail group by sku_id ,create_date ) select distinct sku_id from t where sale_all >=100 and datediff(next_date,create_date)=1 and datediff(next_next_date,next_date)=1
28、从用户登录明细表（user_login_detail）中首次登录算作当天新增，第二天也登录了算作一日留存
结果如下：
| first_login |
register |
retention |
| 2021-09-21 |
1 |
0.00 |
| 2021-09-22 |
1 |
0.00 |
| 2021-09-23 |
1 |
0.00 |
| 2021-09-24 |
1 |
0.00 |
| 2021-09-25 |
1 |
0.00 |
| 2021-09-26 |
1 |
0.00 |
| 2021-09-27 |
1 |
0.00 |
| 2021-10-04 |
2 |
0.50 |
| 2021-10-06 |
1 |
0.00 |
需要用到的表：
用户登录明细表：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 |
代码
-- 用户 登录时间 注册日期 注册时间第二天 with t1 as ( select user_id ,substr(login_ts,1,10) as login_ts ,min(login_ts) over(partition by user_id) as date1 ,lead(login_ts,1,'null') over(partition by user_id order by login_ts) as date2 from (select distinct user_id,substr(login_ts,1,10) as login_ts from user_login_detail)a ) ,t2 as ( -- 用户 登录时间 注册日期 注册时间第二天 是否新增 是否留存 select * ,if(login_ts=date1,1,0) as is_new ,if(datediff(date2,date1)=1,1,0) as is_retention from t1 ) -- 注册日期 注册期内新增数 注册期内留存数的用户/注册期内注册的所有用户 select date1 as first_login ,sum(is_new) as register ,cast (sum(is_retention)/count(distinct user_id) as decimal(10,2))as retention from t2 group by date1
转载：https://blog.csdn.net/weixin_43859562/article/details/128662531