百度大数据面试SQL-连续签到领金币

本文首发“数据仓库技术”
百度大数据面试SQL-连续签到领金币

一、题目

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

签到领金币规则如下：

1. 用户签到获得1金币；
2. 如果用户连续签到3天则第三天获得2金币，如果用户连续签到7天则第7天获得5金币；
3. 连续签到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        |
+----------+--------------+----------+

二、分析

1. 本题难度较大，但是依旧是连续问题；
2. 先解决连续问题，然后计算出每天是连续签到第几天；
3. 处理7天重置问题,得到参与活动的实际连续第几天签到；
4. 计算每天得到的金币数量，然后求和得到每月获得的金币总数；

维度	评分
题目难度	⭐️⭐️⭐️⭐️⭐
题目清晰度	⭐️⭐️⭐️⭐️⭐
业务常见度	⭐️⭐️⭐️⭐️

三、SQL

1.根据用户是否签到，判断用户是否连续签到

本题每个用户的日期记录是连续的，给出了当天用户是否签到。我们把签到日期记录为0，未签到日期记录为1。 根据用户、月份进行分组,按照日期排序，得到一个用户连续签到的分组 signin_group。为方便后续处理，增加sign_month 字段。

注意： 这里面的分组数据中包含了用户未签到的日期数据，所以不是标准的连续结果，我们稍后再进行处理。

执行SQL

select user_id,
       signin_date,
       is_sign,
       substr(signin_date, 1, 7)                                                          as sign_month,
       sum(if(is_sign = 1, 0, 1))
           over (partition by user_id,substr(signin_date, 1, 7) order by signin_date asc) as signin_group
from t_coin_signin

查询结果

+----------+--------------+----------+-------------+---------------+
| user_id  | signin_date  | is_sign  | sign_month  | signin_group  |
+----------+--------------+----------+-------------+---------------+
| 001      | 2024-01-01   | 1        | 2024-01     | 0             |
| 001      | 2024-01-02   | 1        | 2024-01     | 0             |
| 001      | 2024-01-03   | 1        | 2024-01     | 0             |
| 001      | 2024-01-04   | 0        | 2024-01     | 1             |
| 001      | 2024-01-05   | 1        | 2024-01     | 1             |
| 001      | 2024-01-06   | 1        | 2024-01     | 1             |
| 001      | 2024-01-07   | 1        | 2024-01     | 1             |
| 001      | 2024-01-08   | 1        | 2024-01     | 1             |
| 001      | 2024-01-09   | 1        | 2024-01     | 1             |
| 001      | 2024-01-10   | 1        | 2024-01     | 1             |
| 001      | 2024-01-11   | 1        | 2024-01     | 1             |
| 001      | 2024-01-12   | 1        | 2024-01     | 1             |
| 001      | 2024-01-13   | 1        | 2024-01     | 1             |
| 001      | 2024-01-14   | 1        | 2024-01     | 1             |
| 001      | 2024-01-15   | 1        | 2024-01     | 1             |
| 001      | 2024-01-16   | 1        | 2024-01     | 1             |
| 001      | 2024-01-17   | 1        | 2024-01     | 1             |
| 001      | 2024-01-18   | 1        | 2024-01     | 1             |
| 001      | 2024-01-19   | 1        | 2024-01     | 1             |
| 001      | 2024-01-20   | 0        | 2024-01     | 2             |
| 001      |