如何统计仅访问Alpha店、Beta店及两店都访问的客户数量

现有Order表的结构及数据如下：

原Order表 #

需要完成两项操作：

• 为每个客户新增store_visit字段，标记其门店访问类型：仅访问Alpha填Alpha，仅访问Beta填Beta，两者都访问填Both
• 统计不同访问类型的客户数量

预期客户标记结果 #

最终统计结果 #

SQL实现代码 #

生成客户访问类型标记

WITH customer_unique_stores AS (
    -- 去重每个客户的门店记录，避免重复统计
    SELECT DISTINCT customer_id, store
    FROM `Order`
),
customer_visit_tags AS (
    SELECT
        customer_id,
        CASE
            WHEN COUNT(DISTINCT store) = 2 THEN 'Both'
            WHEN MAX(store) = 'Alpha' THEN 'Alpha'
            ELSE 'Beta'
        END AS store_visit
    FROM customer_unique_stores
    GROUP BY customer_id
)
SELECT * FROM customer_visit_tags;

统计各访问类型的客户数量

WITH customer_unique_stores AS (
    SELECT DISTINCT customer_id, store
    FROM `Order`
),
customer_visit_tags AS (
    SELECT
        customer_id,
        CASE
            WHEN COUNT(DISTINCT store) = 2 THEN 'Both'
            WHEN MAX(store) = 'Alpha' THEN 'Alpha'
            ELSE 'Beta'
        END AS store_visit
    FROM customer_unique_stores
    GROUP BY customer_id
)
SELECT
    store_visit,
    COUNT(customer_id) AS count
FROM customer_visit_tags
GROUP BY store_visit;

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