SQL技术咨询：月度热销产品、高收益套餐及客户统计问题

我来帮你逐个梳理这些SQL问题，每个问题都拆解清楚并给出可落地的解决方案：

1. 修正每月最高收益套餐的查询 #

你的原SQL存在几个关键问题：

• GROUP BY里不能包含聚合计算后的MonthlySales（这是SUM的结果，不属于原始分组字段）
• 重复的ORDER BY语法错误，且LIMIT 1只会返回全局最高的一条记录，无法实现按月统计每个月的Top套餐

结合Type 2维度表的特性（需要匹配套餐的有效时间范围），正确的方案如下：

WITH MonthlyPlanSales AS (
    SELECT
        s.month AS SalesMonth,
        SUM(s.payment) AS MonthlySales,
        ci.plan_type AS PlanType
    FROM Sales s
    INNER JOIN CustomerInfo ci 
        ON s.customer_id = ci.cust_id
        -- 核心：确保交易月份落在套餐的有效时间范围内
        AND s.month BETWEEN DATE_TRUNC('month', ci.start_date) AND DATE_TRUNC('month', ci.end_date)
    GROUP BY s.month, ci.plan_type
),
RankedPlans AS (
    SELECT
        SalesMonth,
        MonthlySales,
        PlanType,
        -- 按月份分组，对套餐销售额降序排名（RANK保留并列第一，ROW_NUMBER只取一个）
        RANK() OVER (PARTITION BY SalesMonth ORDER BY MonthlySales DESC) AS SalesRank
    FROM MonthlyPlanSales
)
SELECT
    SalesMonth AS month,
    MonthlySales AS "$",
    PlanType AS plan
FROM RankedPlans
WHERE SalesRank = 1;

2. 统计每月新增客户数（month、plan、# new customers） #

新增客户指的是首次启用套餐的客户，利用Type 2表的start_date可以定位每个客户的首次套餐记录：

WITH CustomerFirstPlan AS (
    SELECT
        cust_id,
        plan_type,
        DATE_TRUNC('month', start_date) AS first_month
    FROM (
        SELECT
            cust_id,
            plan_type,
            start_date,
            -- 取每个客户最早的套餐记录
            ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY start_date ASC) AS rn
        FROM CustomerInfo
    ) t
    WHERE rn = 1
)
SELECT
    first_month AS month,
    plan_type AS plan,
    COUNT(DISTINCT cust_id) AS "# new customers"
FROM CustomerFirstPlan
GROUP BY first_month, plan_type
ORDER BY first_month;

如果需要仅统计当月有交易的新增客户，可以关联Sales表做筛选：

WITH CustomerFirstPlan AS (
    SELECT
        cust_id,
        plan_type,
        DATE_TRUNC('month', start_date) AS first_month
    FROM (
        SELECT
            cust_id,
            plan_type,
            start_date,
            ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY start_date ASC) AS rn
        FROM CustomerInfo
    ) t
    WHERE rn = 1
)
SELECT
    cfp.first_month AS month,
    cfp.plan_type AS plan,
    COUNT(DISTINCT s.customer_id) AS "# new customers"
FROM CustomerFirstPlan cfp
INNER JOIN Sales s
    ON cfp.cust_id = s.customer_id
    AND cfp.first_month = s.month
GROUP BY cfp.first_month, cfp.plan_type
ORDER BY cfp.first_month;

3. 统计每月套餐切换的客户数量（month、from plan to plan、# customers） #

套餐切换指的是客户从一个套餐变更为另一个套餐，用窗口函数LAG()可以获取客户的历史套餐记录：

WITH CustomerPlanHistory AS (
    SELECT
        cust_id,
        plan_type,
        start_date,
        -- 获取客户的上一个套餐类型
        LAG(plan_type) OVER (PARTITION BY cust_id ORDER BY start_date ASC) AS previous_plan,
        DATE_TRUNC('month', start_date) AS switch_month
    FROM CustomerInfo
)
SELECT
    switch_month AS month,
    CONCAT(previous_plan, ' to ', plan_type) AS "from plan to plan",
    COUNT(DISTINCT cust_id) AS "# customers"
FROM CustomerPlanHistory
WHERE previous_plan IS NOT NULL -- 排除首次加入的客户
  AND previous_plan != plan_type -- 确保是真正的套餐切换（排除同套餐的版本更新）
GROUP BY switch_month, previous_plan, plan_type
ORDER BY switch_month;

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