Find users with multi-country successful logins
Company: Marshall Wace
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Given a table login_attempts with columns: user_id (TEXT), timestamp (TIMESTAMP), status (TEXT), and country (TEXT), write an SQL query to return the user_id of all users who have at least one 'SUCcEss' login from two or more different countries. The result should be a single column named user_id, sorted alphabetically.
Overview: This question evaluates proficiency in relational data manipulation, text normalization, aggregation, deduplication, and sorting within the Data Manipulation (SQL/Python) domain, focusing on practical query-writing and data-processing skills.
You are given a table `login_attempts` with columns: `user_id` (TEXT), `timestamp` (TIMESTAMP), `status` (TEXT), and `country` (TEXT).
Write an SQL query to return the `user_id` of all users who have at least one successful login from two or more different countries. The `status` value may have inconsistent capitalization (e.g., 'SUCcEss'), so treat it case-insensitively.
Return a single column named `user_id`, sorted alphabetically.
Tables
login_attempts(user_id TEXT, timestamp TIMESTAMP, status TEXT, country TEXT)
Hints
- Filter to successful logins using a case-insensitive comparison (e.g., LOWER(status) = 'success').
- Group by user_id and use COUNT(DISTINCT country) in the HAVING clause.