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
;

结果展示

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 2
    评论
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

话数Science

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值