Sign in to track progress, unlock prep layers, and browse your pipeline.Sign in
Compute User Login Streaks
Problem: Compute User Login Streaks
You are given a table named user_login_log with the following schema:
user_id: A unique identifier for the user.login_date: The calendar date of a user's login event.
Assumptions & Definitions:
- A user may have multiple login events on the same calendar date. For the purpose of this problem, treat all logins by the same user on the same date as a single login day.
- A "login streak" is defined as a sequence of consecutive calendar dates on which a user logged in at least once.
Tasks:
- Streaks within a Window: For a specified 15-day analysis window, compute and return each user's consecutive login streaks that fall entirely or partially within that window.
- Maximum Streak Length: Compute and return each user's maximum consecutive login streak length across all available historical data in the
user_login_logtable. - Users with Streaks >= 2 Days: Identify and return all
user_ids who have achieved at least one login streak of two or more consecutive days at any point in time.
For each task, provide the SQL queries and discuss the final output schemas.
AI interview prep
Core dilemma
Efficiently identifying and calculating consecutive sequences (streaks) within potentially massive, sparse time-series data using SQL, while optimizing for performance and resource consumption.
Mid-level signals3
Senior signals3
Red flags4
3 tradeoffs and 3 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Compute User Login Streaks
Problem: Compute User Login Streaks
You are given a table named user_login_log with the following schema:
user_id: A unique identifier for the user.login_date: The calendar date of a user's login event.
Assumptions & Definitions:
- A user may have multiple login events on the same calendar date. For the purpose of this problem, treat all logins by the same user on the same date as a single login day.
- A "login streak" is defined as a sequence of consecutive calendar dates on which a user logged in at least once.
Tasks:
- Streaks within a Window: For a specified 15-day analysis window, compute and return each user's consecutive login streaks that fall entirely or partially within that window.
- Maximum Streak Length: Compute and return each user's maximum consecutive login streak length across all available historical data in the
user_login_logtable. - Users with Streaks >= 2 Days: Identify and return all
user_ids who have achieved at least one login streak of two or more consecutive days at any point in time.
For each task, provide the SQL queries and discuss the final output schemas.