Build1 publisher2 min readPublished Updated
The LAG-and-SUM streak query keeps its gap rule in one editable CASE line
SQL makes you assemble streaks by hand, so a dev.to walkthrough builds three from ROW_NUMBER, LAG and a running SUM. What separates them is where the definition of a gap gets written, and how hard it is to change later.
The Engineer · Build desk
What happened
- A dev.to walkthrough of the gaps and islands problem starts from the fact that SQL has no built-in FIND_STREAKS() function, so consecutive runs have to be assembled out of window functions.
- Its sample table of login dates has user 1 logging in three days running, skipping the fourth and returning for two more, so a correct answer is two islands of three days and two days.
- The first construction numbers rows with ROW_NUMBER and groups on login_date minus rn days, and the walkthrough shows the five rows collapsing to Jan 1 three times and Jan 2 twice.
- The second flags each gap with a CASE expression and runs a cumulative SUM over that flag to number the islands, then aggregates by user and streak id.
- The third approach pairs boundary rows, matching the Nth streak start with the Nth streak end, and the post's SQL listing for it breaks off inside a CASE expression.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- constraint Teams whose streaks live on timestamps or irregular intervals are ruled out of the row-number subtraction by the spacing assumption, before any question of speed is raised.
- decision Picking the flag construction puts the definition of a gap in one predicate, so a team that later redefines a streak as an hour, or as a run of one status code, edits a single line.
- cost The flag version computes a second window over the same partition, so its extra work is paid on every execution of the query.
Read the CASE expression in the second construction closely. It returns 0 when `login_date - LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) = 1`, and 1 in every other case [9]. On a partition's first row, LAG has no previous row to read, the predicate is not true, and the ELSE branch fires, so that row gets a 1 [3]. The running SUM opens each user at island 1 [10]. A COALESCE is redundant here.
The day spacing is written twice in the post, in two notations. The first query multiplies the row number by `INTERVAL '1 day'` and subtracts it from the date [6]. The second compares a date difference to the integer 1 [9]. Moving either query to a different engine means checking what date subtraction returns there, and the edit lands in a different place in each one.
The post's case for the flag version: the gap test is a single CASE expression you fully control. Change the condition to "is the gap more than 1 hour", or to "did the status code change", and the rest of the query is untouched [11]. "That's what makes this the one worth reaching for by default," the post says [12]. I would make the same call, for the reason the post gives about the first query. Subtracting an evenly spaced counter only works cleanly on whole days or plain integers, and on timestamps or irregular intervals it stops producing clean matches [8]. In the first query, that assumption sits inside the GROUP BY key [6], so changing it changes what a group is.
The third construction drops the grouping and the aggregation and works from boundary rows instead [13]. Matching by ordinal means the starts and the ends each need their own numbering, which is a further window pass over the boundary set [4].
Cost is where the evidence stops. The post publishes no execution plans and no timings [16]. Both OVER specifications in the flag query are identical, `PARTITION BY user_id ORDER BY login_date` [2]. An engine that recognises that sorts each partition once and computes both windows from that sort. An engine that does not sorts twice. The sample is too small to settle it: user 1 contributes five rows, three plus two [1], and separating two plans takes more than that. Before "this construction is cheaper" transfers to your table, you need the plan on your partition sizes, with your index on `(user_id, login_date)`, at the row count where the sort spills to disk.
What to watch
- The full boundary-pairing SQL, once published, would show whether that version needs its own ROW_NUMBER pass over each set of boundary rows.
- EXPLAIN output or timings on a large partition would let the three constructions be ranked by cost instead of by the shape of the gap test.
- A version of the flag predicate written for timestamps would show how much editing the "gap more than 1 hour" swap actually takes.