跨时段对比ID（新增、现有、流失）的SQL技术咨询

Hey there! Let’s break down how to solve this snapshot-based ID retention and attrition reporting problem. I’ve tackled similar scenarios before, so here’s a practical, step-by-step solution tailored to your needs.

First, let’s clarify the core requirements to make sure we’re aligned:

• You have a monthly snapshot table with ID and snapshot_date fields.
• For a specified target month (usually the latest), generate a report showing:
  • Initial number of IDs (X) from a given start year-month
  • Number of IDs lost (Y) during the period
  • Number of new IDs added (Z) during the period
• Additionally, you need to output every month, every ID, and its status:
  • 0 = Retained (present in current and previous month)
  • +1 = New (present in current month, not in previous)
  • -1 = Churned (not present in current month, but was present in previous)
• Critical edge case: ID=2 is marked as churned in Feb 2018 because it was already churned in Jan 2018—once an ID churns, it stays marked as churned in all subsequent months.

Let’s build this with common table expressions (CTEs) for readability and maintainability. Adjust date functions to match your database (e.g., use DATE_FORMAT instead of DATE_TRUNC for MySQL).

Step 1: Generate all possible month-ID pairs #

We first create a full list of every month present in the snapshot, paired with every ID that ever appears. This ensures we don’t miss any ID-month combinations that need status tagging.

WITH all_months AS (
    -- Get all distinct months from the snapshot table
    SELECT DISTINCT DATE_TRUNC('month', snapshot_date) AS month
    FROM snapshot_table
),
all_ids AS (
    -- Get all distinct IDs that appear in any snapshot
    SELECT DISTINCT id
    FROM snapshot_table
),
month_id_pairs AS (
    -- Create every possible month-ID combination
    SELECT am.month, ai.id
    FROM all_months am
    CROSS JOIN all_ids ai
)

Step 2: Mark ID presence in each month #

Next, we check if each ID was actually present in the snapshot for its paired month.

, id_month_presence AS (
    SELECT 
        mip.month,
        mip.id,
        -- 1 = ID exists in this month's snapshot, 0 = doesn't exist
        CASE WHEN st.id IS NOT NULL THEN 1 ELSE 0 END AS is_present
    FROM month_id_pairs mip
    LEFT JOIN snapshot_table st
        ON mip.id = st.id
        AND DATE_TRUNC('month', st.snapshot_date) = mip.month
)

Step 3: Calculate ID status (New/Retained/Churned) #

This is the core logic, including handling the "permanent churn" edge case:

, id_status AS (
    SELECT 
        month,
        id,
        CASE 
            -- Mark as NEW if present now, but not present last month
            WHEN is_present = 1 
                 AND LAG(is_present) OVER (PARTITION BY id ORDER BY month) = 0 THEN +1
            -- Mark as CHURNED if not present now, but was present last month
            WHEN is_present = 0 
                 AND LAG(is_present) OVER (PARTITION BY id ORDER BY month) = 1 THEN -1
            -- For all other cases:
            ELSE CASE 
                -- If the ID was already churned last month, keep it as churned
                WHEN LAG(status) OVER (PARTITION BY id ORDER BY month) = -1 THEN -1
                -- Otherwise, it's retained (or an initial ID in the first month)
                ELSE 0
            END
        END AS status
    FROM id_month_presence
)

Step 4: Generate the final summary report #

Replace the start and target months with your desired values (e.g., '2018-01-01' as the start, '2018-03-01' as the target):

SELECT 
    -- Count of initial IDs in the start month
    SUM(CASE WHEN month = '2018-01-01' AND is_present = 1 THEN 1 ELSE 0 END) AS initial_ids,
    -- Count of unique IDs that churned during the period
    COUNT(DISTINCT CASE WHEN status = -1 THEN id END) AS churned_ids,
    -- Count of unique IDs that were added during the period
    COUNT(DISTINCT CASE WHEN status = +1 THEN id END) AS new_ids
FROM id_status
WHERE month BETWEEN '2018-01-01' AND '2018-03-01';

The id_status CTE handles the ID=2 scenario automatically: once the ID is marked as -1 (churned) in Jan 2018, the LAG(status) check ensures Feb 2018 and all later months keep the -1 status, even if the ID never reappears.

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