Finding the largest continuous set (with tolerance) - is there a better way?

Transact-SQL
1

The following query works but I'd like to know if there is a better way to write it.

Using what I've learned from this post I've expanded on the query I have.

Every row in the temporary table is a calendar day where the sales of that day were high enough to call it "TopSale" - either 1 or 0. The goal is to identify streaks of the most continuous days with top sales. The exception is that there can be up to (3) days that are not TopSale & still be considered part of the streak. On the 4th consecutive non-TopSale day, the streak is broken & it is part of a new/different streak.

I've used multiple CTEs to LAG previous values to get a running total. It is fine for this use case, but I wonder if there was a more dynamic way to go about this.

CREATE TABLE #seasonalsales ( [Date] date, [TopSale] INT ) ;
INSERT INTO #seasonalsales ( [Date], [TopSale] ) VALUES 
	('2022-01-01', '1')
	,('2022-01-02', '0')
	,('2022-01-03', '0')
	,('2022-01-04', '0')
	,('2022-01-05', '0')
	,('2022-01-06', '0')
	,('2022-01-07', '0')
	,('2022-01-08', '0')
	,('2022-01-09', '0')
	,('2022-01-10', '0')
	,('2022-01-11', '0')
	,('2022-01-12', '0')
	,('2022-01-13', '0')
	,('2022-01-14', '0')
	,('2022-01-15', '0')
	,('2022-01-16', '0')
	,('2022-01-17', '0')
	,('2022-01-18', '0')
	,('2022-01-19', '0')
	,('2022-01-20', '0')
	,('2022-01-21', '0')
	,('2022-01-22', '0')
	,('2022-01-23', '0')
	,('2022-01-24', '0')
	,('2022-01-25', '0')
	,('2022-01-26', '0')
	,('2022-01-27', '0')
	,('2022-01-28', '0')
	,('2022-01-29', '0')
	,('2022-01-30', '0')
	,('2022-01-31', '0')
	,('2022-02-01', '0')
	,('2022-02-02', '0')
	,('2022-02-03', '0')
	,('2022-02-04', '0')
	,('2022-02-05', '0')
	,('2022-02-06', '0')
	,('2022-02-07', '0')
	,('2022-02-08', '0')
	,('2022-02-09', '0')
	,('2022-02-10', '0')
	,('2022-02-11', '0')
	,('2022-02-12', '0')
	,('2022-02-13', '0')
	,('2022-02-14', '0')
	,('2022-02-15', '0')
	,('2022-02-16', '0')
	,('2022-02-17', '0')
	,('2022-02-18', '0')
	,('2022-02-19', '0')
	,('2022-02-20', '0')
	,('2022-02-21', '0')
	,('2022-02-22', '0')
	,('2022-02-23', '0')
	,('2022-02-25', '0')
	,('2022-02-26', '0')
	,('2022-02-27', '0')
	,('2022-02-28', '0')
	,('2022-03-01', '0')
	,('2022-03-02', '0')
	,('2022-03-03', '0')
	,('2022-03-04', '0')
	,('2022-03-06', '0')
	,('2022-03-08', '0')
	,('2022-03-09', '0')
	,('2022-03-10', '0')
	,('2022-03-12', '0')
	,('2022-03-13', '0')
	,('2022-03-14', '0')
	,('2022-03-15', '0')
	,('2022-03-16', '0')
	,('2022-03-18', '0')
	,('2022-03-19', '0')
	,('2022-03-20', '0')
	,('2022-03-21', '0')
	,('2022-03-22', '0')
	,('2022-03-23', '0')
	,('2022-03-24', '0')
	,('2022-03-25', '0')
	,('2022-03-26', '0')
	,('2022-03-27', '0')
	,('2022-03-30', '0')
	,('2022-03-31', '0')
	,('2022-04-01', '0')
	,('2022-04-02', '0')
	,('2022-04-03', '0')
	,('2022-04-04', '0')
	,('2022-04-05', '0')
	,('2022-04-07', '0')
	,('2022-04-08', '0')
	,('2022-04-09', '0')
	,('2022-04-10', '0')
	,('2022-04-11', '0')
	,('2022-04-17', '0')
	,('2022-04-20', '0')
	,('2022-04-22', '0')
	,('2022-04-23', '0')
	,('2022-04-24', '0')
	,('2022-04-25', '0')
	,('2022-04-27', '0')
	,('2022-04-29', '0')
	,('2022-04-30', '0')
	,('2022-05-01', '0')
	,('2022-05-02', '0')
	,('2022-05-05', '0')
	,('2022-05-07', '0')
	,('2022-05-08', '0')
	,('2022-05-09', '0')
	,('2022-05-11', '0')
	,('2022-05-14', '0')
	,('2022-05-15', '0')
	,('2022-05-16', '0')
	,('2022-05-17', '0')
	,('2022-05-20', '0')
	,('2022-05-22', '0')
	,('2022-05-23', '0')
	,('2022-05-26', '0')
	,('2022-05-27', '0')
	,('2022-05-29', '0')
	,('2022-05-30', '0')
	,('2022-05-31', '0')
	,('2022-06-02', '0')
	,('2022-06-05', '0')
	,('2022-06-10', '0')
	,('2022-06-11', '0')
	,('2022-06-13', '0')
	,('2022-06-14', '0')
	,('2022-06-15', '0')
	,('2022-06-16', '0')
	,('2022-06-17', '0')
	,('2022-06-18', '0')
	,('2022-06-21', '0')
	,('2022-06-23', '0')
	,('2022-06-25', '0')
	,('2022-06-27', '0')
	,('2022-06-28', '0')
	,('2022-06-29', '0')
	,('2022-07-01', '0')
	,('2022-07-03', '0')
	,('2022-07-04', '0')
	,('2022-07-05', '0')
	,('2022-07-06', '0')
	,('2022-07-07', '0')
	,('2022-07-08', '0')
	,('2022-07-09', '0')
	,('2022-07-10', '0')
	,('2022-07-12', '0')
	,('2022-07-13', '0')
	,('2022-07-15', '0')
	,('2022-07-16', '0')
	,('2022-07-17', '0')
	,('2022-07-20', '1')
	,('2022-07-21', '1')
	,('2022-07-22', '0')
	,('2022-07-23', '0')
	,('2022-07-24', '0')
	,('2022-07-25', '0')
	,('2022-07-27', '0')
	,('2022-07-28', '0')
	,('2022-07-29', '0')
	,('2022-07-30', '0')
	,('2022-07-31', '0')
	,('2022-08-01', '0')
	,('2022-08-02', '0')
	,('2022-08-03', '0')
	,('2022-08-04', '0')
	,('2022-08-05', '0')
	,('2022-08-06', '0')
	,('2022-08-07', '0')
	,('2022-08-08', '0')
	,('2022-08-09', '0')
	,('2022-08-10', '0')
	,('2022-08-11', '0')
	,('2022-08-12', '0')
	,('2022-08-13', '0')
	,('2022-08-14', '0')
	,('2022-08-15', '0')
	,('2022-08-16', '0')
	,('2022-08-17', '0')
	,('2022-08-18', '1')
	,('2022-08-19', '0')
	,('2022-08-20', '0')
	,('2022-08-21', '0')
	,('2022-08-22', '0')
	,('2022-08-23', '0')
	,('2022-08-24', '0')
	,('2022-08-25', '0')
	,('2022-08-26', '0')
	,('2022-08-27', '0')
	,('2022-08-28', '0')
	,('2022-08-29', '0')
	,('2022-08-30', '0')
	,('2022-08-31', '0')
	,('2022-09-01', '1')
	,('2022-09-02', '1')
	,('2022-09-03', '1')
	,('2022-09-04', '1')
	,('2022-09-05', '1')
	,('2022-09-06', '1')
	,('2022-09-07', '1')
	,('2022-09-08', '1')
	,('2022-09-09', '1')
	,('2022-09-10', '0')
	,('2022-09-11', '0')
	,('2022-09-12', '0')
	,('2022-09-13', '0')
	,('2022-09-14', '1')
	,('2022-09-15', '1')
	,('2022-09-16', '1')
	,('2022-09-17', '1')
	,('2022-09-18', '1')
	,('2022-09-19', '1')
	,('2022-09-20', '1')
	,('2022-09-21', '1')
	,('2022-09-22', '1')
	,('2022-09-23', '1')
	,('2022-09-24', '1')
	,('2022-09-25', '1')
	,('2022-09-26', '1')
	,('2022-09-27', '1')
	,('2022-09-28', '1')
	,('2022-09-29', '1')
	,('2022-09-30', '1')
	,('2022-10-01', '1')
	,('2022-10-02', '1')
