SQL面试题练习 —— 连续签到领金币

题目来源：百度。

1 题目

有用户签到记录表，t_coin_signin，记录用户当天是否完成签到，请计算出每个用户的每个月获得的金币数量；

签到领金币规则如下：

• 用户签到获得1金币；
• 如果用户连续签到3天则第三天获得2金币，如果用户连续签到7天则第7天获得5金币；
• 连续签到7天后连续天数重置，每月签到天数重置；

样例数据

+----------+--------------+----------+
| user_id  | signin_date  | is_sign  |
+----------+--------------+----------+
| 001      | 2024-01-01   | 1        |
| 001      | 2024-01-02   | 1        |
| 001      | 2024-01-03   | 1        |
| 001      | 2024-01-04   | 0        |
| 001      | 2024-01-05   | 1        |
| 001      | 2024-01-06   | 1        |
| 001      | 2024-01-07   | 1        |
| 001      | 2024-01-08   | 1        |
| 001      | 2024-01-09   | 1        |
| 001      | 2024-01-10   | 1        |
| 001      | 2024-01-11   | 1        |
| 001      | 2024-01-12   | 1        |
| 001      | 2024-01-13   | 1        |
| 001      | 2024-01-14   | 1        |
| 001      | 2024-01-15   | 1        |
| 001      | 2024-01-16   | 1        |
| 001      | 2024-01-17   | 1        |
| 001      | 2024-01-18   | 1        |
| 001      | 2024-01-19   | 1        |
| 001      | 2024-01-20   | 0        |
| 001      | 2024-01-21   | 1        |
| 001      | 2024-01-22   | 1        |
| 001      | 2024-01-23   | 1        |
| 001      | 2024-01-24   | 0        |
| 001      | 2024-01-25   | 1        |
| 001      | 2024-01-26   | 1        |
| 001      | 2024-01-27   | 1        |
| 001      | 2024-01-28   | 1        |
| 001      | 2024-01-29   | 0        |
| 001      | 2024-01-30   | 1        |
| 001      | 2024-01-31   | 1        |
| 001      | 2024-02-01   | 1        |
| 001      | 2024-02-02   | 1        |
| 001      | 2024-02-03   | 1        |
| 001      | 2024-02-04   | 1        |
| 001      | 2024-02-05   | 1        |
| 001      | 2024-02-06   | 1        |
| 001      | 2024-02-07   | 1        |
| 001      | 2024-02-08   | 1        |
| 001      | 2024-02-09   | 1        |
| 001      | 2024-02-10   | 1        |
+----------+--------------+----------+

2 建表语句

--建表语句
CREATE TABLE t_coin_signin
(
    user_id     string COMMENT '用户ID',
    signin_date string COMMENT '日期',
    is_sign     bigint COMMENT '是否签到 1-签到，0-未签到'
) COMMENT '签到领金币记录表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
;
-- 插入数据
insert into t_coin_signin(user_id, signin_date, is_sign)
values ('001', '2024-01-01', 1),
       ('001', '2024-01-02', 1),
       ('001', '2024-01-03', 1),
       ('001', '2024-01-04', 0),
       ('001', '2024-01-05', 1),
       ('001', '2024-01-06', 1),
       ('001', '2024-01-07', 1),
       ('001', '2024-01-08', 1),
       ('001', '2024-01-09', 1),
       ('001', '2024-01-10', 1),
       ('001', '2024-01-11', 1),
       ('001', '2024-01-12', 1),
       ('001', '2024-01-13', 1),
       ('001', '2024-01-14', 1),
       ('001', '2024-01-15', 1),
       ('001', '2024-01-16', 1),
       ('001', '2024-01-17', 1),
       ('001', '2024-01-18', 1),
       ('001', '2024-01-19', 1),
       ('001', '2024-01-20', 0),
       ('001', '2024-01-21', 1),
       ('001', '2024-01-22', 1),
       ('001', '2024-01-23', 1),
       ('001', '2024-01-24', 0),
       ('001', '2024-01-25', 1),
       ('001', '2024-01-26', 1),
       ('001', '2024-01-27', 1),
       ('001', '2024-01-28', 1),
       ('001', '2024-01-29', 0),
       ('001', '2024-01-30', 1),
       ('001', '2024-01-31', 1),
       ('001', '2024-02-01', 1),
       ('001', '2024-02-02', 1),
       ('001', '2024-02-03', 1),
       ('001', '2024-02-04', 1),
       ('001', '2024-02-05', 1),
       ('001', '2024-02-06', 1),
       ('001',