按累计订单数分桶 统计每日各桶独立用户数量

基于以下SQL定义的订单表，需实现两个核心目标：

1. 按用户的累计订单数将其划分至指定桶（bucket_1至bucket_4_more）
2. 统计每日每个桶中的独立用户（distinct user）数量

订单表SQL：

WITH orders AS
      (SELECT '1234' as user_id, '12340' as order_id, DATE(2021, 01, 05) as date
        UNION ALL SELECT '1234', '1234A', DATE(2022, 01, 07)
        UNION ALL SELECT '1234', '1234B', DATE(2022, 02, 10)
        UNION ALL SELECT '1234', '1234C', DATE(2022, 02, 11)
        UNION ALL SELECT '1234', '1234D', DATE(2022, 03, 21)
        UNION ALL SELECT '1234', '1234E', DATE(2022, 06, 23)
        UNION ALL SELECT '1234', '1234F', DATE(2022, 07, 01)
        UNION ALL SELECT '1234', '1234G', DATE(2022, 08, 04)
        UNION ALL SELECT '1234', '1234H', DATE(2022, 08, 08)
        UNION ALL SELECT '1234', '1234I', DATE(2022, 10, 23)
        UNION ALL SELECT '456', '456A', DATE(2022, 01, 11)
        UNION ALL SELECT '456', '456B', DATE(2022, 02, 23)
        UNION ALL SELECT '456', '456C', DATE(2022, 03, 08)
        UNION ALL SELECT '456', '456D', DATE(2022, 03, 15)
        UNION ALL SELECT '456', '456E', DATE(2022, 07, 19)
        UNION ALL SELECT '456', '456F', DATE(2022, 08, 12)
      )

已完成步骤 #

• 通过窗口函数获取每个用户的累计订单数：

  COUNT(order_id) OVER(PARTITION BY user_id ORDER BY date) AS cumul_orders
  

• 通过CASE语句完成用户分桶：

  CASE
    WHEN cumul_purchases = 1 THEN 'bucket_1'
    WHEN cumul_purchases = 2 THEN 'bucket_2'
    WHEN cumul_purchases = 3 THEN 'bucket_3'
    WHEN cumul_purchases >= 4 THEN 'bucket_4_more'
  END
  

当前困境 #

无法实现按日期统计每个桶中的独立用户数量，期望输出为每日各桶的独立用户数统计示例：

Date            bucket                value     
2022/01/01      'bucket_1'            0
2022/01/01      'bucket_2'            0
2022/01/01      'bucket_3'            0
2022/01/01      'bucket_4_more'       0
2022/01/02      'bucket_1'            0
2022/01/02      'bucket_2'            0
2022/01/02      'bucket_3'            0
2022/01/02      'bucket_4_more'       0
...
2022/01/07      'bucket_1'            1
2022/01/07      'bucket_2'            0
2022/01/07      'bucket_3'            0
2022/01/07      'bucket_4_more'       0
...
2022/01/11      'bucket_1'            2
2022/01/11      'bucket_2'            0
2022/01/11      'bucket_3'            0
2022/01/11      'bucket_4_more'       0
2022/01/12      'bucket_1'            2
2022/01/12      'bucket_2'            0
2022/01/12      'bucket_3'            0
2022/01/12      'bucket_4_more'       0
...
2022/06/01      'bucket_1'            0
2022/06/01      'bucket_2'            0
2022/06/01      'bucket_3'            0
2022/06/01      'bucket_4_more'       2

核心规则 #

每个日期下，用户根据累计订单数归属对应桶，独立用户仅在所属桶中被统计一次。

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