Find Consecutive Login Activity | Dataford Interview Questions

Ameriprise wants to identify sustained engagement with its client digital experience. Given client login events, write a PostgreSQL query that finds each client's consecutive calendar-day login streaks.

Requirements

Treat multiple logins by the same client on one calendar date as a single active day.
Return every streak of at least two consecutive days, including the client ID, client name, start date, end date, and streak length.
Exclude login records with a missing client ID or login date, and sort results by client ID and streak start date.

Schema

clients

Column	Type	Description
client_idPK	INT	Unique Ameriprise client identifier
client_name	VARCHAR(100)	Client display name

client_logins

Column	Type	Description
login_idPK	INT	Unique login event identifier
client_id	INT	Client associated with the login
login_date	DATE	Calendar date of client activity

Your question is Find Consecutive Login Activity. 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