Solving the Classic SQL "Gaps and Islands" Problem: 3 Modern Approaches

Automated excerpt

ORDER BY ... ) — numbers rows 1, 2, 3... within each group, in the order you specify. Walking through it: rn gives each row a position (1, 2, 3, 4, 5). Subtracting rn days from login_date gives Jan 1, Jan 1, Jan 1, Jan 2, Jan 2 — the first three rows collapse to the same value because there's no gap between them, and the last two collapse to a different shared value because the gap on Jan 4 threw the counter out of sync.

Selected automatically from source text; not independently written or fact-checked. Read the original for full context.