题解 | #连续签到领金币#

题解： 从结果出发，用需要的数据形式倒推逻辑

知识点：

• 日期格式化函数求月份、标准日期 date_format(dt, '[format]')
• dense_rank()和rank()的区别，对于并列的情况，dense_rank排序出来是连续的，而rank可能因为间断而跳过一些数字
• 窗口函数partition by的灵活应用，partition by不仅限于某一列，也会包含特定的字段

select uid,
date_format(dt, '%Y%m') mon,
sum(case (continue_dt % 7) 
    when 3 then 3
    when 0 then 7
   else 1 end) coin
from 
(
select uid,
dt,
dense_rank() over(partition by date_sub(dt, INTERVAL rk day) order by dt asc) continue_dt
from 
(
select distinct uid, 
date(in_time) dt,
dense_rank() over(partition by uid order by date(in_time) asc) rk
from tb_user_log
where artical_id = 0
and sign_in = 1
and in_time between '2021-07-07' and '2021-11-01'
) t1 
    ) t2
    group by 1, 2 
    order by 2, 1