数据描述

用户行为日志表tb_user_log

（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

场景逻辑说明：artical_id-文章ID代表用户浏览的文章的ID，artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。

问题：统计2021年11月每天的人均浏览文章时长（秒数），结果保留1位小数，并按时长由短到长排序。

输出示例：

示例数据的输出结果如下

解释：

11月1日有2个人浏览文章，总共浏览时长为31+24+11=66秒，人均浏览33秒；

11月2日有2个人浏览文章，总共时长为50+23=73秒，人均时长为36.5秒。

SQL代码

select   
date(in_time) dt,
round(sum(timestampdiff(second,in_time,out_time))/count(distinct uid),1) avg_viiew_len_sec
from  tb_user_log
where date(in_time) like "2021-11%"
and artical_id !=0
group by dt
order by avg_viiew_len_sec

问题：统计每篇文章同一时刻最大在看人数，如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，结果按最大人数降序。

输出示例：

示例数据的输出结果如下

解释：10点0分10秒时，有3个用户正在浏览文章9001；11点01分0秒时，有2个用户正在浏览文章9002。

注意：题目要求在瞬时统计时遵循【先进后出】：如果同一时刻有进入也有离开时，先记录用户数增加，再记录减少。

本题难点在于如何计算瞬时的最大计数（在看人数）

首先，在此对原表in_time和out_time进行编码，in为观看人数+1， out为观看人数-1

然后，进行两次SELECT联立，并按artical_id升序，时间戳升序， 观看记录【先进后出】降序。最后通过max函数取到最大观看人数。

SQL代码

with t as
(select artical_id,
sum(ct) over (partition by artical_id order by dt,ct desc ) uv 
from 
(select artical_id, in_time as dt , 1 ct from tb_user_log
union all
select artical_id, out_time as dt , -1 ct from tb_user_log) p

where  artical_id !=0)

select artical_id, max(uv) max_uv
from t
group by artical_id
order by max_uv desc

问题：统计2021年11月每天新用户的次日留存率（保留2位小数）

注：

• 次日留存率为当天新增的用户数中第二天又活跃了的用户数占比。
• 如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过，结果按日期升序。

输出示例：

示例数据的输出结果如下

解释：

11.01有3个用户活跃101、102、103，均为新用户，在11.02只有101、103两个又活跃了，因此11.01的次日留存率为0.67；

11.02有104一位新用户，在11.03又活跃了，因此11.02的次日留存率为1.00；

11.03有105一位新用户，在11.04未活跃，因此11.03的次日留存率为0.00；

11.04没有新用户，不输出。

解题思路：

1. 先查询出每个用户第一次登陆时间（最小登陆时间）--每天新用户表
2. 因为涉及到跨天活跃，所以要进行并集操作，将登录时间和登出时间取并集，这里union会去重--用户活跃表
3. 将每天新用户表和用户活跃表左连接，只有是同一用户并且该用户第2天依旧登陆才会保留整个记录，否则右表记录为空
4. 得到每天新用户第二天是否登陆表后,开始计算每天的次日留存率：根据日期分组计算，次日活跃用户个数/当天新用户个数

SQL代码

select a.dt as dt,
round(count(b.uid)/count(a.uid),2) uv_left_rate
from
(select uid,min(date(in_time)) as dt 
from tb_user_log
group by uid) a  #注册表
left join 
(
    select uid,date(in_time) dt from tb_user_log b 
    union
    select uid,date(out_time) dt from tb_user_log b 
) as b   #用户活跃表
on a.uid=b.uid and a.dt=date_sub(b.dt,interval 1 day)
where a.dt like "2021-11%" 
group by a.dt

问题：统计活跃间隔对用户分级后，各活跃等级用户占比，结果保留两位小数，且按占比降序排序。

注：

• 用户等级标准简化为：忠实用户(近7天活跃过且非新晋用户)、新晋用户(近7天新增)、沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。
• 假设今天就是数据中所有日期的最大值。
• 近7天表示包含当天T的近7天，即闭区间[T-6, T]。

输出示例：

示例数据的输出结果如下

解释：

今天日期为2021.11.04，根据用户分级标准，用户行为日志表tb_user_log中忠实用户有：109、108、104；新晋用户有105、102；沉睡用户有103；流失用户有101；共7个用户，因此他们的比例分别为0.43、0.29、0.14、0.14。

解题思路：

先建立用户活跃信息表（包含用户id，最后一次活跃日期，成为新用户日期，以及“今天”日期），之后再根据等级划分的条件定义各分层用户，最后统计分层用户占比数据。

