Katkat
Back

Determine Maximum Consecutive Order Days Per User

NetflixData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

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.

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.