Easy · Database

Duplicate Active Emails

Given Users(id, email, isActive), return Email and Count for every non-empty email used by at least two active users. Compare email strings exactly, ignore inactive users and empty emails, and return rows in any order.

Examples

Example 1

{
  "users": [
    {"id": 1, "email": "a@example.com", "isActive": true},
    {"id": 2, "email": "a@example.com", "isActive": true},
    {"id": 3, "email": "b@example.com", "isActive": true},
    {"id": 4, "email": "b@example.com", "isActive": false},
    {"id": 5, "email": "", "isActive": true},
    {"id": 6, "email": "", "isActive": true}
  ]
}

Output: [{"Email":"a@example.com","Count":2}]

Example 2

{
  "users": [
    {"id": 7, "email": "x@test.io", "isActive": true},
    {"id": 8, "email": "x@test.io", "isActive": true},
    {"id": 9, "email": "Y@test.io", "isActive": true},
    {"id": 10, "email": "Y@test.io", "isActive": true}
  ]
}

Output: [{"Email":"x@test.io","Count":2},{"Email":"Y@test.io","Count":2}]

Rebuild it in the studio

20 free core interview problems with full runner & lessons; explore the complete 380+ library with Pro.