05_new_vs_returning.sql

-- ============================================================
-- 查询05：新老用户行为对比
-- 业务问题：新用户和老用户在消费金额、下单频次、用券行为上有什么差异？
--           差异指导我们对不同用户群用不同的运营策略
-- ============================================================

SELECT
    u.user_type                                           AS 用户类型,
    COUNT(DISTINCT u.user_id)                             AS 用户总数,
    COUNT(o.order_id)                                     AS 总订单数,

    -- 人均订单数：平均每个用户下了多少单
    ROUND(
        COUNT(o.order_id) * 1.0
        / NULLIF(COUNT(DISTINCT u.user_id), 0),
        2
    )                                                     AS 人均订单数,

    -- 平均客单价
    ROUND(AVG(o.amount), 2)                               AS 平均客单价,

    -- 用券率：下单时用了优惠券的比例
    ROUND(
        SUM(CASE WHEN o.used_coupon = TRUE THEN 1 ELSE 0 END) * 100.0
        / NULLIF(COUNT(o.order_id), 0),
        1
    )                                                     AS 用券率百分比,

    -- 人均消费总金额
    ROUND(
        SUM(o.amount)
        / NULLIF(COUNT(DISTINCT u.user_id), 0),
        2
    )                                                     AS 人均消费总额

FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
    AND o.order_date BETWEEN '2025-03-01' AND '2025-03-31'
GROUP BY u.user_type;

-- ============================================================
-- 结果解读及运营策略映射：
--
-- 新用户客单价低、用券率高
--   → 价格敏感，依赖优惠驱动
--   → 策略：新人专属券 + 首单立减，培养第一次消费习惯
--
-- 老用户客单价高、用券率低
--   → 消费习惯稳定，价格不敏感
--   → 策略：维护体验，不要用大额券拉低利润
--   → 可以用权益升级（会员积分、专属客服）替代折扣
--
-- 新用户人均订单数远低于老用户
--   → 新用户还没形成习惯
--   → 策略：在首次消费后7天内触达，设计"第二单激励"提频
-- ============================================================