Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table users( id bigserial primary key,  UserName varchar(30));
insert into users values
 (1,'User1(EN)-(FR)')
,(2,'User2(FR)-(EN,TUR)')
,(3,'User3(TUR)-()')
,(4,'User4(ITA)-()')
,(5,'User5(ITA)-(FR,TUR)')
,(6,'User6(DE)-()')
;
create table users_languages(id bigserial primary key, user_id bigint    not null
        constraint users_languages_user_id_foreign references users,
    level                varchar(255)         not null,
    lang_code            varchar(10)          not null
);
insert into users_languages values
 (1,1,'NATIVE','EN')
,(2,1,'LEARN','FR')
,(3,2,'NATIVE','FR')
,(4,2,'LEARN','EN')
,(5,2,'LEARN','TUR')
,(6,3,'NATIVE','TUR')
,(7,4,'NATIVE','ITA')
,(8,5,'NATIVE','ITA')
,(9,5,'LEARN','FR')
,(10,5,'LEARN','TUR')
,(11,6,'NATIVE','DE')
;
select * from users;
select * from users_languages;
select * from users_languages l
left join users u on u.id=l.user_id;

CREATE TABLE



INSERT 0 6



CREATE TABLE



INSERT 0 11




id
username


1
User1(EN)-(FR)

2
User2(FR)-(EN,TUR)

3
User3(TUR)-()

4
User4(ITA)-()

5
User5(ITA)-(FR,TUR)

6
User6(DE)-()



SELECT 6




id
user_id
level
lang_code


1
1
NATIVE
EN

2
1
LEARN
FR

3
2
NATIVE
FR

4
2
LEARN
EN

5
2
LEARN
TUR

6
3
NATIVE
TUR

7
4
NATIVE
ITA

8
5
NATIVE
ITA

9
5
LEARN
FR

10
5
LEARN
TUR

11
6
NATIVE
DE



SELECT 11




id
user_id
level
lang_code
id
username


2
1
LEARN
FR
1
User1(EN)-(FR)

1
1
NATIVE
EN
1
User1(EN)-(FR)

5
2
LEARN
TUR
2
User2(FR)-(EN,TUR)

4
2
LEARN
EN
2
User2(FR)-(EN,TUR)

3
2
NATIVE
FR
2
User2(FR)-(EN,TUR)

6
3
NATIVE
TUR
3
User3(TUR)-()

7
4
NATIVE
ITA
4
User4(ITA)-()

10
5
LEARN
TUR
5
User5(ITA)-(FR,TUR)

9
5
LEARN
FR
5
User5(ITA)-(FR,TUR)

8
5
NATIVE
ITA
5
User5(ITA)-(FR,TUR)

11
6
NATIVE
DE
6
User6(DE)-()



SELECT 11


select *
from users_languages l1
left join users_languages l2 on l2.lang_code=l1.lang_code and l1.user_id<>l2.user_id


id
user_id
level
lang_code
id
user_id
level
lang_code


1
1
NATIVE
EN
4
2
LEARN
EN

2
1
LEARN
FR
9
5
LEARN
FR

2
1
LEARN
FR
3
2
NATIVE
FR

3
2
NATIVE
FR
9
5
LEARN
FR

3
2
NATIVE
FR
2
1
LEARN
FR

4
2
LEARN
EN
1
1
NATIVE
EN

5
2
LEARN
TUR
10
5
LEARN
TUR

5
2
LEARN
TUR
6
3
NATIVE
TUR

6
3
NATIVE
TUR
10
5
LEARN
TUR

6
3
NATIVE
TUR
5
2
LEARN
TUR

7
4
NATIVE
ITA
8
5
NATIVE
ITA

8
5
NATIVE
ITA
7
4
NATIVE
ITA

9
5
LEARN
FR
3
2
NATIVE
FR

9
5
LEARN
FR
2
1
LEARN
FR

10
5
LEARN
TUR
6
3
NATIVE
TUR

10
5
LEARN
TUR
5
2
LEARN
TUR

11
6
NATIVE
DE
null
null
null
null



SELECT 17


-- for current user
with current_user_languages as(
  select * from users_languages l
  where user_id=1
)
,cul_intersects as(
select cul.user_id user1, cul.level level1,cul.lang_code code1
  ,l2.user_id user2, l2.level level2,l2.lang_code code2
from current_user_languages cul
left join users_languages l2 
   on l2.lang_code=cul.lang_code and cul.user_id<>l2.user_id
)
,priorities as(
select user1,user2
  ,case when sum(case when level1='LEARN' and level2='NATIVE' then 1 else 0 end)>0 
    and sum(case when level2='NATIVE' and level1='LEARN' then 1 else 0 end)>0 then 1
       when sum(case when level2='NATIVE' and level1='LEARN' then 1 else 0 end)>0 then 2
       when count(*)>0 then 3
   end priority
  ,sum(case when level1='LEARN' and level2='NATIVE' then 1 else 0 end) priority1_1
  ,sum(case when level2='LEARN' and level1='NATIVE' then 1 else 0 end) priority1_2
  ,sum(case when level2='NATIVE' and level1='LEARN' then 1 else 0 end) priority2
  ,count(*) totalIntersect
from cul_intersects
group by user1,user2
)
select user1,user2,	priority,priority1_1,priority1_2,priority2,totalintersect,username 
from priorities p
inner join users u on u.id=p.user2
order by user1,user2


