N Consecutive Rows Meeting a Condition
The classic 'three consecutive days with sales over X' window pattern.
A LeetCode Classic
This is one of the most-asked SQL interview problems: "Find all dates with at least three consecutive days where sales exceeded a threshold," or the LeetCode favorite "report the stadium with 3+ consecutive rows of attendance over 100."
The shape is always the same: a row qualifies only if it sits inside a run of N consecutive qualifying rows. This lesson shows two clean solutions and the trap that catches most candidates.
The Sample Data
We use a daily sales table. The condition is amount > 100. We must return every day that belongs to a run of 3 or more consecutive calendar days all meeting the condition.
sale_date— one row per dayamount— total sales that day
Key subtlety: the rows must be consecutive in sequence, and for date-based versions, consecutive in the calendar too.
SELECT * FROM sales ORDER BY sale_date;
-- sale_date | amount
-- 2024-03-01 | 120
-- 2024-03-02 | 150
-- 2024-03-03 | 130
-- 2024-03-04 | 90
-- 2024-03-05 | 200All lessons in this course
- Detecting Consecutive Calendar Days
- Longest Streak Per User
- N Consecutive Rows Meeting a Condition
- Current Active Streak as of Today