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 | +----+---------+------------+
The commerce team wants to know each customer’s best ordering streak for loyalty analysis.
For every user, return the maximum number of consecutive calendar days on which they placed at least one order.
Generate dense date series per user; use gaps-and-islands or window functions.