SQL面试题正误核验：统计2020年有多次block记录的门店产品总数

我今日参加企业面试时遇到下述SQL问题，我认为自己提交的查询逻辑正确，但面试官并未认可。我回到本地环境复现了同类数据并运行了自己写的查询，始终未发现问题，想请教大家该解法是否错误，感谢阅览。

问题要求 #

统计2020年每家门店中拥有超过1次block记录的产品总数量。

原题测试数据 #

你编写的查询代码 #

; with cte as
(
select 1 as 'store_id', '2020-01-01' as 'action_day' ,'1A' as product_id, 'block' as 'action_type' Union all
select 1 , '2020-01-01', '1A' , 'reinstate'  Union all
select 1 , '2020-01-02', '1A' , 'block' Union all
select 3 , '2020-01-03', '2B' , 'block' Union all
select 3 , '2020-01-03', '1A' , 'block' Union all
select 4 , '2020-01-04', '2C' , 'block' 
)

Select store_id, count( product_id) as total from cte
where action_type = 'block' and year(action_Day) = 2020
group by store_id
having count(product_id) >1

解法错误点说明 #

• 核心逻辑不符合需求：需求要求先判断单门店下单个产品的block次数是否超过1，再统计每家门店符合该条件的产品总数量。你的SQL直接按门店维度聚合统计block总次数，跳过了按产品维度统计次数的步骤，逻辑完全偏差。
• 结果不符合预期：以原题数据为例，你的SQL会返回门店1的total为2、门店3的total为2。但正确结果应为：门店1仅产品1A满足>1次block的要求，所以total为1；门店3的两个产品各只有1次block，均不满足要求，total为0；门店4的产品2C仅1次block，也不满足要求。
• 测试数据构造有误：你构造的CTE中，门店1第二次block的action_day写为2020-01-02，和原题的2020-01-03不符，虽不影响核心逻辑判断，但测试数据和原题不一致可能会干扰结果校验。

正确写法参考 #

-- 第一步：先按门店+产品维度统计2020年的block次数，筛选出次数>1的产品
WITH product_block_stat AS (
    SELECT 
        store_id,
        product_id
    FROM 表名
    WHERE action_type = 'block' AND YEAR(action_day) = 2020
    GROUP BY store_id, product_id
    HAVING COUNT(*) > 1
)
-- 第二步：按门店维度统计符合条件的产品总数
SELECT 
    store_id,
    COUNT(product_id) AS qualified_product_total
FROM product_block_stat
GROUP BY store_id;

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