Determine Maximum Consecutive Order Days Per User
Overview
This question evaluates proficiency in data manipulation and time-series reasoning, focusing on date arithmetic, aggregation, and identification of consecutive-day ordering patterns per user.
Netflix
Jul 12, 2025, 6:59 PM
Data Scientist
Onsite
Data Manipulation (SQL/Python)
43
0
orders
+----+---------+------------+ | id | user_id | order_date | +----+---------+------------+ | 1 | 101 | 2024-01-01 | | 2 | 101 | 2024-01-02 | | 3 | 101 | 2024-01-05 | | 4 | 102 | 2024-01-03 | | 5 | 102 | 2024-01-04 | +----+---------+------------+
Scenario
The commerce team wants to know each customer’s best ordering streak for loyalty analysis.
Question
For every user, return the maximum number of consecutive calendar days on which they placed at least one order.
Hints
Generate dense date series per user; use gaps-and-islands or window functions.