LeetCode 182: Duplicate Emails (GROUP BY + HAVING Count)

Today we solve LeetCode 182 - Duplicate Emails.

Source: https://leetcode.com/problems/duplicate-emails/

English

Problem Summary

Given table Person(id, email), return all email addresses that appear more than once.

Key Insight

This is a pure aggregation task. Group rows by email, then keep groups whose frequency is greater than 1.

SQL Approach

1) GROUP BY email to collapse each email into one group.
2) HAVING COUNT(*) > 1 to filter duplicated groups.
3) Output the column as Email to match expected schema.

Complexity Analysis

Time: roughly O(n) scan plus grouping cost (depends on engine/indexes).
Space: grouping hash/sort memory determined by database engine.

Common Pitfalls

- Using WHERE COUNT(*) > 1 (invalid, must be HAVING).
- Forgetting output alias Email.
- Grouping by wrong column such as id.

Reference Implementations (Java / Go / C++ / Python / JavaScript)

-- Java (JDBC) executes this SQL directly
SELECT email AS Email
FROM Person
GROUP BY email
HAVING COUNT(*) > 1;

-- Go (database/sql) executes this SQL directly
SELECT email AS Email
FROM Person
GROUP BY email
HAVING COUNT(*) > 1;

-- C++ service layer executes this SQL directly
SELECT email AS Email
FROM Person
GROUP BY email
HAVING COUNT(*) > 1;

# Python (SQLAlchemy / connector) executes this SQL directly
SELECT email AS Email
FROM Person
GROUP BY email
HAVING COUNT(*) > 1;

-- JavaScript (Node DB driver) executes this SQL directly
SELECT email AS Email
FROM Person
GROUP BY email
HAVING COUNT(*) > 1;

中文

题目概述

给定表 Person(id, email)，找出所有重复出现（出现次数大于 1）的邮箱地址。

核心思路

这是标准聚合题。按 email 分组后统计次数，保留计数大于 1 的组即可。

SQL 解法步骤

1）使用 GROUP BY email 按邮箱聚合。
2）使用 HAVING COUNT(*) > 1 过滤重复邮箱。
3）返回列别名为 Email，与题目输出一致。

复杂度分析

时间复杂度通常由扫描与分组阶段决定，可近似看作 O(n) 级别（受执行计划影响）。
空间复杂度由数据库分组中间结构决定。

常见陷阱

- 把聚合过滤写在 WHERE 而不是 HAVING。
- 漏掉输出列别名 Email。
- 错误地按 id 分组导致结果不对。

多语言参考实现（Java / Go / C++ / Python / JavaScript）

-- Java (JDBC) 直接执行 SQL
SELECT email AS Email
FROM Person
GROUP BY email
HAVING COUNT(*) > 1;

-- Go (database/sql) 直接执行 SQL
SELECT email AS Email
FROM Person
GROUP BY email
HAVING COUNT(*) > 1;

-- C++ 服务层直接执行 SQL
SELECT email AS Email
FROM Person
GROUP BY email
HAVING COUNT(*) > 1;

# Python (SQLAlchemy / connector) 直接执行 SQL
SELECT email AS Email
FROM Person
GROUP BY email
HAVING COUNT(*) > 1;

-- JavaScript (Node 驱动) 直接执行 SQL
SELECT email AS Email
FROM Person
GROUP BY email
HAVING COUNT(*) > 1;