怎么用SQL分析连续登录用户价值_SQL分析连续登录用户价值

WITH UserLogins AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) as rn
    FROM
        login_table
),
ConsecutiveLogins AS (
    SELECT
        user_id,
        login_date,
        DATE(login_date, '-' || (rn - 1) || ' days') AS group_date
    FROM
        UserLogins
)
SELECT
    user_id,
    MIN(login_date) AS start_date,
    MAX(login_date) AS end_date,
    COUNT(*) AS consecutive_days
FROM
    ConsecutiveLogins
GROUP BY
    user_id,
    group_date
ORDER BY
    user_id,
    start_date;

ROW_NUMBER()

GROUP BY

login_table

DATE_SUB()

login_date - (rn - 1)

WITH ConsecutiveLoginUsers AS (
    -- 上面的SQL，用于识别连续登录用户
    SELECT
        user_id,
        MIN(login_date) AS start_date,
        MAX(login_date) AS end_date,
        COUNT(*) AS consecutive_days
    FROM
        ConsecutiveLogins
    GROUP BY
        user_id,
        group_date
),
UserTransactions AS (
    SELECT
        user_id,
        transaction_date,
        amount
    FROM
        transactions_table
)
SELECT
    clu.user_id,
    SUM(ut.amount) AS total_spending
FROM
    ConsecutiveLoginUsers clu
JOIN
    UserTransactions ut ON clu.user_id = ut.user_id
WHERE
    ut.transaction_date BETWEEN clu.end_date AND DATE(clu.end_date, '+30 days')
GROUP BY
    clu.user_id
ORDER BY
    total_spending DESC;