# 题解 | #SQL 12.连续签到领金币#

http://www.nowcoder.com/practice/aef5adcef574468c82659e8911bb297f

# 连续签到领金币

### 问题分解：

• 计算每个人连续签到的天次（生成子表t_sign_idx）：
• 计算每次签到的序号和连续签到起始日期（生成子表t_sign_base）：
• 计算每次签到的序号（生成子表t_sign_date_rn）：
• 过滤出活动期内的有效签到记录：
• 有效签到：artical_id = 0 and sign_in = 1
• 活动期：DATE(in_time) >= "2021-07-07" and DATE(in_time) <= "2021-10-31"
• 截取签到日期：DATE(in_time) as sign_dt
• 每次签到序号（按天，相同日期的序号一样）：DENSE_RANK() over(partition by uid order by DATE(in_time)) as rn
• 去重，每天的多次签到只保留一条：SELECT DISTINCT uid, sign_dt, rn
• 计算当前签到往前连续的起始日期：DATE_SUB(sign_dt, INTERVAL rn DAY) as base_dt
• 定义窗口，按每个用户每次起始日期分区，按签到日期排序：window wd_uid_dt as (partition by uid, base_dt order by sign_dt)
• 计算当前签到是一个签到周期内（7天一个周期）的连续签到的天序：(ROW_NUMBER() over(wd_uid_dt) - 1) % 7 as sign_idx
• 按用户和月份分组：group by uid, DATE_FORMAT(sign_dt, "%Y%m")
• 计算每个用户每月获取的金币：
• 第3天和第7天单独奖励，其他为1：case when sign_idx=6 then 7 when sign_idx=2 then 3 else 1 end
• 计算总金币，求和：sum()

### 细节问题：

• 表头重命名：as
• 按月份、ID升序排序：order by `month`, uid;

### 完整代码：

``````select uid, DATE_FORMAT(sign_dt, "%Y%m") as `month`,
sum(case when sign_idx=6 then 7 when sign_idx=2 then 3 else 1 end) as coin
from (
select uid, sign_dt,
(ROW_NUMBER() over(wd_uid_dt) - 1) % 7 as sign_idx
from (
select uid, sign_dt, rn, DATE_SUB(sign_dt, INTERVAL rn DAY) as base_dt
from (
select DISTINCT uid, DATE(in_time) as sign_dt,
DENSE_RANK() over(partition by uid order by DATE(in_time)) as rn
from tb_user_log
where artical_id = 0 and sign_in = 1
and DATE(in_time) >= "2021-07-07" and DATE(in_time) <= "2021-10-31"
) as t_sign_date_rn
) as t_sign_base
window wd_uid_dt as (partition by uid, base_dt order by sign_dt)
) as t_sign_idx
group by uid, `month`
order by `month`, uid;
``````
SQL大厂真题 文章被收录于专栏

2

(ROW_NUMBER() over(wd_uid_dt) - 1) % 7 as sign_idx 中的wd_uid_dt 哪来的，找不到啊

06-11 18:10

07-10 15:22

13 2 评论