求职 | 看了一套应届生的笔试题，老司机破防了

WITH filtered_orders AS (
    SELECT 
        user_id, 
        order_date, 
        SUM(amount) AS daily_amount
    FROM orders
    WHERE status = 1 AND order_date >= '2024-03-01' AND order_date < '2024-04-01'
    GROUP BY user_id, order_date
),
datediff AS (
    SELECT 
        user_id, 
        order_date, 
        daily_amount,
        LAG(order_date, 1) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_date
    FROM filtered_orders
),
continuous_days AS (
    SELECT 
        user_id, 
        order_date, 
        daily_amount,
        CASE 
            WHEN DATEDIFF(order_date, prev_date) = 1 THEN 0 
            ELSE 1 
        END AS new_group
    FROM datediff
),
grouped_days AS (
    SELECT 
        user_id, 
        order_date, 
        daily_amount,
        SUM(new_group) OVER (PARTITION BY user_id ORDER BY order_date) AS group_id
    FROM continuous_days
),
max_continuous AS (
    SELECT 
        user_id, 
        group_id, 
        COUNT(*) AS continuous_days
    FROM grouped_days
    GROUP BY user_id, group_id
),
user_max_continuous AS (
    SELECT 
        user_id, 
        MAX(continuous_days) AS max_continuous_days
    FROM max_continuous
    GROUP BY user_id
),
max_daily_amount AS (
    SELECT 
        user_id, 
        MAX(daily_amount) AS max_single_day_amount
    FROM filtered_orders
    GROUP BY user_id
)
SELECT 
    u.user_id,
    COALESCE(m.max_continuous_days, 0) AS max_continuous_days,
    COALESCE(a.max_single_day_amount, 0) AS max_single_day_amount
FROM (
    SELECT DISTINCT user_id FROM orders WHERE status = 1 AND order_date >= '2024-03-01' AND order_date < '2024-04-01'
) u
LEFT JOIN user_max_continuous m ON u.user_id = m.user_id
LEFT JOIN max_daily_amount a ON u.user_id = a.user_id
ORDER BY u.user_id;