文章目录
1.21 查询每个用户登录日期的最大空档期
1.21.1 题目需求
从登录明细表(user_login_detail)中查询每个用户两个登录日期(以login_ts为准)之间的最大的空档期。统计最大空档期时,用户最后一次登录至今的空档也要考虑在内,假设今天为2021-10-10。期望结果如下:
select user_id,max(diff) max_diff
from
(
select user_id,datediff(next_login_date,login_date) diff
from
(
select user_id,login_date,lead(login_date,1,'2021-10-10') over(partition by user_id order by login_date) next_login_date
from
(
select user_id,date_format(login_ts,'yyyy-MM-dd') login_date
from user_login_detail
group by user_id,date_format(login_ts,'yyyy-MM-dd')
)t1
)t2
)t3
group by user_id;
1.22 查询相同时刻多地登陆的用户
1.22.1 题目需求
从登录明细表(user_login_detail)中查询在相同时刻,多地登陆(ip_address不同)的用户,期望结果如下:
select distinct t2.user_id
from
(
select t1.user_id,if(t1.max_logout is null ,2,if(t1.max_logout<t1.login_ts,1,0)) flag
from
(
select user_id,login_ts,logout_ts,
max(logout_ts)over(partition by user_id order by login_ts rows between unbounded preceding and 1 preceding) max_logout
from user_login_detail
)t1
)t2
where t2.flag=0
1.23 销售额完成任务指标的商品
1.23.1 题目需求
商家要求每个商品每个月需要售卖出一定的销售总额
假设1号商品销售总额大于21000,2号商品销售总额大于10000,其余商品没有要求
请写出SQL从订单详情表中(order_detail)查询连续两个月销售总额大于等于任务总额的商品
结果如下:
先筛选出满足题目要求的sku_id为1、2的在每个月份的销售额:
select sku_id,substring(create_date,1,7) as month,sum(price*sku_num) as money
from order_detail
where sku_id in (1,2)
group by sku_id,substring(create_date,0,7)
having (sku_id=