Take one user of a language-learning app and list the days in November on which she opened it: the 1st, 2nd, 3rd, 5th, 6th, 9th, 10th, 11th and 12th. Looking at the list, you can see three separate runs of consecutive days: 1 to 3, 5 to 6, and 9 to 12. Her longest streak is four days. The missing days, the 4th, 7th and 8th, are the gaps between them.
This shape of problem is known as “gaps and islands”. The islands are unbroken runs; the gaps are the breaks between them. It shows up far beyond app streaks: consecutive months a customer paid an EMI, periods a machine ran without a fault, stretches when a stock stayed above a threshold, nights a hotel room stayed booked, or the date ranges an employee held each designation.
A human spots islands instantly. SQL works on sets, not on lists you scan with your eyes, so you need a trick that gives every row in the same island a common label. Once the label exists, an ordinary GROUP BY finishes the job.
The row-number difference trick
Number the user’s active days in date order with ROW_NUMBER, starting at 1. Then subtract that number, as days, from the date itself.
| Active date | Row number | Date minus row number |
|---|---|---|
| 1 Nov | 1 | 31 Oct |
| 2 Nov | 2 | 31 Oct |
| 3 Nov | 3 | 31 Oct |
| 5 Nov | 4 | 1 Nov |
| 6 Nov | 5 | 1 Nov |
| 9 Nov | 6 | 3 Nov |
| 10 Nov | 7 | 3 Nov |
| 11 Nov | 8 | 3 Nov |
| 12 Nov | 9 | 3 Nov |
Within a run of consecutive days, the date goes up by one each row and the row number also goes up by one, so their difference stays fixed. When a day is skipped, the date jumps by two or more while the row number still goes up by one, so the difference shifts to a new value. The third column is therefore a label for each island. Its actual value means nothing; it only has to be the same within an island and different between islands.
Now group by user and that label. For each group, the minimum date is the streak start, the maximum date is the streak end, and the count of rows is the streak length: 3, 2 and 4 days. To get each user’s longest streak, take the maximum of those lengths per user, or rank the streaks and keep the top one.
The step people skip: deduplicate first
Activity data usually arrives as events, not days. A user who opens the app five times on 2 November has five rows for that date. If you apply the trick directly, those five rows get row numbers 2 to 6 while the date stays the same, the difference drifts, and the island breaks apart into nonsense. Always reduce the data to one row per user per day first, either with DISTINCT or by grouping on user and the date part of the timestamp.
That also forces a decision about time zones. An event at 11:45 pm IST on 2 November is still 1 November in UTC. If your timestamps are stored in UTC and your users are in India, convert before truncating to a date, or streaks will break and merge at the wrong boundaries.
A second approach: flag the breaks
The difference trick depends on the sequence moving in fixed steps of one. When it does not, a more general method works. Use LAG to fetch the previous date for the same user. Where the gap from the previous date is more than one day, or where there is no previous date, mark the row with a 1, meaning “a new island starts here”; otherwise mark it 0. A running sum of those flags, ordered by date, then gives every island a number: 1 for the first, 2 for the second and so on.
This version is easier to adapt. If a streak should survive a single missed day, change the break condition to a gap of more than two days. If you are grouping web events into sessions, the break condition becomes “more than 30 minutes since the previous event”, which is the same idea applied to timestamps. For irregular sequences such as invoice numbers or shift IDs, the condition can be anything you can express about two neighbouring rows.
Finding the gaps instead
Sometimes the gaps are the interesting part: missing invoice numbers that an auditor will ask about, days a sensor sent no readings, months a subscriber skipped. With LEAD, compare each value to the next one in sequence. Where the next value is more than one step ahead, the gap runs from the current value plus one to the next value minus one. For invoice numbers 1001, 1002, 1005 and 1006, that reports a single gap of 1003 to 1004.
Gaps that sit at the edges need separate handling. If you are checking November and the first reading is on the 3rd, the 1st and 2nd are missing too, but there is no earlier row to compare with. Joining to a calendar table, one row per date, is the cleanest way to catch these and to report “days with no activity” directly.
Where streak metrics mislead
Streaks are motivating for users and attractive for product dashboards, but they need care as a business metric. A “current streak” is meaningless for a user whose last activity was months ago, so define whether a streak counts as current only if it includes today or yesterday. Longest-streak averages are dominated by a few devoted users, so a median or a distribution says more than a mean. And a streak definition built on calendar days treats a user who uses the app at 11:55 pm and again at 12:05 am as active on two days, which may or may not match what the product team intends.
Before writing the query, agree on the unit of time, the time zone, what counts as “active”, and how many missed periods break a streak. The SQL is the easy part once those four things are settled.
Written by the RP Strativa Team. Dates and numbers in the examples are illustrative.
Leave a Reply