SQL面试题：连续登录获取奖励

题目

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

建表与数据初始化

炸裂函数初始化数据

CREATE TABLE test_login_details
(
    user_id INT,
    login_date DATE,
    login_time STRING
) COMMENT '登录明细' STORED AS PARQUET;

WITH temp1 AS (
    SELECT 
        posexplode(split(space(10000), '')) AS (n, x)
), temp2 AS (
    SELECT 
        FLOOR(RAND() * 10) + 1000 AS user_id
    FROM temp1
), temp3 AS (
    SELECT 
        user_id,
        CAST(RAND() * 18 AS INT) AS date_index
    FROM
        temp2
), temp4 AS (
    SELECT
        FLOOR(RAND() * 10) + 1000 AS user_id,
        DATE_ADD('2023-09-28', date_index) AS login_date,
        CONCAT(FLOOR(RAND() * 24), ':', FLOOR(RAND() * 60), ':', FLOOR(RAND() * 60)) AS login_time
    FROM
        temp3
)

INSERT OVERWRITE TABLE test_login_details
SELECT
    user_id,
    login_date,
    login_time
FROM
    temp4

直接插入数据

INSERT OVERWRITE TABLE test_login_details VALUES
(1000, '2023-08-01', '12:51:09'),
(1000, '2023-08-02', '12:51:09'),
(1000, '2023-08-03', '12:51:09'),
(1000, '2023-09-01', '12:51:09'),
(1000, '2023-09-02', '12:51:09'),
(1000, '2023-09-03', '12:51:09'),
(1000, '2023-09-04', '12:51:09'),
(1000, '2023-09-05', '12:51:09'),
(1000, '2023-09-06', '12:51:09'),
(1000, '2023-09-07', '12:51:09'),
(1000, '2023-09-08', '12:51:09'),
(1000, '2023-09-09', '12:51:09'),
(1000, '2023-09-10', '12:51:09'),
(1000, '2023-09-11', '12:51:09'),
(1000, '2023-09-12', '12:51:09'),
(1000, '2023-09-13', '12:51:09'),
(1000, '2023-09-14', '12:51:09'),
(1000, '2023-09-15', '12:51:09'),
(1000, '2023-09-16', '12:51:09'),
(1000, '2023-09-17', '12:51:09'),
(1000, '2023-09-18', '12:51:09'),
(1000, '2023-09-19', '12:51:09'),
(1000, '2023-09-20', '12:51:09'),
(1000, '2023-09-21', '12:51:09');

解题

考点：

1. 连续登录
2. 累计领取的金币数

--1.对登录数据进行去重
WITH temp1 AS (
    SELECT
        user_id,
        login_date
    FROM
        test_login_details
    WHERE
        user_id = 1000
    GROUP BY
        user_id,
        login_date
), 
--2.判断是否是连续
temp2 AS (
    SELECT
        user_id,
        login_date,
        date_sub(login_date, row_number() over (partition by user_id order by login_date)) as base_date
    FROM
        temp1
), 
--3.排序
temp3 AS (
    SELECT
        user_id,
        login_date,
        base_date,
        row_number() over (partition by user_id, base_date order by login_date) AS login_day,
        1 AS login_date_num
    FROM
        temp2
),

temp4 AS (
    SELECT
        user_id,
        login_date,
        base_date,
        login_day,
        login_date_num,
        login_day % 7 AS schedule_day,
        case
            when login_day < 3 or (login_day % 7 <> 0 and login_day % 7 < 3) then login_date_num
            when (login_day >=3 and login_day < 7) or (login_day >= 7 and login_day % 7 >=3) then login_date_num + 2
            when login_day = 7 or login_day % 7 = 0 then login_date_num + 6 
        end AS num
    FROM
        temp3
)

SELECT
    user_id,
    login_date,
    login_day,
    login_date_num,
    schedule_day,
    num,
    sum(num) over (partition by base_date order by login_date) AS sum_login_num
FROM
    temp4
ORDER BY
    login_date
;

结果展示