技术问询：查找每月多次购买的唯一ID及分版本统计合规MAU

Hey there! Let's break down your two analytics questions with practical SQL solutions, since that's the standard tool for these kinds of tasks:

The core idea here is to group your data by user ID and month, then filter for groups where the number of purchases is 2 or more. Depending on whether you need the monthly breakdown or just a list of users who ever had multiple purchases in a month, here are two approaches:

Option 1: Get users + their corresponding months with multiple purchases #

Assuming your purchase table is named purchases with columns user_id (unique user identifier) and purchase_date (date of purchase):

SELECT 
    user_id,
    DATE_TRUNC('month', purchase_date) AS purchase_month
FROM 
    purchases
GROUP BY 
    user_id,
    DATE_TRUNC('month', purchase_date)
HAVING 
    COUNT(*) >= 2;

Option 2: Get a list of unique users who ever had multiple purchases in any month #

If you just need the distinct user IDs (not tied to specific months):

SELECT DISTINCT
    user_id
FROM (
    SELECT 
        user_id,
        DATE_TRUNC('month', purchase_date) AS purchase_month
    FROM 
        purchases
    GROUP BY 
        user_id,
        purchase_month
    HAVING 
        COUNT(*) >= 2
) AS monthly_multiple_purchases;

Note: Date functions vary by database. For example:

• MySQL: Use DATE_FORMAT(purchase_date, '%Y-%m-01') instead of DATE_TRUNC
• SQL Server: Use DATEFROMPARTS(YEAR(purchase_date), MONTH(purchase_date), 1)

I see where you might have gone wrong—just adding UNIQUE (or DISTINCT) to your outer query won't filter out users who only used the app once that month. You need to first narrow down to users who meet the "used more than once" condition, then count the unique users per version and month.

Here's the correct approach, assuming your activity table is user_activity with columns user_id, app_version, and activity_date:

SELECT 
    app_version,
    DATE_TRUNC('month', activity_date) AS activity_month,
    COUNT(DISTINCT user_id) AS mau_count
FROM (
    -- First, filter to only users who used the app >1 time per month per version
    SELECT 
        user_id,
        app_version,
        DATE_TRUNC('month', activity_date) AS activity_month
    FROM 
        user_activity
    GROUP BY 
        user_id,
        app_version,
        DATE_TRUNC('month', activity_date)
    HAVING 
        COUNT(*) > 1
) AS qualified_active_users
GROUP BY 
    app_version,
    activity_month
ORDER BY 
    activity_month, app_version;

Why this works: #

1. The inner query groups data by user, app version, and month, then keeps only those groups where the user had more than one activity that month.
2. The outer query then counts the distinct users for each version-month combination—this gives you the MAU you need, since we've already excluded users who only used the app once.

Again, adjust the date function to match your database (use the same alternatives mentioned in the first question if needed).

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