SQL代码

with a as
(select 
        case when datediff(now_dt,min_dt)<=6 then "新晋用户"
            when datediff(now_dt,min_dt)>6 and datediff(now_dt,max_dt)<=6 then "忠实用户"
            when datediff(now_dt,max_dt)>=7 and datediff(now_dt,max_dt)<=29 then "沉睡用户"
            when datediff(now_dt,max_dt)>=30 then "流失用户" 
            end as user_grade
from
    (select 
    uid,
    min(date(in_time)) min_dt,  #用户注册时间
    max(date(out_time)) max_dt,  #用户最后一次活跃时间
    max(max(date(out_time)))over() now_dt  #现在的时间
    from tb_user_log
    group by uid) t
 )

 select user_grade,round(count(user_grade)/sum(count(user_grade))over(),2) ratio 
 from a
 group by user_grade
 order by ratio desc;

问题：统计每天的日活数及新用户占比

注：

• 新用户占比=当天的新用户数÷当天活跃用户数（日活数）。
• 如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过。
• 新用户占比保留2位小数，结果按日期升序排序。

输出示例：

示例数据的输出结果如下

解释：

2021年10月31日有2个用户活跃，都为新用户，新用户占比1.00；

2021年11月1日有3个用户活跃，其中1个新用户，新用户占比0.33；

解题思路：

先建立一张用户活跃基础信息表（这张表要包含用户id，活跃日，注册日。因为用户可能1天活跃N次，所以要做去重处理，活跃日直接并联in_time和out_time）；

其次定义新用户（如果dt=new_dt那么这天就是用户首次登录成为新用户的日子），再计算新用户占比；

SQL代码

select  a.dt,
count(distinct a.uid) dau,
round(count(if(a.dt=b.new_dt,b.uid,null))/count(distinct a.uid),2) uv_new_ratio
from 
(select distinct uid,date(in_time) dt from tb_user_log 
union
select distinct uid,date(out_time) dt from tb_user_log ) a
left join
(
    select uid,min(date(in_time)) new_dt from tb_user_log group by uid 
)b
on a.uid=b.uid
group by a.dt
order by a.dt;

问题：计算每个用户2021年7月以来每月获得的金币数（该活动到10月底结束，11月1日开始的签到不再获得金币）。结果按月份、ID升序排序。

注：如果签到记录的in_time-进入时间和out_time-离开时间跨天了，也只记作in_time对应的日期签到了。

场景逻辑说明：

• artical_id-文章ID代表用户浏览的文章的ID，特殊情况artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。注意：只有artical_id为0时sign_in值才有效。
• 从2021年7月7日0点开始，用户每天签到可以领1金币，并可以开始累积签到天数，连续签到的第3、7天分别可额外领2、6金币。
• 每连续签到7天后重新累积签到天数（即重置签到天数：连续第8天签到时记为新的一轮签到的第一天，领1金币）

输出示例：

示例数据的输出结果如下：

解释：

101在活动期内连续签到了7天，因此获得1*7+2+6=15金币；

102在10.01~10.03连续签到3天获得5金币

10.04断签了，10.05~10.06连续签到2天获得2金币，共得到7金币。

SQL代码

WITH t1 AS( -- t1表筛选出活动期间内的数据，并且为了防止一天有多次签到活动，distinct 去重
	SELECT
		DISTINCT uid,
		DATE(in_time) dt,
		DENSE_RANK() over(PARTITION BY uid ORDER BY DATE(in_time)) rn -- 编号
	FROM
		tb_user_log
	WHERE
		DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31' AND artical_id = 0 AND sign_in = 1
),
t2 AS (
	SELECT
	*,
	DATE_SUB(dt,INTERVAL rn day) dt_tmp, 
	case DENSE_RANK() over(PARTITION BY DATE_SUB(dt,INTERVAL rn day),uid ORDER BY dt )%7 -- 再次编号
		WHEN 3 THEN 3
		WHEN 0 THEN 7
		ELSE 1
	END as day_coin -- 用户当天签到时应该获得的金币数
	FROM
	t1
)
	SELECT
		uid,DATE_FORMAT(dt,'%Y%m') `month`, sum(day_coin) coin  -- 总金币数
	FROM
		t2
	GROUP BY
		uid,DATE_FORMAT(dt,'%Y%m')
	ORDER BY
		DATE_FORMAT(dt,'%Y%m'),uid;