Consecutive dates
Growth is chasing a hunch: “Which customers ordered on three days in a row? That kind of streak usually means someone’s really engaged — I want to know who to feature in the loyalty program.”
I need to compare each order's date to the customer's previous order's date. That's LAG, from the window functions chapter — 'what happened before this row.'
Exactly the right instinct. Let's see what it actually shows us before we assume the answer.
“Are these rows part of the same run” is usually called gaps and islands: islands are the runs of connected rows, gaps are the breaks between them. The tool for spotting a break is the one you already know — line each row up next to whatever came before it in the same group, using LAG partitioned by customer.
Line each row up against the one before it. Where they disagree, that’s a gap.
SELECT
customer_id,
placed_at,
LAG(placed_at) OVER (
PARTITION BY customer_id
ORDER BY placed_at
) AS previous_order
FROM orders
ORDER BY customer_id, placed_at;Running…
22 orders, lined up next to whatever the same customer ordered right before it (or NULL, for their first order ever). Scan it: nobody’s placed_at and previous_order are a single calendar day apart, anywhere in the table. No three-day streaks. Not even a two-day one.
Harry: “So the whole hunch is just wrong?”
Hermione: “The literal hunch, yes — nobody at NOVA has ever ordered two days running, so three is never going to happen either. But look again. A couple of rows are close — not consecutive days, but the same month.”
Computing an exact day-count gap (“14 days apart”) means subtracting one date from another, and that arithmetic isn’t reliable across every SQL engine — it’s worth sidestepping. A same-month check doesn’t need subtraction at all: SUBSTR(CAST(placed_at AS TEXT), 1, 7) takes just the YYYY-MM piece of an ISO date as plain text, so two dates are “the same month” exactly when those two substrings are equal — an ordinary string comparison, portable everywhere.
WITH sequenced AS (
SELECT
customer_id,
placed_at,
LAG(placed_at) OVER (
PARTITION BY customer_id
ORDER BY placed_at
) AS previous_order
FROM orders
)
SELECT c.name, s.placed_at, s.previous_order
FROM sequenced s
JOIN customers c ON c.id = s.customer_id
WHERE s.previous_order IS NOT NULL
AND SUBSTR(CAST(s.placed_at AS TEXT), 1, 7) = SUBSTR(CAST(s.previous_order AS TEXT), 1, 7)
ORDER BY c.name;| name | placed_at | previous_order |
|---|---|---|
| Emma Wilson | 2023-05-15 | 2023-05-01 |
| Julia Souza | 2023-08-15 | 2023-08-01 |
Two customers who came back within the same calendar month: Emma, twice in May, and Julia, twice in August. Not the streak growth first imagined, but a real, checkable version of the same underlying question — “did this customer come back soon after their last order” — built entirely from LAG and a string comparison.
Running…
Worth one more look at the very first table on this page: scan it by eye and Alice’s gap between March 14th and May 22nd is the longest stretch of silence between any two orders from the same customer, anywhere in the data. SQL got the 22 rows lined up in order, next to their neighbor — from there, actually reading it is completely reasonable. Not every last step has to happen in the query.
Try it: What if you swap LAG for LEAD, keeping everything else the same?
Run your query to see results here.
Same information, read from the other direction. LAG looks backward at the previous row in the partition; LEAD looks forward at the next one. Notice where the NULLs move: under LAG, every customer’s first order has a NULL previous order. Under LEAD, it’s every customer’s last order that has a NULL next order. Same partition, same ordering, opposite direction.
Retention wants it framed differently: forget which specific orders were adjacent — just tell them which customers placed more than one order in the same calendar month, and how many orders that was. Return customer name, the month, and the count.
Hint 1
You don't actually need to know which two orders were next to each other for this version — just whether a customer+month combination shows up more than once. Does that sound like a pattern from a couple of lessons back?
Hint 2
Group by customer and by the year-month piece of placed_at — SUBSTR(CAST(placed_at AS TEXT), 1, 7) gives you strings like '2023-05'.
Hint 3
SELECT c.name, SUBSTR(CAST(o.placed_at AS TEXT), 1, 7) AS order_month, COUNT(*) AS n FROM orders o JOIN customers c ON c.id = o.customer_id GROUP BY c.id, c.name, SUBSTR(CAST(o.placed_at AS TEXT), 1, 7) HAVING COUNT(*) > 1;
Solution
Running…
Same two customers, same two months — Emma in May, Julia in August — but this version didn’t need LAG at all. It’s just duplicate detection’s GROUP BY ... HAVING COUNT(*) > 1, pointed at a (customer, month) pair instead of an email address. That’s the real lesson underneath “gaps and islands”: LAG tells you exactly which rows are adjacent, while GROUP BY only tells you that something repeated. Pick whichever one actually answers the question being asked.
To reason about a sequence, line each row up against its neighbor with LAG (backward) or LEAD (forward), partitioned by whatever group the sequence belongs to. Exact date-difference arithmetic isn’t portable across SQL engines — when you can, compare date strings directly, or slice out just the piece you need (like the YYYY-MM prefix) with SUBSTR instead of subtracting.