如何用SQL实现连续登录奖励逻辑_SQL模拟登录奖励系统

user_login_logs

user_id

login_time

WITH LoginData AS (
    SELECT
        user_id,
        login_time::DATE AS login_date, -- 将登录时间转换为日期
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time::DATE) AS rn
    FROM
        user_login_logs
    WHERE
        login_time >= CURRENT_DATE - INTERVAL '30 days' -- 限制查询范围，提高效率
    GROUP BY user_id, login_time::DATE -- 去重，避免同一天多次登录
),
ContinuousLogin AS (
    SELECT
        user_id,
        login_date,
        login_date - INTERVAL '1 day' * (rn - 1) AS group_start_date, -- 计算连续登录的起始日期
        COUNT(*) OVER (PARTITION BY user_id, (login_date - INTERVAL '1 day' * (rn - 1)) ORDER BY login_date) AS continuous_days
    FROM
        LoginData
)
SELECT
    user_id,
    MAX(continuous_days) AS max_continuous_days,
    CASE
        WHEN MAX(continuous_days) >= 7 THEN '高级奖励'
        WHEN MAX(continuous_days) >= 3 THEN '中级奖励'
        WHEN MAX(continuous_days) >= 1 THEN '初级奖励'
        ELSE '无奖励'
    END AS reward_level
FROM
    ContinuousLogin
GROUP BY
    user_id
ORDER BY
    user_id;

ROW_NUMBER()

group_start_date

login_date - INTERVAL '1 day' * (rn - 1)

COUNT(*)

CREATE MATERIALIZED VIEW daily_continuous_login AS
SELECT
    user_id,
    MAX(continuous_days) AS max_continuous_days
FROM
    (
      -- 这里插入上面计算连续登录天数的SQL代码
    ) AS ContinuousLogin
GROUP BY
    user_id;

-- 刷新物化视图
REFRESH MATERIALIZED VIEW daily_continuous_login;

SELECT
    user_id,
    AVG(session_duration) AS avg_session_duration
FROM
    game_sessions
WHERE
    start_time >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY
    user_id;

SELECT
    user_id,
    login_time
FROM
    user_login_logs
WHERE
    login_time BETWEEN '2023-01-01' AND CURRENT_DATE; -- 限制时间范围，过滤掉明显错误的时间戳

WITH RankedLogins AS (
    SELECT
        user_id,
        login_time,
        ROW_NUMBER() OVER (PARTITION BY user_id, login_time::DATE ORDER BY login_time) AS rn
    FROM
        user_login_logs
)
SELECT
    user_id,
    login_time
FROM
    RankedLogins
WHERE
    rn = 1;