Determine Maximum Consecutive Order Days Per User
Company: Netflix
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Onsite
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.
Quick Answer: Determine Maximum Consecutive Order Days Per User evaluates SQL or pandas logic, joins, grouping, window functions, null handling, edge cases, and validation in a realistic interview setting. A strong answer states assumptions, handles edge cases, explains trade-offs, and shows how to validate the result clearly.
Solution
# Solution Alignment
Use a gaps-and-islands pattern after deduplicating each user's order dates.
## PostgreSQL Query
```sql
WITH user_days AS (
SELECT DISTINCT user_id, order_date::date AS order_date
FROM orders
), numbered AS (
SELECT
user_id,
order_date,
order_date - (ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY order_date
)::int) AS island_key
FROM user_days
), streaks AS (
SELECT user_id, island_key, COUNT(*) AS streak_len
FROM numbered
GROUP BY user_id, island_key
)
SELECT user_id, MAX(streak_len) AS max_consecutive_order_days
FROM streaks
GROUP BY user_id
ORDER BY user_id;
```
## Why It Works
For consecutive calendar days, subtracting the row number from the date produces the same island key for every day in the same streak. Deduplicating `user_id, order_date` first ensures multiple orders on one day do not inflate the streak length.
## Complexity
The dominant cost is sorting within each user for the window function: O(n log n) overall in the usual implementation. Space is O(n) for intermediate rows.
## Edge Cases
Users with multiple orders on the same date, one-day streaks, gaps between dates, and users with no orders if they must be included from a separate users table.