user1
user2
priority
priority1_1
priority1_2
priority2
totalintersect
username


1
2
1
1
1
1
2
User2(FR)-(EN,TUR)

1
5
3
0
0
0
1
User5(ITA)-(FR,TUR)



SELECT 2


-- for all users, without langCodes and nativeCode
with all_intersects as(
select l1.user_id user1, l1.level level1,l1.lang_code code1
  ,l2.user_id user2, l2.level level2,l2.lang_code code2
from users_languages l1
left join users_languages l2 on l2.lang_code=l1.lang_code and l1.user_id<>l2.user_id
)
,priorities as(
select user1,user2
  ,case when sum(case when level1='LEARN' and level2='NATIVE' then 1 else 0 end)>0 
        and sum(case when level1='NATIVE' and level2='LEARN' then 1 else 0 end)>0 then 1
       when sum(case when level2='NATIVE' and level1='LEARN' then 1 else 0 end)>0 then 2
       when count(user2)>0 then 3
   end priority
  ,sum(case when level1='LEARN' and level2='NATIVE' then 1 else 0 end) priority1_1
  ,sum(case when level2='LEARN' and level1='NATIVE' then 1 else 0 end) priority1_2
  ,sum(case when level2='NATIVE' and level1='LEARN' then 1 else 0 end) priority2
  ,count(user2) totalIntersect
from all_intersects
group by user1,user2
)
--select * from priorities;

select user1,u1.username,user2,	priority,priority1_1,priority1_2,priority2
  ,totalintersect,u2.username 
from priorities p
left join users u1 on u1.id=p.user1
left join users u2 on u2.id=p.user2
order by user1,user2


user1
username
user2
priority
priority1_1
priority1_2
priority2
totalintersect
username


1
User1(EN)-(FR)
2
1
1
1
1
2
User2(FR)-(EN,TUR)

1
User1(EN)-(FR)
5
3
0
0
0
1
User5(ITA)-(FR,TUR)

2
User2(FR)-(EN,TUR)
1
1
1
1
1
2
User1(EN)-(FR)

2
User2(FR)-(EN,TUR)
3
2
1
0
1
1
User3(TUR)-()

2
User2(FR)-(EN,TUR)
5
3
0
1
0
2
User5(ITA)-(FR,TUR)

3
User3(TUR)-()
2
3
0
1
0
1
User2(FR)-(EN,TUR)

3
User3(TUR)-()
5
3
0
1
0
1
User5(ITA)-(FR,TUR)

4
User4(ITA)-()
5
3
0
0
0
1
User5(ITA)-(FR,TUR)

5
User5(ITA)-(FR,TUR)
1
3
0
0
0
1
User1(EN)-(FR)

5
User5(ITA)-(FR,TUR)
2
2
1
0
1
2
User2(FR)-(EN,TUR)

5
User5(ITA)-(FR,TUR)
3
2
1
0
1
1
User3(TUR)-()

5
User5(ITA)-(FR,TUR)
4
3
0
0
0
1
User4(ITA)-()

6
User6(DE)-()
null
null
0
0
0
0
null



SELECT 13


WITH langCodes as(select 'EN' langCode union all select 'FR')
,nativeCode as (select 'EN' nativeCode)
,user_lang_priority AS NOT MATERIALIZED (
                SELECT l.user_id, MIN(CASE 
                    WHEN l.level = 'NATIVE' AND l.lang_code IN(select * from langCodes) THEN
                      CASE
                        WHEN EXISTS (
                           SELECT ll.id FROM users_languages ll
                           WHERE ll.level != 'NATIVE'
                           AND ll.lang_code = (select nativeCode from nativeCode)
                           AND ll.user_id = l.user_id
                        ) THEN 1 ELSE 2
                      END
                    WHEN l.lang_code IN(select * from langCodes) THEN 3
                END) AS priority
                FROM users_languages l
                GROUP BY l.user_id
            )
--  select * from user_lang_priority;

SELECT u.id userId,lp.priority,userName
FROM users u
INNER JOIN user_lang_priority lp ON u.id = lp.user_id
ORDER BY priority ASC, u.id DESC


userid
priority
username


2
1
User2(FR)-(EN,TUR)

1
2
User1(EN)-(FR)

5
3
User5(ITA)-(FR,TUR)

6
null
User6(DE)-()

4
null
User4(ITA)-()

3
null
User3(TUR)-()



SELECT 6
id	username
1	User1(EN)-(FR)
2	User2(FR)-(EN,TUR)
3	User3(TUR)-()
4	User4(ITA)-()
5	User5(ITA)-(FR,TUR)
6	User6(DE)-()
id	user_id	level	lang_code
1	1	NATIVE	EN
2	1	LEARN	FR
3	2	NATIVE	FR
4	2	LEARN	EN
5	2	LEARN	TUR
6	3	NATIVE	TUR
7	4	NATIVE	ITA
8	5	NATIVE	ITA
9	5	LEARN	FR
10	5	LEARN	TUR
11	6	NATIVE	DE