Flag Accounts With Multi-Country Logins | Dataford Interview Questions

Given a table of login attempts, write a SQL query to flag accounts that have attempted to log in from more than three different countries in a 24-hour period.

Return the earliest qualifying 24-hour window for each flagged account. Treat missing countries as unknown and do not count them as distinct countries.

Output

One row per flagged account with account_id, window_start, window_end, and distinct_country_count
Include only accounts with more than three distinct non-null countries within the window
Order by account_id ascending, then window_start ascending

Schema

login_attempts

Column	Type	Description
attempt_idPK	INT	Unique identifier for the login attempt
account_id	INT	Account that initiated the login attempt
attempted_at	TIMESTAMPTZ	Timestamp when the login attempt occurred
country	VARCHAR(64)	Country associated with the login attempt

Your question is Flag Accounts With Multi-Country Logins. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem