Find Valid Emails - Problem
Email Validation Challenge
You're working as a data engineer for a tech company that needs to clean up their user database. The
Your mission: Write a SQL query to identify all valid email addresses based on strict validation rules.
Validation Rules:
• Contains exactly one @ symbol
• Ends with
• Username (before @): only letters, numbers, and underscores
• Domain (between @ and .com): only letters
Input: Users table with user_id and email columns
Output: All rows with valid emails, ordered by user_id ascending
Example:
You're working as a data engineer for a tech company that needs to clean up their user database. The
Users table contains user IDs and email addresses, but some emails are invalid due to data entry errors.Your mission: Write a SQL query to identify all valid email addresses based on strict validation rules.
Validation Rules:
• Contains exactly one @ symbol
• Ends with
.com• Username (before @): only letters, numbers, and underscores
• Domain (between @ and .com): only letters
Input: Users table with user_id and email columns
Output: All rows with valid emails, ordered by user_id ascending
Example:
john_doe123@gmail.com ✅ Validuser@@domain.com ❌ Two @ symbolstest@domain.net ❌ Doesn't end with .com Input & Output
example_1.sql — Basic Valid Emails
$
Input:
Users table:
+--------+-------------------+
| user_id| email |
+--------+-------------------+
| 1 | john@gmail.com |
| 2 | user_name@test.com|
| 3 | invalid@.com |
+--------+-------------------+
›
Output:
+--------+-------------------+
| user_id| email |
+--------+-------------------+
| 1 | john@gmail.com |
| 2 | user_name@test.com|
+--------+-------------------+
💡 Note:
john@gmail.com: valid username 'john' (letters only), valid domain 'gmail' (letters only), ends with .com. user_name@test.com: valid username 'user_name' (letters and underscore), valid domain 'test' (letters only). invalid@.com is rejected because domain is empty.
example_2.sql — Invalid Email Patterns
$
Input:
Users table:
+--------+----------------------+
| user_id| email |
+--------+----------------------+
| 4 | user@@domain.com |
| 5 | test@domain.net |
| 6 | user123@domain123.com|
+--------+----------------------+
›
Output:
+--------+-------+
| user_id| email |
+--------+-------+
+--------+-------+
💡 Note:
All emails are invalid: user@@domain.com has two @ symbols. test@domain.net doesn't end with .com. user123@domain123.com has numbers in domain 'domain123', violating the letters-only rule.
example_3.sql — Mixed Valid and Invalid
$
Input:
Users table:
+--------+------------------------+
| user_id| email |
+--------+------------------------+
| 7 | admin_2@company.com |
| 8 | test.user@site.com |
| 9 | valid123@domain.com |
+--------+------------------------+
›
Output:
+--------+----------------------+
| user_id| email |
+--------+----------------------+
| 7 | admin_2@company.com |
| 9 | valid123@domain.com |
+--------+----------------------+
💡 Note:
admin_2@company.com: valid (underscore and numbers allowed in username). valid123@domain.com: valid (numbers allowed in username, letters-only domain). test.user@site.com: invalid (dot not allowed in username part).
Constraints
- 1 ≤ user_id ≤ 104
- 1 ≤ email.length ≤ 100
- Email contains only printable ASCII characters
- Username must contain only letters (a-z, A-Z), digits (0-9), and underscores (_)
- Domain must contain only letters (a-z, A-Z)
Visualization
Tap to expand
Understanding the Visualization
1
Start Anchor (^)
Ensures pattern starts from the beginning
2
Username ([a-zA-Z0-9_]+)
One or more letters, digits, or underscores
3
At Symbol (@)
Exactly one @ character required
4
Domain ([a-zA-Z]+)
One or more letters only
5
Ending (\.com$)
Must end with literal .com
Key Takeaway
🎯 Key Insight: A well-crafted regex pattern can validate complex email rules in a single, efficient operation, making the code cleaner and more performant than multiple string manipulations.
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code