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

买全所有品类

困难 21-classic 面试经典题型 relational-divisioncount-distincthavingsemi-joinNone

找出买全了所有品类的客户。

  • “买过”只看 status = 'paid' 的订单;cancelled / refunded / pending 订单里的商品不算。
  • “所有品类”指 products 表中出现的全部非 null category(去重),包括从来没卖出过的商品所在的品类。category 为 null 的商品不属于任何品类,买了也不加分,也不计入品类总数。
  • 同一品类买多件、多次只算一次;一个客户的多笔订单合起来算。
  • 只输出 customers 表里存在的客户;customer_id 为 null 或在客户表中找不到的订单忽略。
  • 如果没有任何客户满足条件,输出空表(保留列名)。

输入

  • customers(customer_id, name, email, city, signup_date, tier)
  • products(product_id, name, category, price, cost, launched_on)
  • orders(order_id, customer_id, order_ts, status, channel)
  • order_items(order_id, product_id, qty, unit_price, discount)

输出

列为 customer_id, name,行顺序不限。

输入表 (case base,共 3 个 case:base、mixed、unsold)

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 行
products shop/products
#product_idint64namestringcategorystringpricefloat64costfloat64launched_ondate
1 1 Wireless Mouse Electronics 25.0 16.77 2025-05-07
2 2 USB-C Hub Electronics 49.9 34.17 2025-07-27
3 3 Bluetooth Speaker Electronics 89.0 61.47 2025-02-10
4 4 Mechanical Keyboard Electronics 199.0 105.48 2024-09-19
5 5 4K Monitor Electronics 500.0 282.38 2024-07-10
6 6 Noise-cancel Headphones Electronics 299.0 211.57 2025-01-14
7 7 Python Cookbook Books 59.0 30.08 2025-09-15
8 8 SQL Pocket Guide Books 39.9 22.14 2025-01-20
9 9 数据分析实战 Books 79.0 40.83 2024-12-15
10 10 Clean Code Books 50.0 28.48 2025-03-13
11 11 统计学习方法 Books 99.0 70.22 2025-03-13
12 12 Desk Lamp Home 45.0 24.99 2025-11-04
13 13 Ceramic Mug Home 19.9 11.34 2025-12-02
14 14 Throw Pillow Home 35.0 19.23 2024-06-29
15 15 Wall Clock Home 99.0 47.98 2025-02-23
16 16 Storage Box Home 29.9 15.06 2025-02-27
17 17 Puzzle 1000pc Toys 129.0 59.53 2025-08-19
18 18 Building Blocks Toys 199.0 110.19 2025-04-18
19 19 RC Car Toys 359.0 208.55 2025-11-06
20 20 Plush Panda Toys 89.0 43.14 2026-01-12
显示前 20 行,共 30 行
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 行
order_items shop/order_items
#order_idint64product_idint64qtyint64unit_pricefloat64discountfloat64
1 1001 22 3 298.8 null
2 1001 26 1 190.0 0.1
3 1002 6 2 254.2 0.0
4 1002 9 3 79.0 null
5 1002 19 5 305.1 0.2
6 1003 9 2 79.0 0.1
7 1003 28 2 47.4 0.0
8 1004 5 1 500.0 0.1
9 1005 3 1 89.0 0.0
10 1005 12 4 45.0 null
11 1006 13 1 19.9 0.1
12 1006 25 2 39.9 null
13 1007 27 1 12.5 null
14 1008 3 1 89.0 null
15 1008 16 1 29.9 null
16 1008 17 1 129.0 null
17 1009 2 1 49.9 0.0
18 1009 10 2 42.5 null
19 1009 20 1 84.5 0.05
20 1010 1 1 23.8 0.1
显示前 20 行,共 500 行

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

#customer_idInt64nameString
1 2 Dana Xu
2 5 Tom Zhang
3 9 Uma Chen
4 11 Mia Smith
5 12 Xin Yang
6 26 Mia Xu
共 6 行