SQL分组多日期校验：判断顾客是否为对应店铺回头客

需求说明 #

现有订单表（假设表名为orders），包含customer、shop、order_date三个字段，需编写SQL查询生成结果集，包含customer、shop、returnedLast30Days字段：

• 当顾客在对应店铺过去365天内订单数>1，且过去30天至少有1笔订单时，returnedLast30Days取值为YES
• 其他情况取值为NO

实现SQL #

SELECT
    customer,
    shop,
    CASE
        WHEN COUNT(CASE WHEN order_date >= CURRENT_DATE - INTERVAL '365 days' THEN 1 END) > 1
             AND COUNT(CASE WHEN order_date >= CURRENT_DATE - INTERVAL '30 days' THEN 1 END) >= 1
        THEN 'YES'
        ELSE 'NO'
    END AS returnedLast30Days
FROM orders
GROUP BY customer, shop;

逻辑解释 #

1. 按customer和shop分组，聚焦每个顾客与店铺的订单组合
2. 第一个条件：通过CASE筛选过去365天内的订单，统计数量是否超过1
3. 第二个条件：用CASE筛选过去30天内的订单，统计数量是否至少为1
4. 两个条件同时满足则标记为回头客（YES），否则标记为NO

测试数据执行结果 #

假设当前日期为2023-03-03，执行上述SQL后结果如下：

内容的提问来源于stack exchange，提问作者Dimo Georgiev