Find streaks with gaps and islands
"How many months in a row has this customer paid?" sounds like it needs a loop. It doesn't. The gaps-and-islands trick finds every unbroken run in one query.
The data
create table payments as select * from (values
('acme', date '2026-01-01'), ('acme', date '2026-02-01'), ('acme', date '2026-03-01'),
('acme', date '2026-05-01'), ('acme', date '2026-06-01'),
('globex', date '2026-02-01'), ('globex', date '2026-04-01')
) t(account, paid_month);The query
with numbered as (
select account, paid_month,
date_diff('month', date '2000-01-01', paid_month) as month_number,
row_number() over (partition by account order by paid_month) as rn
from payments
)
select account,
min(paid_month) as streak_start,
max(paid_month) as streak_end,
count(*) as months
from numbered
group by account, month_number - rn
order by account, streak_start;account streak_start streak_end months
acme 2026-01-01 2026-03-01 3
acme 2026-05-01 2026-06-01 2
globex 2026-02-01 2026-02-01 1
globex 2026-04-01 2026-04-01 1Why it works
Within a streak, the month number and the row number both go up by one each row, so their difference stays the same. A skipped month bumps the month number by two while the row number still goes up by one, so the difference changes and a new group starts. Acme's January to March share one value; May and June share another.
Uses
Longest paying streak, customers who churned and came back, consecutive days active, and how long an account has been above a usage threshold. In Snowflake, swap in datediff('month', '2000-01-01', paid_month). The anchor date doesn't matter, as long as it's the same for every row.
One assumption to check first: one row per account per month. If the table can hold two payments in the same month, deduplicate before numbering, or the streaks come out too long.
Every query here ran in DuckDB 1.5.1 and returned the output shown. This note was written with AI assistance.