带WHERE条件的COUNT未返回全量行：SQL查询缺失user_id=115行问题

核心问题 #

你的查询里WHERE ranked_transact IN (SELECT MAX(ranked_transact) FROM CTE)这一行犯了关键错误：这里计算的是所有用户的全局最大rank值，而非每个用户自己的最大rank值。

举个实际场景的例子：

• user_id=115只有1笔交易，它的ranked_transact是1
• user_id=159有2笔交易，它的ranked_transact最大值是2
• 全局MAX(ranked_transact)结果为2，因此WHERE条件只保留rank等于2的行，user_id=115的rank=1自然被过滤掉了。

修正方案 #

需要针对每个用户单独计算其最大rank值，而非全局统一取值，以下是三种可行写法：

写法1：在CTE中新增每个用户的最大rank列

WITH CTE AS
(
SELECT 
  user_id,
  transaction_date,
  DENSE_RANK() OVER (PARTITION BY user_id ORDER BY transaction_date) as ranked_transact,
  -- 新增列：计算每个用户自己的最大rank
  MAX(DENSE_RANK() OVER (PARTITION BY user_id ORDER BY transaction_date)) OVER (PARTITION BY user_id) AS user_max_rank
FROM user_transactions
)
SELECT 
  transaction_date,
  user_id,
  COUNT(user_id) as purchase_count
FROM CTE
WHERE ranked_transact = user_max_rank
GROUP BY user_id, transaction_date

写法2：用关联子查询匹配每个用户的最大rank

WITH CTE AS
(
SELECT 
  user_id,
  transaction_date,
  DENSE_RANK() OVER (PARTITION BY user_id ORDER BY transaction_date) as ranked_transact
FROM user_transactions
)
SELECT 
  c.transaction_date,
  c.user_id,
  COUNT(c.user_id) as purchase_count
FROM CTE c
-- 子查询仅取当前用户的最大rank
WHERE c.ranked_transact = (SELECT MAX(ranked_transact) FROM CTE WHERE user_id = c.user_id)
GROUP BY c.user_id, c.transaction_date

写法3：参考参考答案的降序排名逻辑

直接按交易日期降序排名，取rank=1（即最新交易），逻辑更简洁：

WITH CTE AS
(
SELECT 
  user_id,
  transaction_date,
  DENSE_RANK() OVER (PARTITION BY user_id ORDER BY transaction_date DESC) as ranked_transact
FROM user_transactions
)
SELECT 
  transaction_date,
  user_id,
  COUNT(user_id) as purchase_count
FROM CTE
WHERE ranked_transact = 1
GROUP BY user_id, transaction_date

补充说明 #

升序取用户自身的MAX(rank)，和降序取rank=1的逻辑完全等价，但你之前的写法错误地使用了全局最大值，导致部分用户的最新交易被过滤。

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