本题有2个难点：
  1. 解决经典问题 -- 连续签到
  2. 签到金币数量如何转化成代码语言

【难点1】连续签到天数

这是一个经典问题！面试中常会出现！必须熟练！
解题思路：
1、筛选出用户连续签到的dt
2、使用row_number()窗口函数按照dt升序进行排序
  • row_number 排序结果是 1、2、3、4……
  • rank 排序结果是 1、2、2、4……
  • dense_rank 排序结果是 1、2、2、3……
3、如果签到日期是连续的，则签到日期-排序得到的日期相同。由此按照得到的日期进行分组，则能计算出用户各连续签到天数

【难点2】计算签到当日领取金币的数量

先来看领取金币数量规则
  • 用户每天签到可以领1金币，并可以开始累积签到天数，连续签到的第3、7天分别可额外领2、6金币。
  • 每连续签到7天后重新累积签到天数
这里，按照连续签到天数7的余数来处理即可
  • 当签到天数%7=3 则领取3金币
  • 当签到天数%7=0 则领取7金币
  • 其余情况，领取1金币
突破以上2个难点后，就可以开始解题了~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

首先，筛选出各用户符合条件的签到日期，得到<签到表>。条件需要满足
  • 签到日期为2021-07-07 0时至2021-10-31 24时
  • artical_id=0
  • and sign_in=1
  • 签到只以in_time对应的日期为准 
SELECT * FROM tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31'
and artical_id=0
and sign_in=1;
其次，对签到表进行排序。使用窗口函数row_number()
  • 以uid分组
  • 按照date(in_time)排序 
SELECT DISTINCT uid,date(in_time) dt,
row_number() over (partition by uid order by date(in_time)) ranking 
FROM tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31'
and artical_id=0
and sign_in=1;
然后，求出各用户连续登录的天数。用dt-ranking，得到日期相同的为一组。此组数量则为连续签到天数 
SELECT DISTINCT uid,date(in_time) dt,
row_number() over (partition by uid order by date(in_time)) ranking,
date(in_time)-(row_number() over (partition by uid order by date(in_time))) dt2 
FROM tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31'
and artical_id=0
and sign_in=1;
再来计算日期对应的连续签到天数 

select *,row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt) ranking2 FROM
(SELECT DISTINCT uid,date(in_time) dt,
row_number() over (partition by uid order by date(in_time)) ranking,
date(in_time)-(row_number() over (partition by uid order by date(in_time))) dt2
FROM tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31'
and artical_id=0
and sign_in=1)t1
;
有了连续签到天数ranking2，就可以攻克每日领取的金币数量了，取7的余数 

select *,row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt) ranking2,
case 
when row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt)%7=3 then 3
when row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt)%7=0 then 7
else 1 end coin 
FROM
(SELECT DISTINCT uid,date(in_time) dt,
row_number() over (partition by uid order by date(in_time)) ranking,
date(in_time)-(row_number() over (partition by uid order by date(in_time))) dt2
FROM tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31'
and artical_id=0
and sign_in=1)t1
;

好了，按照uid，月份分组，求出对应的coin和。并且按照月份、ID升序排序 

select t2.uid,DATE_FORMAT(t2.dt,'%Y%m') month,sum(t2.coin) coin FROM
(select *,row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt) ranking2,
case 
when row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt)%7=3 then 3
when row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt)%7=0 then 7
else 1 end coin 
FROM
(SELECT DISTINCT uid,date(in_time) dt,
row_number() over (partition by uid order by date(in_time)) ranking,
date(in_time)-(row_number() over (partition by uid order by date(in_time))) dt2
FROM tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31'
and artical_id=0
and sign_in=1)t1
)t2
group by t2.uid,DATE_FORMAT(t2.dt,'%Y%m')
order by t2.uid,DATE_FORMAT(t2.dt,'%Y%m')
;