df-drills pandas · Polars · DuckDB SQL · DuckDB Relational 题目列表 登录

月度 cohort

困难 21-classic 面试经典题型 cohortretentionmonth-diffcross-joinfill-zero

按注册月份把用户分成 cohort,统计每个 cohort 在注册后第 0~3 个月的活跃留存。

  • cohort_month:signup_ts 所在月份的 1 号(date 类型)。
  • month_offset:事件所在的日历月与注册所在日历月相差的月数,按“年×12+月”计算,跨年照常累加(2025-11 注册、2026-01 的事件 offset 为 2)。与相隔多少天无关:11-30 注册、12-01 的事件 offset 为 1。
  • 只统计 month_offset 为 0、1、2、3 的事件,其余忽略。
  • retained:该 cohort 中在该 offset 月份有任意事件的去重用户数。
  • rate = retained / cohort 人数,不舍入。cohort 人数包括从来没有事件的用户。
  • 每个 cohort 都必须输出 offset 0~3 共 4 行,没有人活跃的月份(包括数据还没覆盖到的未来月份)输出 retained = 0、rate = 0.0。

输入

  • users(user_id, signup_ts, country, device)
  • events(event_id, user_id, ts, event_type, page)

输出

列为 cohort_month(date)、month_offset、retained(整数)、rate(浮点),长表格式,行顺序不限。

输入表 (case base,共 2 个 case:base、edge)

users web/users
#user_idint64signup_tsdatetimecountrystringdevicestring
1 1 2026-06-24 03:08:20 CN web
2 2 2026-06-20 22:19:20 DE web
3 3 2026-09-17 01:45:10 US android
4 4 2026-09-04 07:46:49 JP android
5 5 2026-07-17 14:10:25 CN ios
6 6 2026-07-26 18:38:11 JP android
7 7 2026-09-04 04:54:06 JP ios
8 8 2026-07-19 20:08:09 CN web
9 9 2026-08-09 06:02:40 JP web
10 10 2026-09-08 17:29:29 US android
11 11 2026-06-18 09:52:08 US ios
12 12 2026-08-28 19:51:40 CN android
13 13 2026-09-21 02:59:16 CN web
14 14 2026-08-11 14:56:25 CN android
15 15 2026-08-17 10:25:06 DE android
16 16 2026-06-23 14:41:12 US web
17 17 2026-09-04 08:22:08 CN android
18 18 2026-08-21 13:11:02 US web
19 19 2026-09-08 14:31:03 US ios
20 20 2026-07-14 14:21:42 JP web
显示前 20 行,共 60 行
events web/events
#event_idint64user_idint64tsdatetimeevent_typestringpagestring
1 1 53 2026-06-03 17:57:33 login /home
2 2 53 2026-06-04 15:21:00 visit /home
3 3 53 2026-06-04 15:22:00 view /product/303
4 4 53 2026-06-04 15:32:04 add_to_cart /cart
5 5 56 2026-06-05 23:24:19 login /home
6 6 56 2026-06-05 23:29:18 view /product/202
7 7 56 2026-06-05 23:36:15 view /product/202
8 8 56 2026-06-05 23:47:36 add_to_cart /cart
9 9 56 2026-06-05 23:56:24 checkout /checkout
10 10 56 2026-06-06 19:14:00 visit /home
11 11 56 2026-06-06 19:22:53 view /product/303
12 12 56 2026-06-06 19:25:12 view /home
13 13 56 2026-06-06 19:28:25 add_to_cart /cart
14 14 29 2026-06-07 10:40:01 login /home
15 15 29 2026-06-07 10:46:43 view /product/303
16 16 29 2026-06-07 11:04:46 add_to_cart /cart
17 17 29 2026-06-07 11:09:25 checkout /checkout
18 18 34 2026-06-07 12:57:49 login /home
19 19 34 2026-06-07 13:03:57 view /product/202
20 20 34 2026-06-07 13:11:07 view /home
显示前 20 行,共 1500 行

期望输出 (case base,行顺序不限)

#cohort_monthDatemonth_offsetInt64retainedInt64rateFloat64
1 2026-06-01 0 14 1.0
2 2026-06-01 1 10 0.7142857143
3 2026-06-01 2 7 0.5
4 2026-06-01 3 10 0.7142857143
5 2026-09-01 0 21 1.0
6 2026-09-01 1 16 0.7619047619
7 2026-08-01 0 12 1.0
8 2026-08-01 1 12 1.0
9 2026-08-01 2 8 0.6666666667
10 2026-07-01 0 13 1.0
11 2026-07-01 1 9 0.6923076923
12 2026-07-01 2 11 0.8461538462
13 2026-07-01 3 8 0.6153846154
14 2026-09-01 2 0 0.0
15 2026-09-01 3 0 0.0
16 2026-08-01 3 0 0.0
共 16 行