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.
Overview: 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.
Using PostgreSQL, return every user who appears in orders and the maximum number of consecutive calendar dates on which that user placed at least one order. Multiple orders by the same user on the same date count as one ordering day. Return user_id and max_consecutive_order_days, ordered by user_id.
Tables
orders(id INTEGER, user_id INTEGER, order_date DATE)
Hints
- Deduplicate user/date pairs before measuring streak length.
- For consecutive dates, date minus a row number remains constant within each island.