Find Users With Valid E-Mails - Practice Coding Problems

Find Users With Valid E-Mails - Problem

Database Easy

Find Users With Valid E-Mails

You're working as a data analyst for a popular coding platform. The user registration system has been collecting email addresses, but some users have entered invalid emails that are causing issues with the notification system.

Given a Users table with user information, you need to identify all users who have valid email addresses that match the platform's email format requirements.

Email Validation Rules:
✅ Prefix Requirements: Must start with a letter, can contain letters (a-z, A-Z), digits (0-9), underscore (_), period (.), and dash (-)
✅ Domain Requirement: Must end with @leetcode.com

Table Structure:
Users table contains user_id (primary key), name, and mail columns.

Your task is to write a SQL query that returns all users with valid email addresses.

Input & Output

example_1.sql — Basic Valid Emails

$ Input: Users table:\n| user_id | name | mail |\n|---------|----------|-------------------------|\n| 1 | Winston | winston@leetcode.com |\n| 2 | Jonathan | jonathanisgreat |\n| 3 | Annabelle| bella-@leetcode.com |\n| 4 | Sally | sally.come@leetcode.com |\n| 5 | Marwan | quarz#2020@leetcode.com |\n| 6 | David | teewhy@leetcode.com |

› Output: | user_id | name | mail |\n|---------|----------|-------------------------|\n| 1 | Winston | winston@leetcode.com |\n| 4 | Sally | sally.come@leetcode.com |\n| 6 | David | teewhy@leetcode.com |

💡 Note: Winston's email starts with 'w' (letter) and contains only valid characters. Sally's email starts with 's' and uses allowed characters (period). David's email starts with 't' and is valid. Jonathan's email lacks '@leetcode.com', Annabelle's starts with 'b' but contains invalid dash at end of prefix, and Marwan's contains '#' which is not allowed.

example_2.sql — Edge Cases

$ Input: Users table:\n| user_id | name | mail |\n|---------|------|--------------------------|\n| 7 | Alex | a@leetcode.com |\n| 8 | Bob | 123bob@leetcode.com |\n| 9 | Eve | eve_123@leetcode.com |\n| 10 | Max | max.min@leetcode.com |

› Output: | user_id | name | mail |\n|---------|------|----------------------|\n| 7 | Alex | a@leetcode.com |\n| 9 | Eve | eve_123@leetcode.com |\n| 10 | Max | max.min@leetcode.com |

💡 Note: Alex has the shortest valid email (single letter prefix). Eve's email uses underscore and numbers (valid). Max uses a period in the prefix (valid). Bob's email starts with a number '1' instead of a letter, making it invalid.

example_3.sql — Invalid Domain Cases

$ Input: Users table:\n| user_id | name | mail |\n|---------|-------|---------------------------|\n| 11 | John | john@gmail.com |\n| 12 | Jane | jane@leetcode.org |\n| 13 | Mike | mike@leetcode.com.au |\n| 14 | Lisa | lisa.wong@leetcode.com |

› Output: | user_id | name | mail |\n|---------|------|------------------------|\n| 14 | Lisa | lisa.wong@leetcode.com |

💡 Note: Only Lisa's email has the correct domain '@leetcode.com'. John uses Gmail, Jane uses .org instead of .com, and Mike has extra '.au' at the end. All others have wrong domains despite having valid prefixes.

Constraints

1 ≤ Users table rows ≤ 1000
1 ≤ user_id ≤ 10⁶
1 ≤ name.length ≤ 50
1 ≤ mail.length ≤ 100
Email prefix must start with a letter [a-zA-Z]
Email domain must be exactly '@leetcode.com'

Visualization

Tap to expand

Understanding the Visualization

Input Emails

Database contains mixed valid and invalid email addresses

Pattern Matching

REGEXP pattern checks each email against validation rules

Filter Results

Only emails matching the pattern are returned

Valid Emails

Final result contains only properly formatted emails

Key Takeaway

🎯 Key Insight: Regular expressions provide a powerful, single-operation solution for complex string pattern validation, making email filtering both efficient and maintainable.

Asked in

a Amazon 45 G Google 38 ⊞ Microsoft 32 f Meta 28

The optimal solution uses regular expressions (REGEXP) to validate email format in a single condition. The pattern ^[a-zA-Z][a-zA-Z0-9._-]*@leetcode\.com$ efficiently checks that emails start with a letter, contain only valid characters, and end with the correct domain. This approach is concise, readable, and performs in O(n) time.

Common Approaches

Approach	Time	Space	Notes
✓ Regular Expression Pattern (Optimal)	O(n)	O(1)	Use SQL REGEXP to validate email format in a single, elegant pattern
Basic LIKE Pattern Matching	O(n*m)	O(1)	Use basic SQL LIKE patterns to check email format piece by piece

Regular Expression Pattern (Optimal) — Algorithm Steps

Create regex pattern: '^[a-zA-Z][a-zA-Z0-9._-]*@leetcode\.com$'
Use REGEXP operator to match email against pattern
Return all users whose emails match the pattern
Single operation validates all requirements

Visualization

Tap to expand

Step-by-Step Walkthrough

Pattern Start

^ ensures we match from the beginning

First Letter

[a-zA-Z] matches exactly one letter

Valid Characters

[a-zA-Z0-9._-]* matches zero or more allowed characters

Domain Match

@leetcode\.com$ matches the exact domain at the end

Code -

solution.c — C

// Optimal SQL query using regular expressions
#include <stdio.h>

int main() {
    const char* query = """
    SELECT user_id, name, mail
    FROM Users
    WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9._-]*@leetcode\\.com$'
    ORDER BY user_id;
    """;
    
    const char* mysqlQuery = """
    SELECT user_id, name, mail
    FROM Users
    WHERE mail RLIKE '^[a-zA-Z][a-zA-Z0-9._-]*@leetcode\\.com$'
    ORDER BY user_id;
    """;
    
    printf("Optimal Query:\n%s\n", query);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Linear scan through n users with efficient regex matching

✓ Linear Growth

Space Complexity

O(1)

No additional space beyond result set

✓ Linear Space

38.2K Views

Medium Frequency

~15 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Regular Expression Pattern (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler