题解 | #牛客的课程订单分析(七)#

SELECT DISTINCT(name) AS source, COUNT(name) AS cnt
FROM (
    SELECT O.id,user_id, product_name, status, client_id, date,is_group_buy,
       CASE WHEN C.id IN (1,2,3,4) THEN name ELSE 'GroupBuy' END AS name
    FROM order_info O
    LEFT JOIN client C
    ON O.client_id = C.id
) AA
WHERE date > '2025-10-15' AND status != 'no_completed' AND ((AA.user_id IN(
    SELECT A.user_id
    FROM (
         SELECT user_id,count(user_id) as cnt
         FROM order_info OO
         WHERE date > '2025-10-15' AND is_group_buy ='No' AND status != 'no_completed'
         group by user_id
    )A
    WHERE A.cnt >= 2) AND is_group_buy ='No') OR (
        AA.user_id IN(
    SELECT A.user_id
    FROM (
         SELECT user_id,count(user_id) as cnt
         FROM order_info OO
         WHERE date > '2025-10-15' AND is_group_buy ='Yes' AND status != 'no_completed'
         group by user_id
    )A
    WHERE A.cnt >= 2) AND is_group_buy ='Yes'
    )
)
GROUP BY name;

思路：要分开计算拼团与不拼团 >=2的订单数