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

日期差

简单 08-datetime 日期时间 datetimediffgroupbyjoin

计算每个客户的首单距离注册日过了多少天。

  • 首单是该客户 order_ts 最早的订单,不管订单状态(paid、refunded、cancelled……都算)。
  • 天数按日历日计算:取首单下单时间所在的日期,减去注册日期 signup_date。例如注册日 2025-03-13,首单 2025-03-13 23:59:59 是 0 天,2025-03-14 00:10:00 是 1 天。
  • 首单早于注册日时,天数为负数(例如前一天下单是 -1)。
  • 只输出至少有一笔订单的客户;customer_id 为 null 或在 customers 里找不到的订单忽略。

输入

  • customers(customer_id, name, email, city, signup_date, tier)
  • orders(order_id, customer_id, order_ts, status, channel)

输出

列为 customer_id, days(整数),行顺序不限。

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

customers shop/customers
#customer_idint64namestringemailstringcitystringsignup_datedatetierstring
1 1 Fang Wang fang.wang@shop.io Beijing 2025-03-13 silver
2 2 Dana Xu dana.xu@mail.cn null 2025-02-17 silver
3 3 Li Zhou li.zhou@example.com Guangzhou 2025-10-07 bronze
4 4 Ping Wang ping.wang@example.com null 2025-05-23 gold
5 5 Tom Zhang tom.zhang@example.com Shenzhen 2025-06-26 gold
6 6 王芳 user06@shop.io Hangzhou 2025-02-10 gold
7 7 Victor Xu victor.xu@example.com Shanghai 2025-12-05 bronze
8 8 Dana Chen dana.chen@example.com Hangzhou 2025-11-22 bronze
9 9 Uma Chen uma.chen@shop.io Guangzhou 2025-11-28 null
10 10 Ivy Zhou ivy.zhou@shop.io Hangzhou 2025-07-14 gold
11 11 Mia Smith mia.smith@mail.cn Shanghai 2025-04-28 gold
12 12 Xin Yang xin.yang@example.com Chengdu 2026-01-03 silver
13 13 Xin Smith xin.smith@mail.cn Beijing 2025-05-07 silver
14 14 Zoe Xu zoe.xu@mail.cn Beijing 2025-09-18 silver
15 15 Fang Liu fang.liu@shop.io Guangzhou 2025-08-05 bronze
16 16 杨磊 user16@mail.cn Shanghai 2025-12-15 silver
17 17 Ping Smith ping.smith@mail.cn null 2025-08-11 bronze
18 18 Oscar Zhou oscar.zhou@example.com Shanghai 2025-11-17 null
19 19 Kevin Liu kevin.liu@mail.cn Chengdu 2026-02-03 gold
20 20 Sara Wu sara.wu@example.com null 2025-07-05 gold
显示前 20 行,共 50 行
orders shop/orders
#order_idint64customer_idint64order_tsdatetimestatusstringchannelstring
1 1001 39 2026-01-01 19:38:00 paid app
2 1002 16 2026-01-17 14:07:00 paid app
3 1003 4 2026-01-08 15:40:20 cancelled web
4 1004 2 2026-05-23 10:48:58 paid null
5 1005 12 2026-06-22 11:07:22 paid store
6 1006 17 2026-08-15 05:15:25 paid web
7 1007 9 2026-06-11 01:00:32 refunded store
8 1008 45 2026-09-19 20:47:10 paid web
9 1009 2 2025-10-05 04:53:38 paid app
10 1010 4 2026-02-19 01:39:54 paid app
11 1011 28 2026-03-05 03:04:45 refunded store
12 1012 2 2026-08-01 07:09:17 paid web
13 1013 26 2026-01-22 03:48:14 refunded store
14 1014 2 2026-02-09 01:41:41 paid app
15 1015 5 2026-08-03 19:49:04 refunded web
16 1016 26 2026-02-16 00:43:43 refunded app
17 1017 9 2025-12-06 04:48:57 pending app
18 1018 999 2026-05-19 12:13:43 paid store
19 1019 26 2026-02-05 15:02:26 paid store
20 1020 44 2025-11-24 11:19:00 cancelled web
显示前 20 行,共 200 行

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

#customer_idInt64daysInt64
1 39 9
2 12 1
3 17 248
4 9 8
5 45 210
6 28 22
7 18 4
8 34 4
9 42 93
10 24 26
11 14 120
12 38 270
13 35 269
14 6 186
15 22 124
16 8 48
17 40 189
18 21 348
19 37 293
20 49 10
21 43 309
22 11 153
23 3 184
24 19 170
25 30 261
26 27 1
27 31 201
28 16 33
29 4 230
30 2 204
31 26 0
32 5 52
33 44 18
34 1 356
35 46 189
36 47 308
37 36 320
38 32 187
39 13 424
40 25 104
41 29 191
42 20 194
43 10 72
44 50 204
共 44 行