How to determine the most common intervals between the numbers within a draw

Transact-SQL
1

Hello,

I collected all lottery data, basically I have DrawNumber, DrawDate, Ball1, Ball2, Ball3, Ball4, Ball5 and Ball6. @Jason_A_Long helped me to retrieve most/least frequent numbers and the gaps of numbers each draw date. I would like to know if there is a way to determine most common intervals between numbers within a draw?

Here is the sample query of the gaps:

   SELECT 
	x.DrawDate,
	x.Has5,
	DrawlsBetween = x.Has5 - LAG(x.Has5, 1, 0) OVER (ORDER BY x.DrawDate) -1
FROM 
	(
	SELECT 
		wn.DrawDate,
		Has5 = CASE WHEN 5 IN (wn.[Ball1], wn.[Ball2], wn.[Ball3], wn.[Ball4], wn.[Ball5], wn.[Ball6]) THEN ROW_NUMBER() OVER (ORDER BY wn.DrawDate) END
	FROM 
		[LuckyNumbers].[dbo].[SuperLoto] wn
	) x
WHERE 
	x.Has5 IS NOT NULL;
2

Is this what you're looking for?

--=======================================================================
-- some test data...IF OBJECT_ID('tempdb..#WinningNumbers', 'U') IS NOT NULL 
DROP TABLE #WinningNumbers;

CREATE TABLE #WinningNumbers (
	DrawDate DATE NOT NULL,
	Num_1 INT NOT NULL,
	Num_2 INT NOT NULL,
	Num_3 INT NOT NULL,
	Num_4 INT NOT NULL,
	Num_5 INT NOT NULL,
	Num_6 INT NOT NULL
	);

WITH 
	cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)), 
	cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
	cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
	cte_Tally (n) AS (
		SELECT TOP 1000 
			ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
		FROM
			cte_n3 a CROSS JOIN cte_n3 b
		),
	cte_GetNumbers AS (
		SELECT 
			t.n,
			Position = DENSE_RANK() OVER (PARTITION BY t.n ORDER BY n.Number),
			n.Number
		FROM
			cte_Tally t
			CROSS APPLY ( VALUES -- generate 10 numbers to increase the likelyhood of getting 6 unique numbers.
								(ABS(CHECKSUM(NEWID()) % 45) + 1),
								(ABS(CHECKSUM(NEWID()) % 45) + 1),
								(ABS(CHECKSUM(NEWID()) % 45) + 1),
								(ABS(CHECKSUM(NEWID()) % 45) + 1),
								(ABS(CHECKSUM(NEWID()) % 45) + 1),
								(ABS(CHECKSUM(NEWID()) % 45) + 1),
								(ABS(CHECKSUM(NEWID()) % 45) + 1),
								(ABS(CHECKSUM(NEWID()) % 45) + 1),
								(ABS(CHECKSUM(NEWID()) % 45) + 1),
								(ABS(CHECKSUM(NEWID()) % 45) + 1)
							) n (Number)
		)
INSERT #WinningNumbers (DrawDate, Num_1, Num_2, Num_3, Num_4, Num_5, Num_6)
SELECT 
	DrawDate = DATEADD(wk, gn.n, '2000-01-03'),
	Num_1 = MAX(CASE WHEN gn.Position = 1 THEN gn.Number END),
	Num_2 = MAX(CASE WHEN gn.Position = 2 THEN gn.Number END),
	Num_3 = MAX(CASE WHEN gn.Position = 3 THEN gn.Number END),
	Num_4 = MAX(CASE WHEN gn.Position = 4 THEN gn.Number END),
	Num_5 = MAX(CASE WHEN gn.Position = 5 THEN gn.Number END),
	Num_6 = MAX(CASE WHEN gn.Position = 6 THEN gn.Number END)
FROM
	cte_GetNumbers gn
GROUP BY 
	gn.n;

-- SELECT * FROM #WinningNumbers wn;
--=======================================================================
-- the solution...

WITH 
	cte_WeeksGap AS (
		SELECT 
			upv.Num,
			WeeksGap = DATEDIFF(wk, LAG(wn.DrawDate, 1) OVER (PARTITION BY upv.Num ORDER BY wn.DrawDate), wn.DrawDate)
		FROM
			#WinningNumbers wn
			CROSS APPLY ( VALUES (wn.Num_1), (wn.Num_2), (wn.Num_3), (wn.Num_4), (wn.Num_5), (wn.Num_6) ) upv (Num)
		)
		SELECT 
			wg.Num, 
			AvgWeeksGap = AVG(wg.WeeksGap)
		FROM
			cte_WeeksGap wg
		GROUP BY 
			wg.Num;
3

I will try and let you know @Jason_A_Long