;

WITH season_metric_2 AS ( 
		SELECT 
			[met].[Date]
			, ISNULL( [met].[TopSale], 0 ) AS [TopSale] 
			, ISNULL ( LAG( [TopSale] )  OVER ( ORDER BY [met].[Date] ) + [TopSale] , 0 ) 	AS [TopSale_2]
		FROM #seasonalsales met
	)
	, season_metric_3 AS ( 
		SELECT *
			, ISNULL ( LAG( [TopSale_2] )  OVER ( ORDER BY [Date] ) + [TopSale] , 0 ) AS [TopSale_3]
		FROM season_metric_2
	)
	, season_metric_4 AS ( 
		SELECT *
			, ISNULL ( LAG( [TopSale_3] )  OVER ( ORDER BY [Date] ) + [TopSale] , 0 ) AS [TopSale_4]
		FROM season_metric_3
	)
	, pre_final AS ( 
		SELECT
			[met].[Date]
			, [met].[TopSale]
			, [met].[TopSale_2]
			, [met].[TopSale_3]
			, [met].[TopSale_4]
			, CASE WHEN [met].[TopSale_4] > 0 THEN 1 ELSE 0 END AS [GenearallyInSeason]
		FROM [season_metric_4] met
		WHERE 1=1
		)
	, final as ( 
		SELECT 
			*
			, ROW_NUMBER() OVER ( ORDER BY [Date] )
			- 
			ROW_NUMBER() OVER( PARTITION BY [GenearallyInSeason] ORDER BY [Date] )
			AS [InSeasonStreak]
		FROM pre_final
		)
