SQL如何统计每个用户当前评论之前的历史评论数量

现有基础表base_table结构及样例数据如下：

date      cust_id   review_score
2021-10-19  1              0
2021-08-06  1              7
2021-07-06  1              3
2021-04-06  1              4
2021-07-06  2              5
2021-04-06  2              6

需求说明 #

新增字段num_prior_reviews，统计对应用户在当前评论日期之前的历史评论总数，预期输出如下：

date      cust_id   review_score num_prior_reviews
2021-10-19  1              0              3 
2021-08-06  1              7              2
2021-07-06  1              3              1
2021-04-06  1              4              0
2021-07-06  2              5              1
2021-04-06  2              6              0

逻辑示例：2021年10月19日用户1的评论，此前有4月、7月、8月共3条历史评论，所以num_prior_reviews值为3。
用户已尝试编写代码：row_number() over (partition by cust_id order by date DESC) as rank，需要正确的SQL实现方案。

你原本的开窗函数思路方向正确，只需调整排序规则后做简单计算即可实现，以下是两种可直接使用的方案：

方案1：基于ROW_NUMBER调整（适合单用户同天只有1条评论的场景） #

SELECT
    date,
    cust_id,
    review_score,
    ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY date ASC) - 1 AS num_prior_reviews
FROM base_table
ORDER BY cust_id, date DESC;

逻辑说明：按用户ID分组后按评论日期升序排序，行号从1开始计数，减1后正好等于当前行之前的历史评论数，完全匹配预期输出。

方案2：基于COUNT开窗（通用场景，支持单用户同天多条评论） #

SELECT
    date,
    cust_id,
    review_score,
    COUNT(1) OVER (
        PARTITION BY cust_id 
        ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS num_prior_reviews
FROM base_table
ORDER BY cust_id, date DESC;

逻辑说明：开窗统计每个用户分区内，从最早的评论到当前评论的前1条的总条数，直接得到当前评论之前的历史评论总数，兼容性更强。

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