Lessons › SQL › Go further in SQL: Business questions

SQL lesson

What's our longest run of days with a sale?

What's the longest run of days in a row with at least one sale? The trick: number the days 1, 2, 3… in date order. For days in a row, the date minus its number stays the same; after a gap, it jumps. So that difference labels each run. Count the days in each run and keep the longest.

Example

Gaps and islands: days in a row share a gap from their row number

WITH d AS (
  SELECT sale_date,
         sale_date
           - CAST(ROW_NUMBER() OVER (ORDER BY sale_date) AS INT) AS grp
  FROM daily_sales
), runs AS (
  SELECT grp, COUNT(*) AS days
  FROM d
  GROUP BY grp
)
SELECT MAX(days) AS longest_streak
FROM runs

Result:

Step by step

1. The longest run of days in a row.

   WITH d AS (SELECT sale_date, sale_date - CAST(ROW_NUMBER() OVER (ORDER BY sale_date) AS INT) AS grp FROM daily_sales), runs AS (SELECT grp, COUNT(*) AS days FROM d GROUP BY grp) SELECT MAX(days) AS longest_streak FROM runs

2. One row per day with a sale.

   WITH d AS (SELECT sale_date, sale_date - CAST(ROW_NUMBER() OVER (ORDER BY sale_date) AS INT) AS grp FROM daily_sales), runs AS (SELECT grp, COUNT(*) AS days FROM d GROUP BY grp) SELECT MAX(days) AS longest_streak FROM runs

3. Every day counts.

   WITH d AS (SELECT sale_date, sale_date - CAST(ROW_NUMBER() OVER (ORDER BY sale_date) AS INT) AS grp FROM daily_sales), runs AS (SELECT grp, COUNT(*) AS days FROM d GROUP BY grp) SELECT MAX(days) AS longest_streak FROM runs

4. Date minus ROW_NUMBER is the same for every day in a run.

   WITH d AS (SELECT sale_date, sale_date - CAST(ROW_NUMBER() OVER (ORDER BY sale_date) AS INT) AS grp FROM daily_sales), runs AS (SELECT grp, COUNT(*) AS days FROM d GROUP BY grp) SELECT MAX(days) AS longest_streak FROM runs

Practice

Try each one in your head first, then open the answer.

WITH d AS (SELECT sale_date, sale_date - CAST(ROW_NUMBER() OVER (ORDER BY sale_date) AS INT) AS grp FROM daily_sales), runs AS (SELECT grp, COUNT(*) AS days FROM d GROUP BY grp) SELECT MAX(days) AS longest_streak FROM runs

WITH d AS (SELECT sale_date, sale_date - CAST(ROW_NUMBER() OVER (ORDER BY sale_date) AS INT) AS grp FROM daily_sales) SELECT MIN(sale_date) AS start_date, MAX(sale_date) AS end_date, COUNT(*) AS days FROM d GROUP BY grp ORDER BY start_date

SELECT sale_date, ROW_NUMBER() OVER (ORDER BY sale_date) AS day_no FROM daily_sales

WITH d AS (SELECT sale_date - CAST(ROW_NUMBER() OVER (ORDER BY sale_date) AS INT) AS grp FROM daily_sales) SELECT COUNT(DISTINCT grp) AS runs FROM d

WITH d AS (SELECT sale_date, sale_date - CAST(ROW_NUMBER() OVER (ORDER BY sale_date) AS INT) AS grp FROM daily_sales), runs AS (SELECT MIN(sale_date) AS start_date, COUNT(*) AS days FROM d GROUP BY grp) SELECT start_date, days FROM runs ORDER BY days DESC LIMIT 1