SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT *
INTO dbo.Regions
FROM (VALUES(1,NULL,'EU'),
            (2,1,'Germany'),
            (3,1,'France'))V(ID,ParentID,[Name])

SELECT *
INTO dbo.Cities
FROM (VALUES(1,'Berlin',2),
            (2,'Hamburg',2),
            (3,'Paris',3),
            (4,'Nice',3))V(ID,[Name], RegionID)
7 rows affected

WITH rCTE AS(
    SELECT R.ID,
           R.ParentID,
           R.[Name]
    FROM dbo.Regions R
    UNION ALL
    SELECT R.ID,
           R.ParentID,
           C.[Name]
    FROM dbo.Regions R
         JOIN rCTE C ON R.ParentID = C.ID)
SELECT *
FROM rCTE


ID
ParentID
Name


1
null
EU

2
1
Germany

3
1
France

2
1
EU

3
1
EU


WITH rCTE AS(
    SELECT R.ID,
           R.ParentID,
           R.[Name]
    FROM dbo.Regions R
    UNION ALL
    SELECT R.ID,
           R.ParentID,
           C.[Name]
    FROM dbo.Regions R
         JOIN rCTE C ON R.ParentID = C.ID)
SELECT r.[Name],
       COUNT(*) AS CityCount
FROM rCTE r
     JOIN dbo.Cities C ON r.ID = C.RegionID
GROUP BY r.[Name];


Name
CityCount


EU
4

France
2

Germany
2


DROP TABLE dbo.Cities;
DROP TABLE dbo.Regions;