SELECT
	[InSeasonStreak]
	, COUNT(*)  AS [Days]
	, MIN( [Date] )	AS [FirstDay]
	, MAX( [Date] ) AS [LastDay]
FROM FINAL	
WHERE 1=1
	AND [GenearallyInSeason] = 1
GROUP BY [GenearallyInSeason], [InSeasonStreak]
ORDER BY COUNT(*) DESC
2

Rather that trying to go through your code for the "InSeasonStreak", can you describe what it's supposed to be, please? The rest is obvious.

EDIT.. Never mind... you've already described it. Thanks.

3

Maybe something like:

WITH NewTopSale
AS
(
	SELECT [Date]
		,CASE
			WHEN TopSale = 1
			THEN 1
			WHEN SUM(TopSale) OVER (ORDER BY [Date] ROWS BETWEEN 3 PRECEDING and CURRENT ROW) > 0
			THEN 1
			ELSE 0
		END AS TopSale
	FROM #seasonalsales
)
,Breaks
AS
(
	SELECT [Date], TopSale
		,CASE
			WHEN TopSale = LAG(TopSale) OVER (ORDER BY [Date])
			THEN 0
			ELSE 1
		END AS IsBreak
	FROM  NewTopSale
)
,Streaks
AS
(
	SELECT [Date], TopSale
		, SUM(IsBreak) OVER (ORDER BY [Date]) AS Streak
	FROM Breaks
    WHERE TopSale = 1
)
SELECT Streak
	,COUNT(*) AS [Days]
	,MIN([Date]) AS FirstDay
	,MAX([Date]) AS LastDay
FROM Streaks
GROUP BY Streak
ORDER BY [Days] DESC;
4

Thank you. This is much better than what I had