连续签到领金币

题目：用户行为日志表tb_user_log

id	uid	artical_id	in_time	out_time	sign_in
1	101	0	2021-07-07 10:00:00	2021-07-07 10:00:09	1
2	101	0	2021-07-08 10:00:00	2021-07-08 10:00:09	1
3	101	0	2021-07-09 10:00:00	2021-07-09 10:00:42	1
4	101	0	2021-07-10 10:00:00	2021-07-10 10:00:09	1
5	101	0	2021-07-11 23:59:55	2021-07-11 23:59:59	1
6	101	0	2021-07-12 10:00:28	2021-07-12 10:00:50	1
7	101	0	2021-07-13 10:00:28	2021-07-13 10:00:50	1
8	102	0	2021-10-01 10:00:28	2021-10-01 10:00:50	1
9	102	0	2021-10-02 10:00:01	2021-10-02 10:01:50	1
10	102	0	2021-10-03 10:00:55	2021-10-03 11:00:59	1
11	102	0	2021-10-04 10:00:45	2021-10-04 11:00:55	0
12	102	0	2021-10-05 10:00:53	2021-10-05 11:00:59	1
13	102	0	2021-10-06 10:00:45	2021-10-06 11:00:55	1

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

 

场景逻辑说明：

• 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金币）

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

 

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

 

输出示例：

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

 

uid	month	coin
101	202107	15
102	202110	7

 

解释：

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

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

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

 

分析思路：

第一步：对用户登录日期去重，排除一天多次登录的情况，

第二步：对用户的登录日期进行排序，用户分组，日期排序作为排名

第三步：构造辅助列，登录日期与排名作差，相同则表明是连续签到

第四步：根据用户和辅助列分组，日期排序，建立新的排名：连续签到天数

第五步：金币转换，天数为3金币为3，天数为7，金币为7，其余为1(求余思想很关键)

第六步：分组对金币计数，根据用户和日期分组

注意：除了第六步用聚合函数，其余用窗口函数

 

第一步：

select
distinct uid,  -- 去重 过滤掉重复签到的情况
date(in_time) dt-- 登入日期 
from tb_user_log
where artical_id=0 and sign_in=1
and date(in_time)  between '2021-07-07' and '2021-10-31'

 第二步：

select *,
ranK()over(partition by uid order by dt) ranking  #签到日期减去签到排名，（如果是连续签到，则得到的日期相同），
from (
select
distinct uid,  -- 去重 过滤掉重复签到的情况
date(in_time) dt-- 登入日期 
from tb_user_log
where artical_id=0 and sign_in=1
and date(in_time)  between '2021-07-07' and '2021-10-31'
) t1

 第三步：

select *,date_sub(dt,interval ranking day) as '连续签到' from
( select *,
ranK()over(partition by uid order by dt) ranking  #签到日期减去签到排名，（如果是连续签到，则得到的日期相同），
from (
select
distinct uid,  -- 去重 过滤掉重复签到的情况
date(in_time) dt-- 登入日期 
from tb_user_log
where artical_id=0 and sign_in=1
and date(in_time)  between '2021-07-07' and '2021-10-31'
) t1 
)t2

 第四步：

select *,rank()over(partition by uid,连续签到日期 order by dt) as '连续签到天数' from
( select *,date_sub(dt,interval ranking day) as '连续签到日期' from
( select *,
ranK()over(partition by uid order by dt) ranking  #签到日期减去签到排名，（如果是连续签到，则得到的日期相同）
from (
select
distinct uid,  -- 去重 过滤掉重复签到的情况
date(in_time) dt-- 登入日期 
from tb_user_log
where artical_id=0 and sign_in=1
and date(in_time)  between '2021-07-07' and '2021-10-31'
) t1 
)t2
)t3

第五步：

 第六步：

 

• 难点1：如何判断连续签到这个行为？
• 难点2：如何统计连续3天和连续7天额外得的金币？