01_user_segmentation.sql

-- ============================================================
-- 查询01：用户分层
-- 业务问题：把用户按消费频次分成高频、中频、低频、沉默四类
--           不同层级的用户需要不同的运营策略
-- ============================================================

-- 第一步：统计每个用户在最近30天内的下单次数
-- 第二步：按次数分层，贴上标签
-- 第三步：统计每个层级有多少人

WITH user_order_stats AS (
    SELECT
        user_id,
        COUNT(*) AS order_count  -- 统计每个用户的订单数
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'  -- 只看最近30天
    GROUP BY user_id
),

-- 给每个用户打上分层标签
user_segments AS (
    SELECT
        u.user_id,
        u.city,
        COALESCE(s.order_count, 0) AS order_count,  -- 没有订单的用户补0
        CASE
            WHEN COALESCE(s.order_count, 0) = 0      THEN '沉默用户'   -- 30天没下单
            WHEN COALESCE(s.order_count, 0) < 3      THEN '低频用户'   -- 1-2次
            WHEN COALESCE(s.order_count, 0) < 6      THEN '中频用户'   -- 3-5次
            ELSE                                           '高频用户'   -- 6次及以上
        END AS segment
    FROM users u
    LEFT JOIN user_order_stats s ON u.user_id = s.user_id
    -- LEFT JOIN 保留所有用户，包括30天内没下单的（沉默用户）
)

-- 最终输出：每个层级的用户数量和占比
SELECT
    segment                                           AS 用户层级,
    COUNT(*)                                          AS 用户数,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS 占比百分比
FROM user_segments
GROUP BY segment
ORDER BY
    CASE segment
        WHEN '高频用户' THEN 1
        WHEN '中频用户' THEN 2
        WHEN '低频用户' THEN 3
        WHEN '沉默用户' THEN 4
    END;

-- ============================================================
-- 结果解读：
-- 沉默用户占比高 → 需要设计召回活动（大额优惠券、个性化push）
-- 低频用户占比高 → 需要提频策略（消费满N次送权益）
-- 高频用户是核心资产 → 重点维护体验，不要用大额折扣拉低利润
-- ============================================================