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

找出重复项

简单 13-dedup 去重 duplicateshavingvalue-counts

找出重复注册的邮箱:把 email 转成小写后,出现超过 1 次的邮箱及其出现次数。

  • 只转小写,不做其他处理。
  • email 为 null 的行不算任何邮箱,不参与统计,也不输出。
  • 没有重复邮箱时输出空表。

输入

  • customers(customer_id, name, email, city, signup_date, tier)

输出

列为 email(小写形式)和 n(出现次数,整数),行顺序不限。

输入表 (case base,共 3 个 case:base、nulls、none)

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 行

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

#emailStringnInt64
1 xin.yang@example.com 2
2 rui.chen@example.com 2
共 2 行