Users With Excess Failed Logins | Dataford Interview Questions

Write a SQL query to identify users with more than 3 failed login attempts. Use the users and login_attempts tables. Count only attempts whose status is failed.

Output

One row per user with more than 3 failed attempts
Columns: user_id, username, and failed_attempts
Order by failed_attempts descending, then user_id ascending

Schema

users

Column	Type	Description
user_idPK	INT	Unique identifier for the user
username	VARCHAR(100)	Display username

login_attempts

Column	Type	Description
attempt_idPK	INT	Unique identifier for the login attempt
user_id	INT	User associated with the attempt
status	VARCHAR(20)	Login result, such as failed or successful
attempted_at	TIMESTAMPTZ	Time of the login attempt

Your question is Users With Excess Failed Logins. Start with the requirements and the two tables 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