Active Users

Active Users

What is this problem about?

The Active Users interview question focuses on identifying "loyal" users from a login history table. Specifically, you need to find users who have logged into the system for five or more consecutive days. This is a common requirement for generating retention reports or engagement metrics.

Why is this asked in interviews?

Asked by companies like Amazon, this Active Users coding problem is a test of your ability to handle "Gaps and Islands" problems in SQL. Detecting consecutive patterns in data is a sophisticated skill that requires understanding row ordering, date arithmetic, and window functions.

Algorithmic pattern used

The primary Database interview pattern used here involves DENSE_RANK() or ROW_NUMBER(). By subtracting a sequential row number from the login date, all dates in a consecutive sequence will result in the same "base date." You can then group by the User ID and this calculated "base date" to count the length of the streak.

Example explanation

Suppose a user logs in on Feb 1, Feb 2, Feb 3, and Feb 5.

• Feb 1 minus Row 1 = Jan 31
• Feb 2 minus Row 2 = Jan 31
• Feb 3 minus Row 3 = Jan 31
• Feb 5 minus Row 4 = Feb 1 The first three entries form an "island" (Jan 31 group), while the fourth starts a new one. If the count of a group is $\geq 5$, the user is active.

Common mistakes candidates make

• Ignoring Duplicates: Users might log in multiple times on the same day. Failing to use DISTINCT or DENSE_RANK() can lead to counting multiple logins in one day as a "streak."
• Simple Joins: Trying to solve this with self-joins. While possible for 2 days, a self-join for 5 days is highly inefficient and messy.
• Date Logic: Not accounting for the specific syntax of date subtraction in different SQL dialects (e.g., MySQL DATE_SUB vs. PostgreSQL subtraction).

Interview preparation tip

Master the ROW_NUMBER() trick for consecutive sequences. It is the gold standard for "Gaps and Islands" problems and is much more scalable than any other iterative approach.