Find Users With Valid E-Mails - Solution & Explanation
Problem Statement
Table: Users
+---------------+---------+ | Column Name | Type | +---------------+---------+ | user_id | int | | name | varchar | | mail | varchar | +---------------+---------+ user_id is the primary key (column with unique values) for this table. This table contains information of the users signed up in a website. Some e-mails are invalid.
Write a solution to find the users who have valid emails.
A valid e-mail has a prefix name and a domain where:
- The prefix name is a string that may contain letters (upper or lower case), digits, underscore
'_', period'.', and/or dash'-'. The prefix name must start with a letter. - The domain is
'@leetcode.com'.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Users table: +---------+-----------+-------------------------+ | user_id | name | mail | +---------+-----------+-------------------------+ | 1 | Winston | winston@leetcode.com | | 2 | Jonathan | jonathanisgreat | | 3 | Annabelle | bella-@leetcode.com | | 4 | Sally | sally.come@leetcode.com | | 5 | Marwan | quarz#2020@leetcode.com | | 6 | David | david69@gmail.com | | 7 | Shapiro | .shapo@leetcode.com | +---------+-----------+-------------------------+ Output: +---------+-----------+-------------------------+ | user_id | name | mail | +---------+-----------+-------------------------+ | 1 | Winston | winston@leetcode.com | | 3 | Annabelle | bella-@leetcode.com | | 4 | Sally | sally.come@leetcode.com | +---------+-----------+-------------------------+ Explanation: The mail of user 2 does not have a domain. The mail of user 5 has the # sign which is not allowed. The mail of user 6 does not have the leetcode domain. The mail of user 7 starts with a period.
Approach Overview
Problem Overview: Given a Users table with user_id, name, and mail, return users whose email addresses are valid. A valid email must start with a letter, contain only letters, digits, underscores, periods, or hyphens before the @, and must end exactly with @leetcode.com.
Approach 1: Regular Expression Filtering (O(n * m) time, O(1) space)
This approach uses SQL regular expression matching to validate the email format directly in the query. You apply a REGEXP pattern such as '^[A-Za-z][A-Za-z0-9_.-]*@leetcode\\.com$' to the mail column. The pattern enforces three rules: the first character must be a letter, the local part can contain letters, digits, underscores, periods, or hyphens, and the domain must match @leetcode.com exactly. The database scans each row and checks the regex against the email string. Time complexity is O(n * m), where n is the number of rows and m is the email length, since regex engines inspect characters in the string. Space complexity is O(1) because filtering happens during the query without additional storage. This method is concise and maps directly to the problem constraints, which makes it the most common solution in database interview questions involving regular expressions.
Approach 2: Parsing and Checking (O(n * m) time, O(1) space)
Instead of relying on regex, you can manually validate each email using string operations. Split the email around @ and confirm the domain equals leetcode.com. Then validate the local part: check that the first character is a letter and iterate through the remaining characters ensuring each belongs to the allowed set (letters, digits, underscore, period, or hyphen). This logic can be implemented in application code or through SQL string functions such as SUBSTRING, LIKE, or character checks. Time complexity remains O(n * m) because every character may be inspected, while space complexity stays O(1). This approach is useful when regex support is limited or when you want explicit control over validation logic using string processing.
Recommended for interviews: Regular expression filtering is the expected solution for this problem. It expresses the constraints in a single pattern and keeps the SQL query short and readable. Manual parsing shows deeper understanding of validation rules and works in environments without regex support, but interviewers typically expect the regex-based query because it directly models the problem statement.
Approach 1: Approach 1: Regular Expression Filtering
In this approach, we use regular expressions to check for valid emails. The regular expression will ensure that the email format adheres to the specified rules: the prefix starts with a letter and consists only of permitted characters, followed by '@leetcode.com'. This method efficiently checks the constraints by leveraging regular expressions, which are well-suited for pattern matching tasks.
This Python solution utilizes the 're' module to define a regular expression pattern that matches valid emails. The pattern checks if the email starts with a letter and is followed by any combination of allowed characters before the fixed domain '@leetcode.com'. The function iterates through the list of users, filters valid emails using 're.match', and returns the filtered list.
Code
Python
Java
C#
JavaScript
Complexity
The time complexity of this solution is O(n), where n is the number of users, as each email is scanned once. The space complexity is O(n) as we store all valid users in a list.
Approach 2: Approach 2: Parsing and Checking
This approach involves manually parsing the email to check the prefix and domain components. We split the email at the '@' character and validate both parts separately. This is an alternative to regex that provides a more step-by-step checking method.
This Python function manually splits the email at '@', checks if the domain is 'leetcode.com', and ensures that the prefix starts with a letter and contains only allowed characters. This approach is more granular and does not use regex, making it useful for understanding email validation through conditional checks.
Complexity
The time complexity is O(n * m), where n is the number of users and m is the length of the largest email string due to manual character checking. The space complexity is O(n) for the result list.
Approach 3: REGEXP Pattern Matching
We can use a regular expression to match valid email formats. The expression ensures that the username part meets the required rules and that the domain is fixed as @leetcode.com.
Complexity Comparison
| Approach | Complexity |
|---|---|
| Approach 1: Regular Expression Filtering | The time complexity of this solution is O(n), where n is the number of users, as each email is scanned once. The space complexity is O(n) as we store all valid users in a list. |
| Approach 2: Parsing and Checking | The time complexity is O(n * m), where n is the number of users and m is the length of the largest email string due to manual character checking. The space complexity is O(n) for the result list. |
| REGEXP Pattern Matching | — |
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Regular Expression Filtering | O(n * m) | O(1) | Best for SQL queries where regex support exists and validation rules map cleanly to a pattern |
| Parsing and Checking | O(n * m) | O(1) | Useful when regex is unavailable or when validation must be implemented using explicit string logic |
Video Solution
Find Users With Valid E-Mails | Leetcode 1517 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 9,675 views views
Watch 9 more video solutions →Frequently Asked Questions
Is Find Users With Valid E-Mails easy or hard?
Find Users With Valid E-Mails Python/Java solution
How to solve Find Users With Valid E-Mails in O(n)?
What is the best approach for Find Users With Valid E-Mails?
Is Find Users With Valid E-Mails asked at Google/Amazon/Meta?
What data structure is used in Find Users With Valid E-Mails?
What is the time complexity of Find Users With Valid E-Mails?
Ready to solve this problem?
Practice Find Users With Valid E-Mails with our built-in code editor and test cases.
Practice on FleetCode