Gaps and islands pattern

The gaps and islands pattern finds consecutive streaks in a sequence of rows - the unbroken stretches are the "islands," and the breaks between them are the "gaps.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Finding consecutive runs (gaps and islands)

SELECT status, last_updated, ROW_NUMBER() OVER (ORDER BY last_updated) - ROW_NUMBER() OVER (PARTITION BY status ORDER BY last_updated) AS grp FROM inventory

Result:

status	last_updated	grp
stocked	2026-01-01	0
out_of_stock	2026-01-02	1
out_of_stock	2026-01-03	1
stocked	2026-01-04	2
out_of_stock	2026-01-05	3

Step by step

AS gives a column a friendlier name in the result.

SELECT status, last_updated, ROW_NUMBER() OVER (ORDER BY last_updated) - ROW_NUMBER() OVER (PARTITION BY status ORDER BY last_updated) AS grp

FROM tells SQL which table to pull the data from.

SELECT status, last_updated, ROW_NUMBER() OVER (ORDER BY last_updated) - ROW_NUMBER() OVER (PARTITION BY status ORDER BY last_updated) AS grp
FROM inventory;

Practice

Try each one in your head first, then open the answer.

1. On orders, label consecutive runs of the same order_id value when ordered by order_date. Return order_id, order_date, and a column called grp that's constant within each run.

SELECT order_id, order_date, ROW_NUMBER() OVER (ORDER BY order_date) - ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY order_date) AS grp FROM orders

2. On store_sales, label consecutive runs of the same sale_id value when ordered by sale_date. Return sale_id, sale_date, and a column called grp that's constant within each run.

SELECT sale_id, sale_date, ROW_NUMBER() OVER (ORDER BY sale_date) - ROW_NUMBER() OVER (PARTITION BY sale_id ORDER BY sale_date) AS grp FROM store_sales

3. On products, label consecutive runs of the same product_id value when ordered by product_id. Return product_id, product_id, and a column called grp that's constant within each run.

SELECT product_id, product_id, ROW_NUMBER() OVER (ORDER BY product_id) - ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY product_id) AS grp FROM products

4. On employees, label consecutive runs of the same employee_id value when ordered by hire_date. Return employee_id, hire_date, and a column called grp that's constant within each run.

SELECT employee_id, hire_date, ROW_NUMBER() OVER (ORDER BY hire_date) - ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY hire_date) AS grp FROM employees

5. On reviews, label consecutive runs of the same review_id value when ordered by review_date. Return review_id, review_date, and a column called grp that's constant within each run.

SELECT review_id, review_date, ROW_NUMBER() OVER (ORDER BY review_date) - ROW_NUMBER() OVER (PARTITION BY review_id ORDER BY review_date) AS grp FROM reviews

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed