SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE friendships (
    id INT PRIMARY KEY,
    person VARCHAR(50),
    friend VARCHAR(50)
);

INSERT INTO friendships (id, person, friend) VALUES
(1, 'person3', 'person9'),
(3, 'person10', 'person4'),
(4, 'person2', 'person1'),
(5, 'person6', 'person7'),
(7, 'person4', 'person10'),
(8, 'person6', 'person7'),
(10, 'person10', 'person9'),
(11, 'person5', 'person10'),
(12, 'person3', 'person7'),
(13, 'person9', 'person5'),
(14, 'person9', 'person7'),
(15, 'person9', 'person5'),
(16, 'person3', 'person6'),
(17, 'person8', 'person9'),
(18, 'person10', 'person2'),
(19, 'person7', 'person5'),
(20, 'person10', 'person8');
17 rows affected

with t0 as (
select person,friend
from friendships
 where person='person10'
  union
select friend,person
from friendships
 where friend='person10'
)
, r as(
select person,friend,1 lvl
  ,cast(concat(person,'-',friend) as varchar(1000))path
from t0
union all
select r.person,t.friend ,r.lvl+1 lvl
  ,cast(concat(path,'-',t.friend) as varchar(1000)) path
from r
inner join friendships t on (t.person=r.friend and t.friend<>r.person)
where lvl<2
union all
select r.person,t.person ,r.lvl+1 lvl
  ,cast(concat(path,'-',t.person)  as varchar(1000))path
from r
inner join friendships t on (t.friend=r.friend and t.person<>r.person)
where lvl<2
)
,allfriends as (
select person,friend ,count(*) qty
from r
group by person,friend
)
select * from r order by lvl,friend;
select * from allfriends;
select person,count(*)friend_qty
from allfriends
group by person


person
friend
lvl
path


person10
person2
1
person10-person2

person10
person4
1
person10-person4

person10
person5
1
person10-person5

person10
person8
1
person10-person8

person10
person9
1
person10-person9

person10
person1
2
person10-person2-person1

person10
person3
2
person10-person9-person3

person10
person5
2
person10-person9-person5

person10
person5
2
person10-person9-person5

person10
person7
2
person10-person5-person7

person10
person7
2
person10-person9-person7

person10
person8
2
person10-person9-person8

person10
person9
2
person10-person8-person9

person10
person9
2
person10-person5-person9

person10
person9
2
person10-person5-person9


Msg 208 Level 16 State 1 Line 33
Invalid object name 'allfriends'.

SELECT * --,COUNT(DISTINCT f2.friend)
FROM friendships f1
JOIN friendships f2 ON f1.friend = f2.person
WHERE f1.person = 'person10' AND f2.friend != 'person10' AND f2.friend NOT IN (
    SELECT friend FROM friendships WHERE person = 'person10'
);


id
person
friend
id
person
friend


18
person10
person2
4
person2
person1

10
person10
person9
13
person9
person5

10
person10
person9
14
person9
person7

10
person10
person9
15
person9
person5


SELECT COUNT(DISTINCT f2.friend)
FROM friendships f1
JOIN friendships f2 ON f1.friend = f2.person
WHERE f1.person = 'person10' AND f2.friend != 'person10' AND f2.friend NOT IN (
    SELECT friend FROM friendships WHERE person = 'person10'
);


(No column name)


3

person	friend	lvl	path
person10	person2	1	person10-person2
person10	person4	1	person10-person4
person10	person5	1	person10-person5
person10	person8	1	person10-person8
person10	person9	1	person10-person9
person10	person1	2	person10-person2-person1
person10	person3	2	person10-person9-person3
person10	person5	2	person10-person9-person5
person10	person5	2	person10-person9-person5
person10	person7	2	person10-person5-person7
person10	person7	2	person10-person9-person7
person10	person8	2	person10-person9-person8
person10	person9	2	person10-person8-person9
person10	person9	2	person10-person5-person9
person10	person9	2	person10-person5-person9