Busy Streaks at the Book Fair — SQL Hard Problem

Problem statement

The city book fair counts visitors every day it is open; day_no is the fair's day number (day 1 is opening day), and a day the fair stayed closed has no row. A day is busy when its footfall is at least 100; a NULL footfall means the counter failed and the day is not busy.

Return every day that belongs to a streak of three or more consecutive day numbers that are all busy, with columns day_no, visit_date and footfall, ordered by visit_date. A closed day breaks a streak, because its day number is missing.

Tables

Table: FairDay

Primary key: day_no.

One row per day the fair was open. visit_date is opening day plus day_no - 1 days, so day numbers and dates rise together; numbers of closed days are skipped.

Examples

Example 1

FairDay

Output

How to solve Busy Streaks at the Book Fair

First keep only the busy days (footfall >= 100; NULL fails the test, so a failed counter is not busy). What remains is a sorted set of day numbers, and the task is to find islands of consecutive numbers of size three or more.

The gaps-and-islands trick: number the busy days with ROW_NUMBER() OVER (ORDER BY day_no). Inside a run of consecutive days, both day_no and the row number rise by 1 each step, so day_no - ROW_NUMBER() is constant; any gap — a quiet day, a NULL, or a closed day with no row — makes day_no jump while the row number does not, so the difference changes. That difference is a key for the island, and COUNT(*) OVER (PARTITION BY streak_key) gives each day the length of its island. Keep lengths ≥ 3 and sort by date.

The closed-day case is where a naive LAG/LEAD on the busy rows goes wrong: day 6 and day 8 are adjacent rows, but not adjacent days. Comparing the neighbours' day_no values with day_no ± 1 and ± 2 fixes it — a day qualifies if it starts, sits in the middle of, or ends a triple. A triple self join expresses the same three cases with DISTINCT to remove repeats. The island version handles any streak length in one sort; the self join is cubic without indexes.

Reference solution (MySQL)

WITH busy AS (
  SELECT day_no, visit_date, footfall,
         day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS streak_key
  FROM FairDay
  WHERE footfall >= 100
)
SELECT day_no, visit_date, footfall
FROM (
  SELECT day_no, visit_date, footfall, COUNT(*) OVER (PARTITION BY streak_key) AS streak_len
  FROM busy
) s
WHERE streak_len >= 3
ORDER BY visit_date

Another way

SELECT day_no, visit_date, footfall
FROM (
  SELECT day_no, visit_date, footfall,
         LAG(day_no, 2) OVER (ORDER BY day_no) AS back2,
         LAG(day_no, 1) OVER (ORDER BY day_no) AS back1,
         LEAD(day_no, 1) OVER (ORDER BY day_no) AS ahead1,
         LEAD(day_no, 2) OVER (ORDER BY day_no) AS ahead2
  FROM FairDay
  WHERE footfall >= 100
) t
WHERE back2 = day_no - 2
   OR (back1 = day_no - 1 AND ahead1 = day_no + 1)
   OR ahead2 = day_no + 2
ORDER BY visit_date

Another way

SELECT DISTINCT a.day_no, a.visit_date, a.footfall
FROM FairDay a, FairDay b, FairDay c
WHERE a.footfall >= 100 AND b.footfall >= 100 AND c.footfall >= 100
  AND ((b.day_no = a.day_no + 1 AND c.day_no = a.day_no + 2)
    OR (b.day_no = a.day_no - 1 AND c.day_no = a.day_no + 1)
    OR (b.day_no = a.day_no - 2 AND c.day_no = a.day_no - 1))
ORDER BY a.visit_date