Katkat
Back

Compute User Login Streaks

TemuData Engineer · Technical Screen
EasyData EngineerTechnical ScreenCoding & Algorithms

Last updated

Tests the ability to write a SQL query to identify consecutive daily login streaks from raw event logs, requiring date deduplication, window functions, and streak length aggregation.

Problem Statement

You are given a table named user_logins that logs user authentication events on an e-commerce platform. A user may log in multiple times within the same day.

Write a SQL query to compute the longest consecutive login streak (in days) for each user. A streak is defined as a sequence of consecutive calendar days where the user logged in at least once each day.

Input Schema

Table user_logins:

Column NameTypeDescription
user_idINTUnique identifier for the user
login_timestampTIMESTAMPThe timestamp when the user logged in

Output Schema

Return a result set with the following columns, ordered by user_id ascending:

Column NameTypeDescription
user_idINTUnique identifier for the user
max_streakINTThe maximum number of consecutive login days for this user

Constraints

  • 1 <= user_id <= 10^7
  • Total rows in user_logins is between 1 and 10^6.
  • login_timestamp is between '2020-01-01 00:00:00' and '2025-12-31 23:59:59'.

Example 1

Input:

user_logins
+---------+---------------------+
| user_id | login_timestamp     |
+---------+---------------------+
| 1       | 2024-03-01 08:30:00 |
| 1       | 2024-03-01 19:15:00 |
| 1       | 2024-03-02 10:00:00 |
| 1       | 2024-03-03 14:20:00 |
| 1       | 2024-03-05 09:00:00 |
| 2       | 2024-03-01 11:00:00 |
| 2       | 2024-03-03 12:00:00 |
+---------+---------------------+

Output:

+---------+------------+
| user_id | max_streak |
+---------+------------+
| 1       | 3          |
| 2       | 1          |
+---------+------------+

Explanation:

  • User 1 logged in on March 1 (twice), March 2, and March 3, forming a 3-day streak. They skipped March 4 and logged in on March 5 (1-day streak). The maximum streak is 3.
  • User 2 logged in on March 1 and March 3. Because March 2 is missing, each login is an isolated 1-day streak. The maximum streak is 1.

Edge Cases

  • Multiple logins on the same day must be deduplicated before streak calculation.
  • Users who only logged in once in total should have a max_streak of 1.
  • Large gaps between logins should properly reset the streak counter.

Hints

Hint 1

First, convert `login_timestamp` to a date and deduplicate identical `(user_id, login_date)` pairs.

Hint 2

Use `ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)` and subtract that row number in days from `login_date` to form an invariant group identifier for consecutive dates.

Hint 3

Group by `user_id` and the invariant group identifier to count the streak length, then take the maximum streak per `user_id`.

Follow-up questions

  • How would you modify the query to return the current active streak (i.e., streaks that extend up to today or yesterday)?
  • How does the 'date minus row_number' trick perform at scale, and how would you optimize this query in a distributed engine like Spark SQL or Presto/Trino?
  • If the requirement changes so that missing a single day during a weekend does not break a streak, how would your approach adjust?