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

连续签到领金币

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

连续签到领金币

明确题意:

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


问题分解:

  • 计算每个人连续签到的天次(生成子表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大厂真题 文章被收录于专栏

大厂真题手把手教你怎么解~

全部评论
大佬请问一下定义窗口 window 的用法
2
送花
回复 分享
发布于 2021-12-22 15:12
(ROW_NUMBER() over(wd_uid_dt) - 1) % 7 as sign_idx 中的wd_uid_dt 哪来的,找不到啊
点赞
送花
回复 分享
发布于 2022-01-27 12:38
京东
校招火热招聘中
官网直投
大佬请问下为啥(ROW_NUMBER() over(wd_uid_dt) - 1) % 7 as sign_idx里面要-1呢
点赞
送花
回复 分享
发布于 2022-02-20 21:57
再次看见大佬,respect
点赞
送花
回复 分享
发布于 2022-05-05 18:55

相关推荐

投递字节跳动等公司10个岗位
点赞 评论 收藏
分享
13 2 评论
分享
牛客网
牛客企业服务