-- Active: xiaoding_test@@192.168.110.85@3306@xiaoding_test
WITH
    user_base AS (
        -- 基础用户信息
        SELECT
            id AS user_id,
            nickname,
            created_at AS register_time,
            deleted_at AS logout_time,
            mobile AS contact,
            last_login_time,
            invite_code,
            qrcode,
            avatar,
            state AS user_status
        FROM member_users
        WHERE
            deleted_at IS NULL
    ),
    user_address AS (
        -- 用户默认地址信息
        SELECT
            user_id,
            location AS user_location,
            province,
            city,
            district
        FROM member_address
        WHERE
            is_default = 'YES'
    ),
    user_wallet AS (
        -- 用户钱包信息
        SELECT
            owner_id AS user_id,
            available_balance AS wallet_balance
        FROM wallet
        WHERE
            owner_type = 'USER'
    ),
    user_order_stats AS (
        -- 近3个月订单统计
        SELECT
            user_id,
            COUNT(DISTINCT id) AS order_count,
            COALESCE(AVG(total_amount), 0) AS avg_order_amount
        FROM `order`
        WHERE
            created_at >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
        GROUP BY
            user_id
    ),
    user_violation_stats AS (
        -- 近3个月违规统计
        SELECT user_id, COUNT(DISTINCT id) AS violation_count
        FROM violation_records
        WHERE
            created_at >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
        GROUP BY
            user_id
    ),
    user_inviters AS (
        -- 用户邀请人信息
        SELECT
            t1.user_id,
            l1.nickname AS level1_inviter,
            l2.nickname AS level2_inviter
        FROM
            market_dist_teams t1
            LEFT JOIN member_users l1 ON l1.id = t1.object_id
            AND t1.level = 'LEVEL_1'
            LEFT JOIN market_dist_teams t2 ON t2.user_id = t1.user_id
            AND t2.level = 'LEVEL_2'
            LEFT JOIN member_users l2 ON l2.id = t2.object_id
    )
    -- 最终查询
SELECT
    ub.*,
    ua.user_location,
    ua.province,
    ua.city,
    ua.district,
    uw.wallet_balance,
    COALESCE(uos.order_count, 0) AS order_count,
    COALESCE(uvs.violation_count, 0) AS violation_count,
    COALESCE(uos.avg_order_amount, 0) AS avg_order_amount,
    ui.level1_inviter,
    ui.level2_inviter
FROM
    user_base ub
    LEFT JOIN user_address ua ON ua.user_id = ub.user_id
    LEFT JOIN user_wallet uw ON uw.user_id = ub.user_id
    LEFT JOIN user_order_stats uos ON uos.user_id = ub.user_id
    LEFT JOIN user_violation_stats uvs ON uvs.user_id = ub.user_id
    LEFT JOIN user_inviters ui ON ui.user_id = ub.user_id
ORDER BY ub.register_time DESC
LIMIT 20;