能力验证

WITH first_purchase AS (
    SELECT 
        o.user_id,
        u.registration_time,
        MIN(o.create_time) AS first_order_time,
        EXTRACT(EPOCH FROM (MIN(o.create_time) - u.registration_time)) / 86400 AS days_to_first_purchase
    FROM 
        orders o
    JOIN 
        users u ON o.user_id = u.user_id
    WHERE 
        o.order_status > 0 -- 假设订单状态大于0表示有效订单
    GROUP BY 
        o.user_id, u.registration_time
),
time_ranges AS (
    SELECT 
        CASE
            WHEN days_to_first_purchase < 0 THEN '注册前已购买'
            WHEN days_to_first_purchase BETWEEN 0 AND 1 THEN '当天'
            WHEN days_to_first_purchase BETWEEN 1 AND 3 THEN '1-3天'
            WHEN days_to_first_purchase BETWEEN 3 AND 7 THEN '3-7天'
            WHEN days_to_first_purchase BETWEEN 7 AND 14 THEN '1-2周'
            WHEN days_to_first_purchase BETWEEN 14 AND 30 THEN '2-4周'
            WHEN days_to_first_purchase BETWEEN 30 AND 90 THEN '1-3个月'
            WHEN days_to_first_purchase BETWEEN 90 AND 180 THEN '3-6个月'
            WHEN days_to_first_purchase BETWEEN 180 AND 365 THEN '6-12个月'
            ELSE '1年以上'
        END AS time_range,
        COUNT(*) AS user_count
    FROM 
        first_purchase
    GROUP BY 
        CASE
            WHEN days_to_first_purchase < 0 THEN '注册前已购买'
            WHEN days_to_first_purchase BETWEEN 0 AND 1 THEN '当天'
            WHEN days_to_first_purchase BETWEEN 1 AND 3 THEN '1-3天'
            WHEN days_to_first_purchase BETWEEN 3 AND 7 THEN '3-7天'
            WHEN days_to_first_purchase BETWEEN 7 AND 14 THEN '1-2周'
            WHEN days_to_first_purchase BETWEEN 14 AND 30 THEN '2-4周'
            WHEN days_to_first_purchase BETWEEN 30 AND 90 THEN '1-3个月'
            WHEN days_to_first_purchase BETWEEN 90 AND 180 THEN '3-6个月'
            WHEN days_to_first_purchase BETWEEN 180 AND 365 THEN '6-12个月'
            ELSE '1年以上'
        END
)
SELECT 
    time_range,
    user_count,
    ROUND((user_count * 100.0 / (SELECT SUM(user_count) FROM time_ranges)), 2) AS percentage
FROM 
    time_ranges
ORDER BY 
    CASE time_range
        WHEN '注册前已购买' THEN 0
        WHEN '当天' THEN 1
        WHEN '1-3天' THEN 2
        WHEN '3-7天' THEN 3
        WHEN '1-2周' THEN 4
        WHEN '2-4周' THEN 5
        WHEN '1-3个月' THEN 6
        WHEN '3-6个月' THEN 7
        WHEN '6-12个月' THEN 8
        ELSE 9
    END;

WITH top_users AS (
    SELECT 
        o.user_id
    FROM 
        orders o
    WHERE 
        o.order_status > 0
    GROUP BY 
        o.user_id
    ORDER BY 
        SUM(o.total_amount) DESC
    LIMIT 10
),
user_category_preference AS (
    SELECT 
        o.user_id,
        p.category,
        SUM(oi.subtotal) AS category_spending,
        RANK() OVER (PARTITION BY o.user_id ORDER BY SUM(oi.subtotal) DESC) AS category_rank
    FROM 
        orders o
    JOIN 
        order_items oi ON o.order_id = oi.order_id
    JOIN 
        products p ON oi.product_id = p.product_id
    WHERE 
        o.user_id IN (SELECT user_id FROM top_users)
        AND o.order_status > 0
    GROUP BY 
        o.user_id, p.category
)
SELECT 
    u.user_id,
    u.username,
    ucp.category,
    ucp.category_spending,
    ucp.category_rank
FROM 
    user_category_preference ucp
JOIN 
    users u ON ucp.user_id = u.user_id
WHERE 
    ucp.category_rank <= 3
ORDER BY 
    u.user_id, ucp.category_rank;

WITH category_purchases AS (
    SELECT 
        u.user_id,
        p.category,
        COUNT(DISTINCT o.order_id) AS purchase_count
    FROM 
        users u
    JOIN 
        orders o ON u.user_id = o.user_id
    JOIN 
        order_items oi ON o.order_id = oi.order_id
    JOIN 
        products p ON oi.product_id = p.product_id
    WHERE 
        o.order_status > 0
    GROUP BY 
        u.user_id, p.category
)
SELECT 
    category,
    COUNT(DISTINCT user_id) AS total_users,
    ROUND(AVG(purchase_count), 2) AS avg_purchase_count,
    MAX(purchase_count) AS max_purchase_count,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY purchase_count) AS median_purchase_count
FROM 
    category_purchases
GROUP BY 
    category
ORDER BY 
    avg_purchase_count DESC
LIMIT 5;

WITH quarterly_sales AS (
    SELECT 
        DATE_TRUNC('quarter', o.create_time) AS quarter,
        p.category,
        SUM(oi.subtotal) AS quarterly_sales
    FROM 
        orders o
    JOIN 
        order_items oi ON o.order_id = oi.order_id
    JOIN 
        products p ON oi.product_id = p.product_id
    WHERE 
        o.order_status > 0
        AND o.create_time >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY 
        DATE_TRUNC('quarter', o.create_time), p.category
)
SELECT 
    TO_CHAR(quarter, 'YYYY-Q') AS quarter,
    category,
    quarterly_sales,
    ROUND((quarterly_sales / LAG(quarterly_sales) OVER (PARTITION BY category ORDER BY quarter) - 1) * 100, 2) AS qoq_growth
FROM 
    quarterly_sales
ORDER BY 
    quarter, quarterly_sales DESC;