sql题解--连续签到领金币

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

题目需求

用户每天签到可以领1金币，并可以累计签到天数，连续签到的第3、7天分别可以额外领2和6金币。
每连续签到7天重新累积签到天数。
从用户登录明细表中求出每个用户金币总数，并按照金币总数倒序排序

结果如下：

user_id （用户id）	sum_coin_cn (金币总数)
101	7
109	3
107	3
102	3
106	2
104	2
103	2
1010	2
108	1
105	1

需要用到的表：

用户登录明细表：user_login_detail

user_id(用户id)	ip_address(ip地址)	login_ts(登录时间)	logout_ts(登出时间)
101	180.149.130.161	2021-09-21 08:00:00	2021-09-27 08:30:00
102	120.245.11.2	2021-09-22 09:00:00	2021-09-27 09:30:00
103	27.184.97.3	2021-09-23 10:00:00	2021-09-27 10:30:00

【难点1】连续签到天数

等差数列法

1. 先用dense_rank 对登录日期升序排序 rk

2. 用date_sub(date,rk) 生成 flag ，如果签到日期是连续的，则签到日期-排序得到的日期相同，即flag相同，由此按照得到的日期进行分组，则能计算出用户各连续签到天数。

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

这里，按照连续签到天数7的余数来处理即可

• 当签到天数%7=3 则领取3金币
• 当签到天数%7=0 则领取7金币
• 其余情况，领取1金币

SELECT
    user_id,
    sum(cn) as sum_coin_cn
FROM
(
	SELECT
        user_id,
        CASE
            WHEN rk2%7=0 THEN 7
            WHEN rk2%7=3 THEN 3
            ELSE 1
        end as cn
    FROM

    (
        SELECT
            user_id,
            row_number() over(PARTITION BY user_id,flag order by flag) as rk2
        FROM
        (
            SELECT
                user_id,
                login_date,
                date_sub(login_date,rk) as flag
            FROM
            (
                SELECT
                DISTINCT
                user_id,
                date(login_ts) as login_date,
                dense_rank() over(PARTITION BY user_id order BY date(login_ts) asc) as rk 
                FROM user_login_detail
            )t1
        )t2
    )t3
)t4
GROUP BY user_id