0Pricing
SQL Interview Prep · Lesson

N Consecutive Rows Meeting a Condition

The classic 'three consecutive days with sales over X' window pattern.

N Consecutive Rows Meeting a Condition is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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 day
  • amount — 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 |  200

Approach 1: Filter Then Island

The robust approach: first keep only qualifying rows, then group the survivors into consecutive islands, then keep islands whose length is at least N.

Step one is the WHERE filter. Step two reuses the gaps-and-islands anchor. Because we filtered first, an island here means "a run of consecutive qualifying days."

WITH qualifying AS (
  SELECT sale_date
  FROM sales
  WHERE amount > 100
)
SELECT * FROM qualifying ORDER BY sale_date;

Anchoring the Qualifying Runs

Number the qualifying rows by date and subtract to get the island anchor. Rows that are consecutive on the calendar AND all qualified will share an anchor; any non-qualifying day was removed, which breaks the run exactly where it should.

WITH qualifying AS (
  SELECT sale_date
  FROM sales
  WHERE amount > 100
),
numbered AS (
  SELECT sale_date,
    ROW_NUMBER() OVER (ORDER BY sale_date) AS rn
  FROM qualifying
)
SELECT sale_date, sale_date - rn AS grp
FROM numbered;

Keeping Long-Enough Islands

Group by the anchor, count the rows, and keep only groups with COUNT(*) >= 3. If the interviewer wants the individual qualifying dates back, join the kept anchors to the numbered rows.

WITH qualifying AS (
  SELECT sale_date FROM sales WHERE amount > 100
),
numbered AS (
  SELECT sale_date,
    ROW_NUMBER() OVER (ORDER BY sale_date) AS rn
  FROM qualifying
),
islands AS (
  SELECT sale_date - rn AS grp, COUNT(*) AS len
  FROM numbered
  GROUP BY sale_date - rn
  HAVING COUNT(*) >= 3
)
SELECT n.sale_date
FROM numbered n
JOIN islands i ON n.sale_date - n.rn = i.grp
ORDER BY n.sale_date;

Approach 2: Sliding COUNT Window

A slicker approach when N is small and fixed: use a window frame to count how many of the surrounding rows also qualify. If any window of N consecutive rows containing this row is all-qualifying, the row is in the answer.

First add a boolean flag, then sum that flag over sliding frames.

SELECT sale_date, amount,
  CASE WHEN amount > 100 THEN 1 ELSE 0 END AS ok
FROM sales;

Summing Over Three Frames

For a run of exactly 3, a qualifying row is in the answer if the 3-row window ending here, centered here, or starting here sums to 3. Compute the three rolling sums and test if any equals 3.

This is the technique behind the LeetCode 601 (Human Traffic of Stadium) solution.

WITH flagged AS (
  SELECT sale_date, amount,
    CASE WHEN amount > 100 THEN 1 ELSE 0 END AS ok
  FROM sales
),
w AS (
  SELECT *,
    SUM(ok) OVER (ORDER BY sale_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS s_end,
    SUM(ok) OVER (ORDER BY sale_date
      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS s_mid,
    SUM(ok) OVER (ORDER BY sale_date
      ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS s_start
  FROM flagged
)
SELECT sale_date, amount
FROM w
WHERE ok = 1 AND (s_end = 3 OR s_mid = 3 OR s_start = 3);

The Calendar Gap Trap

The window-sum approach uses ROWS, which counts adjacent result rows, not adjacent calendar days. If a non-qualifying day was already filtered out, two rows can be adjacent in the result yet not consecutive on the calendar.

Lesson: apply the sliding window to the full daily series (do not pre-filter), or use the date-anchor method which inherently respects calendar gaps. State this trade-off in the interview.

Generalizing to Any N

Approach 1 (filter-then-island) trivially generalizes: just change HAVING COUNT(*) >= N. That is its big advantage over the multi-window sum, which needs more frames as N grows.

For a parameterized or large N, prefer the island method — it is one threshold change rather than N−1 hand-written windows.

-- only the threshold changes for N = 5
HAVING COUNT(*) >= 5

Choosing an Approach

A quick decision guide to say aloud:

  • Filter-then-island: respects calendar gaps, generalizes to any N, returns full runs — the safe default.
  • Sliding window sum: elegant for a fixed small N on a dense daily series, but watch the ROWS-vs-calendar trap.

Naming both, then justifying your pick, is exactly what mid-to-senior interviewers reward.

Full Solution

The portable, any-N answer that respects calendar consecutiveness and returns the qualifying dates:

WITH qualifying AS (
  SELECT sale_date FROM sales WHERE amount > 100
),
numbered AS (
  SELECT sale_date,
    ROW_NUMBER() OVER (ORDER BY sale_date) AS rn
  FROM qualifying
),
islands AS (
  SELECT sale_date - rn AS grp, COUNT(*) AS len
  FROM numbered
  GROUP BY sale_date - rn
  HAVING COUNT(*) >= 3
)
SELECT n.sale_date
FROM numbered n
JOIN islands i ON n.sale_date - n.rn = i.grp
ORDER BY n.sale_date;

Quick Check

Spot the subtle bug.

Recap

For N consecutive rows meeting a condition:

  • Filter-then-island: keep qualifying rows, anchor with date - ROW_NUMBER(), group, and HAVING COUNT(*) >= N. Generalizes and respects calendar gaps.
  • Sliding window sum: flag rows, sum over fixed N-row frames; elegant but beware ROWS-vs-calendar on pre-filtered data.

Next: computing a user's current active streak as of today.

Frequently asked questions

Is the “N Consecutive Rows Meeting a Condition” lesson free?

Yes — the full text of “N Consecutive Rows Meeting a Condition” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “N Consecutive Rows Meeting a Condition”?

The classic 'three consecutive days with sales over X' window pattern. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “N Consecutive Rows Meeting a Condition” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Detecting Consecutive Calendar Days
  2. Longest Streak Per User
  3. N Consecutive Rows Meeting a Condition
  4. Current Active Streak as of Today
← Back to SQL Interview Prep