SQL pattern · P19 · 8 problems

Gaps, islands and streaks

Graded problems on gaps, islands and streaks, from easy to hard.

8 problems · about 1 h 30 min in all
Start with problem 01Flag each login row as consecutive or not · 6 min
Learn it first · P19The streak that secretly counts twiceTwo events on one day inflate an active-day streak unless you fix the grain first.Read the lesson →

Easy

1 problem · about 6 min
01Flag each login row as consecutive or not6 min →

Medium

3 problems · about 30 min
02Gaps over 5 days between consecutive orders8 min →03Number of distinct login streaks per user8 min →04Longest gap between a user's logins14 min →

Hard

4 problems · about 52 min
05Consecutive-day login streaks15 min →06Longest consecutive-day login streak13 min →07Tolerant streaks — 1-day gap still counts as continuous12 min →08Longest streak of consecutive months with revenue > $1,50012 min →
Up next · P20

The pivot that duplicates a customer

14 practice problems. An extra GROUP BY column changes the grain, so one customer becomes several rows.

Practise P20Read P20