Longest Daily Selling Streak per Seller — SQL Problem
Seller badges reward consistency: the badge tier depends on a seller's longest streak of consecutive calendar days with at least one sale.
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #120
Problem statement
Seller badges reward consistency: the badge tier depends on a seller's longest streak of consecutive calendar days with at least one sale. Several sales on one day count as one day; a day with no sale ends the streak.
For every seller with at least one sale, return seller_id, longest_streak (in days) and streak_start — the first day of that streak, or of the earliest one if several streaks share the longest length. Order by longest_streak descending, then seller_id.
Tables
Table: seller_sales
| Column | Type |
|---|---|
| sale_id | int |
| seller_id | int |
| sold_on | date |
| amount | int |
Primary key: sale_id.
One row per sale; a seller can have many sales on the same day.
Examples
Example 1
seller_sales
| sale_id | seller_id | sold_on | amount |
|---|---|---|---|
| 1 | 10 | 2024-12-29 | 499 |
| 2 | 10 | 2024-12-30 | 1299 |
| 3 | 10 | 2024-12-30 | 350 |
| 4 | 10 | 2024-12-31 | 899 |
| 5 | 10 | 2025-01-01 | 650 |
| 6 | 10 | 2025-01-03 | 199 |
| 7 | 20 | 2025-01-01 | 2499 |
| 8 | 20 | 2025-01-02 | 799 |
| 9 | 20 | 2025-01-05 | 1500 |
| 10 | 20 | 2025-01-06 | 300 |
| 11 | 30 | 2025-01-04 | 999 |
Output
| seller_id | longest_streak | streak_start |
|---|---|---|
| 10 | 4 | 2024-12-29 |
| 20 | 2 | 2025-01-01 |
| 30 | 1 | 2025-01-04 |
How to solve Longest Daily Selling Streak per Seller
Start by collapsing the sales into distinct selling days per seller: seller 10 sold twice on 30 December, and counting both rows would make a three-day run look like four.
Then the gaps-and-islands trick on dates. Number each seller's days 1, 2, 3, … with ROW_NUMBER(). Within a run of consecutive dates, the date and the row number both advance by one each step, so DATE_SUB(sold_on, INTERVAL rn DAY) is the same date for every day in the run; any missing day shifts it. Grouping by that key gives each streak's first day (MIN) and length (COUNT). Date arithmetic handles month and year boundaries — seller 10's streak runs 29 December to 1 January, four days.
Finally keep one streak per seller: ROW_NUMBER() ordered by length descending, then start ascending, so of two equally long streaks the earlier wins (seller 20 has two two-day streaks; 1 January is reported). The alternative finds streak starts (no sale the day before) and ends (no sale the day after) with NOT EXISTS and pairs each start with the nearest end — clearer to read, but it probes per day instead of sorting once.
Reference solution (MySQL)
WITH days AS (
SELECT DISTINCT seller_id, sold_on FROM seller_sales
), numbered AS (
SELECT seller_id, sold_on, ROW_NUMBER() OVER (PARTITION BY seller_id ORDER BY sold_on) AS rn
FROM days
), islands AS (
SELECT seller_id, MIN(sold_on) AS streak_start, COUNT(*) AS streak_len
FROM numbered
GROUP BY seller_id, DATE_SUB(sold_on, INTERVAL rn DAY)
), best AS (
SELECT seller_id, streak_start, streak_len,
ROW_NUMBER() OVER (PARTITION BY seller_id ORDER BY streak_len DESC, streak_start) AS pos
FROM islands
)
SELECT seller_id, streak_len AS longest_streak, streak_start
FROM best
WHERE pos = 1
ORDER BY longest_streak DESC, seller_idAnother way
WITH days AS (SELECT DISTINCT seller_id, sold_on FROM seller_sales),
starts AS (
SELECT d.seller_id, d.sold_on AS streak_start FROM days d
WHERE NOT EXISTS (SELECT 1 FROM days p WHERE p.seller_id = d.seller_id AND p.sold_on = DATE_SUB(d.sold_on, INTERVAL 1 DAY))
),
ends AS (
SELECT d.seller_id, d.sold_on AS streak_end FROM days d
WHERE NOT EXISTS (SELECT 1 FROM days n WHERE n.seller_id = d.seller_id AND n.sold_on = DATE_ADD(d.sold_on, INTERVAL 1 DAY))
),
streaks AS (
SELECT s.seller_id, s.streak_start,
DATEDIFF((SELECT MIN(e.streak_end) FROM ends e WHERE e.seller_id = s.seller_id AND e.streak_end >= s.streak_start), s.streak_start) + 1 AS streak_len
FROM starts s
)
SELECT seller_id, streak_len AS longest_streak, MIN(streak_start) AS streak_start
FROM streaks t
WHERE streak_len = (SELECT MAX(streak_len) FROM streaks m WHERE m.seller_id = t.seller_id)
GROUP BY seller_id, streak_len
ORDER BY longest_streak DESC, seller_id← Top Three Sellers per Category in Q4, With Ties · The Redemption That Exhausted Each Coupon Budget →