← Marshall Wace Interview Insights

Marshall Wace·Software Engineer·Technical Phone Screen·Intermediate

Intermediate
Jun 2026

Summary

SQL question from Marshall Wace for a software engineering role. One question, case-insensitive matching involved, and the kind of thing that looks straightforward until you're actually writing it.

Questions Asked (1)

Q1

Given a login_attempts table with user_id, timestamp, status, and country columns, write a query to return all user_ids who have at least one successful login (status = 'SUCCESS', case-insensitive) across two or more distinct countries. Result should be a single column sorted alphabetically.

Data ModelingAlgorithms & Data Structures
Author's notes

The case-insensitive part is what gets you.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Filter the login_attempts table to only successful logins using a case-insensitive comparison (e.g., UPPER(status) = 'SUCCESS'). Then group by user_id and count the distinct countries, keeping only those with a count >= 2. Finally, sort the resulting user_ids alphabetically.

Pro tip: Mention that you would first clarify the expected behavior for NULL or empty country values, and consider performance implications of filtering before grouping. Also, explicitly state that you're using COUNT(DISTINCT country) to avoid counting the same country multiple times.

1. Filter successful logins

Use a WHERE clause to select only rows where status is 'SUCCESS', ignoring case (e.g., UPPER(status) = 'SUCCESS' or status ILIKE 'SUCCESS').

2. Group by user

Group the filtered results by user_id to aggregate login attempts per user.

3. Count distinct countries

Within each group, count the number of distinct countries using COUNT(DISTINCT country).

4. Filter users with >=2 countries

Apply a HAVING clause to keep only groups where the distinct country count is at least 2.

5. Select and sort

Select the user_id column and add an ORDER BY user_id to sort the results alphabetically.

Key Points to Mention

  • Case-insensitive comparison for status (e.g., UPPER(status) = 'SUCCESS' or ILIKE).
  • Use of COUNT(DISTINCT country) to ensure distinct countries are counted.
  • Filtering with WHERE before grouping for efficiency.
  • Using HAVING to filter aggregated results.
  • Sorting the final output with ORDER BY user_id.
  • Consideration of NULL or empty country values and how they affect the count.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.