Determine Maximum Consecutive Order Days Per User

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.

Clarifying Questions to Ask

• Clarify SQL dialect or Python library versions, date/time semantics, duplicate handling, and null handling.
• Define the grain of each intermediate result before aggregating.
• State expected output columns and ordering explicitly.

What a Strong Answer Covers

• A query or pandas plan that matches the requested output grain.
• Correct joins, filters, grouping, window functions, and treatment of NULLs or duplicates.
• A brief explanation of why the result is correct and how it handles edge cases.
• Performance notes, indexes/partitioning, and validation queries when relevant.

Follow-up Questions

• How would you test the query on a tiny hand-built dataset?
• What changes if duplicate events or late-arriving data are present?
• Which indexes, clustering, or partitions would help at production scale?

Determine Each User's Longest Consecutive Ordering Streak

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.

Table orders

column	type	constraints
id	INTEGER	PRIMARY KEY
user_id	INTEGER	NOT NULL
order_date	DATE	NOT NULL

Sample data:

id	order_date	user_id
1	2024-01-01	101
2	2024-01-01	101
3	2024-01-02	101
4	2024-01-05	101
5	2024-01-03	102
6	2024-01-04	102
7	2024-01-05	102
8	2024-02-10	103
9	2024-03-01	104
10	2024-03-02	104
11	2024-03-04	104
12	2024-03-05	104

Expected output

user_id	max_consecutive_order_days
101	2
102	3
103	1
104	2

Hints

1. Deduplicate user/date pairs before measuring streak length.
2. For consecutive dates, date minus a row number remains constant within each island.
3. Count each island, then take the maximum count per user.

Solution

Solution Alignment

Use a gaps-and-islands pattern after deduplicating each user's order dates.

PostgreSQL Query

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.