SQL查询：计算每月特定日期的月度订阅活跃用户数

Alright, let's break down how to solve this problem of counting active monthly subscribers on the 4th of each month, where subscriptions start from October. First, let's align on some assumptions about your data structure (feel free to adjust if your schema differs):

I'll assume you have a table named user_subscriptions with these key fields:

• user_id: Unique identifier for each user
• subscription_start_date: Date when the user first subscribed (must be on or after October 1st of your starting year)
• subscription_end_date: Date when the subscription ended (NULL means the subscription is currently active)

Approach #

The core idea is straightforward:

1. Generate a list of target dates (the 4th of each month starting from your initial October)
2. For each target date, count users whose subscription was active on that day (start date ≤ target date, and subscription hasn't ended before the target date)
3. Filter to only include users who started their subscription in October or later

SQL Solution (MySQL Example) #

Here's a concrete query using a CTE to generate target dates, then joining to your subscription data:

WITH monthly_target_dates AS (
    -- Generate the 4th of each month starting from October 2023 (adjust the start date as needed)
    SELECT 
        DATE_ADD('2023-10-04', INTERVAL n MONTH) AS target_date
    FROM 
        -- Add more UNION ALL entries if you need to cover additional months
        (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS months
)
SELECT 
    DATE_FORMAT(mtd.target_date, '%Y-%m') AS reporting_month,
    COUNT(DISTINCT us.user_id) AS active_users_count
FROM 
    monthly_target_dates mtd
LEFT JOIN 
    user_subscriptions us 
    ON us.subscription_start_date <= mtd.target_date
    AND (us.subscription_end_date IS NULL OR us.subscription_end_date >= mtd.target_date)
    AND us.subscription_start_date >= '2023-10-01' -- Filter for subscriptions starting in October or later
GROUP BY 
    reporting_month
ORDER BY 
    reporting_month;

For PostgreSQL/SQL Server (Recursive CTE for Dynamic Date Ranges) #

If you need a dynamic range that automatically includes up to the current month, use a recursive CTE instead of hardcoding months:

PostgreSQL Version

WITH RECURSIVE monthly_target_dates AS (
    SELECT '2023-10-04'::DATE AS target_date
    UNION ALL
    SELECT (target_date + INTERVAL '1 month')::DATE
    FROM monthly_target_dates
    WHERE target_date < CURRENT_DATE -- Stop at the current month
)
SELECT 
    TO_CHAR(target_date, 'YYYY-MM') AS reporting_month,
    COUNT(DISTINCT us.user_id) AS active_users_count
FROM 
    monthly_target_dates mtd
LEFT JOIN 
    user_subscriptions us 
    ON us.subscription_start_date <= mtd.target_date
    AND (us.subscription_end_date IS NULL OR us.subscription_end_date >= mtd.target_date)
    AND us.subscription_start_date >= '2023-10-01'
GROUP BY 
    reporting_month
ORDER BY 
    reporting_month;

Key Notes #

• Distinct User Count: Always use COUNT(DISTINCT user_id) to avoid counting the same user multiple times if they have multiple subscription records.
• Edge Cases: This query includes users who subscribed on the 4th of the month (since subscription_start_date <= target_date holds true) and excludes users who canceled before the 4th.
• Time Zones: If your data uses time zones, make sure to handle them consistently (e.g., convert all dates to UTC to avoid off-by-one errors).
• Adjust Start Date: Replace 2023-10-01 and 2023-10-04 with your actual starting year's October dates.

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