CUSTOMER( 
    ID int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max) NOT NULL
)

[ORDER](
    ID int IDENTITY(1,1) NOT NULL,
    CUSTOMER_ID int NOT NULL,
    [Date] date NOT NULL
)

SELECT TOP 1 CUSTOMER_ID
FROM [ORDER]
....
ORDER BY
....

/*возвращает @N постоянных клиентов за период с @StartDate по @EndDate*/
ALTER PROCEDURE [dbo].[usp_RegularClientListN]
	@N int = 10, 
	@StartDate date = NULL, 
	@EndDate date = NULL
AS
BEGIN
SET NOCOUNT ON;

IF @StartDate IS NULL SET @StartDate = (SELECT MIN([Date]) FROM [ORDER])
IF @EndDate IS NULL SET @EndDate = (SELECT MAX([Date]) FROM [ORDER])

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t

SELECT
	[Name],
	regularity
INTO #t
FROM
	(
	SELECT
		CLIENT_ID,
		
		-- VARP (DISTINCT, some_value) - дисперсия заполнения всех значений в some_value. 
		-- Предполагаем, что чем больше дисперсия даты заказа клиента, тем постояннее клиент.
		VARP( DISTINCT 
			-- преобразование типа данных date в int не разрешено, поэтому сначала преобразовываем date в datetime
			CAST(CAST([DATE] AS datetime) AS int) 
		) AS regularity
		
	FROM 
		[ORDER]
	WHERE
		[DATE] Between @StartDate AND @EndDate 
	GROUP BY
		CLIENT_ID
	ORDER BY
		regularity DESC
	) AS RegularClient
	
	JOIN CLIENT ON CLIENT.ID = RegularClient.CLIENT_ID

EXEC ('
SELECT TOP ' + @N + ' * FROM #t
')

DROP TABLE #t
END