SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

DROP TABLE IF EXISTS #TestTable;
GO

CREATE TABLE #TestTable(
             CustomerID INT, 
             LoginName  VARCHAR(20), 
             Year       VARCHAR(10));

INSERT INTO #TestTable
     VALUES ('1', 'Den', '1990'), 
            ('2', 'Sarah', '1990'), 
            ('3', 'Anton', '1977'), 
            ('4', 'James', '1965'), 
            ('5', 'Den', '1994'), 
            ('6', 'Cristian', '1983'), 
            ('7', 'Sarah', '1997'), 
            ('8', 'Mario', '1986'), 
            ('9', 'Mario', '1983')
        --,('10', 'Den', '1983') -- для проверки ))

;WITH cte 
    AS (SELECT a.CustomerID, 
               a.LoginName, 
               a.Year, 
               MIN(b.Year) AS x
          FROM #TestTable AS a
               CROSS JOIN #TestTable AS b
         WHERE a.LoginName = b.LoginName OR a.Year = b.Year
         GROUP BY a.CustomerID, 
                  a.LoginName, 
                  a.Year),
    orderedgroup -- Только для того, чтобы номер группы был именно в том порядке как в задании
    AS (SELECT CustomerID, 
               LoginName, 
               Year, 
               MIN(CustomerID) OVER(PARTITION BY x) gr
          FROM cte)

SELECT CustomerID, 
       LoginName, 
       Year, 
       DENSE_RANK() OVER(ORDER BY gr) gr
FROM orderedgroup
ORDER BY gr, CustomerID

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

DROP TABLE IF EXISTS #TestTable;
GO

CREATE TABLE #TestTable(
             CustomerID INT, 
             LoginName  VARCHAR(20), 
             Year       VARCHAR(10)
);

INSERT INTO #TestTable
     VALUES ('1', 'Den', '1990'), 
            ('2', 'Sarah', '1990'), 
            ('3', 'Anton', '1977'), 
            ('4', 'James', '1965'), 
            ('5', 'Den', '1994'), 
            ('6', 'Cristian', '1983'), 
            ('7', 'Sarah', '1997'), 
            ('8', 'Mario', '1986'), 
            ('9', 'Mario', '1983')
            ,('10', 'Den', '1983') -- для проверки ))

;WITH cte
     AS (SELECT a.CustomerID, 
                a.LoginName, 
                a.Year, 
                MIN(b.Year) AS mn, 
                MAX(b.Year) AS mx
           FROM #TestTable AS a
                CROSS JOIN #TestTable AS b
          WHERE a.LoginName = b.LoginName
                OR a.Year = b.Year
          GROUP BY a.CustomerID, 
                   a.LoginName, 
                   a.Year),
    grp AS (
     SELECT CustomerID, 
            LoginName, 
            Year,
            CASE
                WHEN LAG(mx) OVER(ORDER BY year, mn, CustomerID) BETWEEN mn AND mx
                     OR LAG(mn) OVER(ORDER BY year, mn, CustomerID) BETWEEN mn AND mx 
                THEN IIF(LAG(mn) OVER(ORDER BY year, mn, CustomerID) < mn, LAG(mn) OVER(ORDER BY year, mn, CustomerID), mn)
                ELSE mn
            END AS gr
       FROM cte
       )

       SELECT CustomerID, 
              LoginName, 
              Year,
              DENSE_RANK() OVER(ORDER BY gr) grp
         FROM grp
         ORDER BY grp, CustomerID