Interview Questions/SQL/Longest Consecutive Order Streak Per Customer

Longest Consecutive Order Streak Per Customer

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Longest Consecutive Order Streak Per Customer

You are given an Orders table. Each row records a customer ID and an order date. A customer may have multiple orders on the same day. Write a SQL query to find the longest streak of consecutive calendar days on which each customer placed at least one order. Duplicate order dates for the same customer count as a single active day. Return customer_id and longest_streak, ordered by customer_id. Table: Orders

Column Name	Type	Description
order_id	INT	Primary key
customer_id	INT	ID of the customer
order_date	DATE	Date the order was placed

Examples

Example 1

Customer 1 has Jan 1-3 (streak=3) then a gap on Jan 4. Customer 2 has 2 consecutive days. Customer 3 has 5 consecutive days.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.

query.sql