MySQL用户流失分析：交易间隔计算与流失日期识别需求

Hey there! Let's work through these two MySQL challenges for your user churn analysis—they're totally manageable once you break them down. Let's dive in.

To create these two new columns, we'll use MySQL's LEAD() window function to fetch each user's next activity date, then calculate the gap and flag large intervals.

Here's a query that adds these columns to your result set:

SELECT
    trx_id,
    user_id,
    last_activity,
    -- Get the next transaction date for the same user (ordered chronologically)
    LEAD(last_activity) OVER (PARTITION BY user_id ORDER BY last_activity) AS next_activity_date,
    -- Calculate days between current and next transaction
    DATEDIFF(
        LEAD(last_activity) OVER (PARTITION BY user_id ORDER BY last_activity),
        last_activity
    ) AS days_until_next_trx,
    -- Flag if the gap is longer than 30 days (1 = yes, 0 = no)
    CASE
        WHEN DATEDIFF(
            LEAD(last_activity) OVER (PARTITION BY user_id ORDER BY last_activity),
            last_activity
        ) > 30 THEN 1
        ELSE 0
    END AS is_greater_than_30_days
FROM tbl_activity
ORDER BY user_id, last_activity;

A quick breakdown of what's happening here:

• PARTITION BY user_id ensures we only compare transactions from the same user
• ORDER BY last_activity keeps transactions in chronological order, so LEAD() grabs the actual next transaction
• DATEDIFF() computes the number of days between the current and next activity date
• The CASE statement turns the gap size into a simple binary flag for easy filtering later

If you want to save these columns permanently in a new table, use this instead:

CREATE TABLE tbl_activity_with_churn_metrics AS
SELECT
    trx_id,
    user_id,
    last_activity,
    LEAD(last_activity) OVER (PARTITION BY user_id ORDER BY last_activity) AS next_activity_date,
    DATEDIFF(
        LEAD(last_activity) OVER (PARTITION BY user_id ORDER BY last_activity),
        last_activity
    ) AS days_until_next_trx,
    CASE
        WHEN DATEDIFF(
            LEAD(last_activity) OVER (PARTITION BY user_id ORDER BY last_activity),
            last_activity
        ) > 30 THEN 1
        ELSE 0
    END AS is_greater_than_30_days
FROM tbl_activity;

Churn date is usually defined as the point when a user is considered inactive after their last interaction. For your 30-day threshold, that means the churn date is 30 days after the user's last transaction.

Here's how to calculate this for every user:

SELECT
    user_id,
    MAX(last_activity) AS last_activity_date,
    -- Add 30 days to the last activity date to get the churn threshold
    DATE_ADD(MAX(last_activity), INTERVAL 30 DAY) AS churn_date
FROM tbl_activity
GROUP BY user_id
ORDER BY user_id;

If you also want to flag users who have already churned (based on today's date), add a has_churned column:

SELECT
    user_id,
    MAX(last_activity) AS last_activity_date,
    DATE_ADD(MAX(last_activity), INTERVAL 30 DAY) AS churn_date,
    -- Check if today's date is past the churn date
    CASE
        WHEN CURDATE() > DATE_ADD(MAX(last_activity), INTERVAL 30 DAY) THEN 1
        ELSE 0
    END AS has_churned
FROM tbl_activity
GROUP BY user_id
ORDER BY user_id;

This query groups transactions by user, grabs their most recent activity, calculates the churn date, and tells you if they've already hit that churn threshold.

内容的提问来源于stack exchange，提问作者HPT