Longest Daily Selling Streak per Seller

Hard
120.

Longest Daily Selling Streak per Seller

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

ColumnType
sale_idint
seller_idint
sold_ondate
amountint

Primary key: sale_id.

One row per sale; a seller can have many sales on the same day.

Example

The rows must come back in the order shown.

seller_sales

sale_idseller_idsold_onamount
1102024-12-29499
2102024-12-301299
3102024-12-30350
4102024-12-31899
5102025-01-01650
6102025-01-03199
7202025-01-012499
8202025-01-02799
9202025-01-051500
10202025-01-06300
1 more row

Output

seller_idlongest_streakstreak_start
1042024-12-29
2022025-01-01
3012025-01-04
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
sale_idint
seller_idint
sold_ondate
amountint

Primary key: sale_id.

One row per sale; a seller can have many sales on the same day.

Examples

Example 1

seller_sales

sale_idseller_idsold_onamount
1102024-12-29499
2102024-12-301299
3102024-12-30350
4102024-12-31899
5102025-01-01650
6102025-01-03199
7202025-01-012499
8202025-01-02799
9202025-01-051500
10202025-01-06300
11302025-01-04999

Output

seller_idlongest_streakstreak_start
1042024-12-29
2022025-01-01
3012025-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_id

Another 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 →