SQL to find two people who participated in the most meetings together

Question

I have three tables which are as follows:

from this I have to find the two people who participated in the most meetings together.

Answer 1

This should get you going.

Sample data

create table person
(
  id int,
  name nvarchar(10)
);    
insert into person (id, name) values
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'David'),
(5, 'Eric');

create table meeting
(
  id int,
  title nvarchar(30)
);    
insert into meeting (id, title) values
(100, 'Corporate training'),
(200, 'Weekly sales'),
(300, 'Welcome introduction'),
(400, 'Evaluation');

create table participant
(
  idMeeting int,
  idPerson int
);    
insert into participant (idMeeting, idPerson) values
(100,1), (100,2), (100,3), (100,4), (100,5),
(200,1), (200,2),
         (300,2), (300,3), (300,4),
                  (400,3),          (400,5),
                  (500,3),          (500,5);

Solution

Find shared meetings by joining participant rows with the same meeting id (pa2.idMeeting = pa1.idMeeting).

select me.title,
       pe1.name,
       pe2.name
from participant pa1
join participant pa2
  on  pa2.idMeeting = pa1.idMeeting
  and pa2.idPerson <> pa1.idPerson
join meeting me
  on me.id = pa1.idMeeting
join person pe1
  on pe1.id = pa1.idPerson
join person pe2
  on pe2.id = pa2.idPerson;

This will give you many duplicates such as Alice went to the Corporate training with Bob and Bob went to the Corporate training with Alice. These are of course the same occurance. We can filter out duplicates by saying that not only do participants have to be different (pa2.idPerson <> pa1.idPerson), they also have to be "sorted" (pa2.idPerson > pa1.idPerson).

select me.title,
       pe1.name,
       pe2.name
from participant pa1
join participant pa2
  on  pa2.idMeeting = pa1.idMeeting
  and pa2.idPerson > pa1.idPerson
join meeting me
  on me.id = pa1.idMeeting
join person pe1
  on pe1.id = pa1.idPerson
join person pe2
  on pe2.id = pa2.idPerson;

This gives you all unique combinations for all meetings. The meeting details can be excluded from the result (removed join meeting). Grouping on the unique combinations (group by pe1.name, pe2.name) gives a count (count(1) as SharedMeetings) to work with.

select pe1.name as Person1,
       pe2.name as Person2,
       count(1) as SharedMeetings
from participant pa1
join participant pa2
  on  pa2.idMeeting = pa1.idMeeting
  and pa2.idPerson > pa1.idPerson
join person pe1
  on pe1.id = pa1.idPerson
join person pe2
  on pe2.id = pa2.idPerson
group by pe1.name,
         pe2.name
order by SharedMeetings desc,
         pe1.name,
         pe2.name;

Result

For the final query:

Person1  Person2  SharedMeetings
-------  -------  --------------
Charlie  Eric     3
Alice    Bob      2
Bob      Charlie  2
Bob      David    2
Charlie  David    2
Alice    Charlie  1
Alice    David    1
Alice    Eric     1
Bob      Eric     1
David    Eric     1

Fiddle to see things in action (with intermediate versions).

Answer 2

This is simply a self-join on meeting:

select top (1) with ties p1.person_id, p2.person_id,
       count(*) as num_meetings
from participant p1 join
     participant p2
     on p1.meeting_id = p2.meeting_id
group by p1.person_id, p2.person_id
order by count(*) desc;

This returns the person ids, which answers your question. If you want more information about the persons from the person table, then you can join such information in.

Answer 3

SELECT  TOP(1)
        partnerA.person_id,
        partnerB.person_id,
        (
            SELECT  COUNT(*)
            FROM    meeting
            WHERE   EXISTS(SELECT 1 FROM participant WHERE participant.meeting_id = meeting.meeting_id AND participant.person_id = partnerA.person_id)
            AND     EXISTS(SELECT 1 FROM participant WHERE participant.meeting_id = meeting.meeting_id AND participant.person_id = partnerB.person_id)
        )   AS      participated_together

FROM        person  partnerA
CROSS JOIN  person  partnerB
WHERE       partnerA.person_id  <   partnerB.person_id

ORDER BY    participated_together   DESC

This shows only 1 pair with most meetings together,

but you can change it to show more - just change the TOP(1)

Answer 4

This is just in case there are multiple pairs of people who attended same maximum number of meetings

with t1 as (select a.person_id, b.person_id, count(*) as cnt
       from meetings a join meetings b
       on a.meeting_id = b.meeting_id
       and a.person_id < b.person_id
       group by 1, 2)
       
       select concat(a.first_name, ' ', a.last_name) as person1,
       concat(b.first_name, ' ', b.last_name) as person2
       from t1 join person a
       on t1.person_id = a.person_id
       join person b
       on t1.person_id = b.person_id
       where cnt = (select max(cnt) from t1)

Answer 5

Here you go:

WITH OUTE AS
(SELECT CONCAT(D.FIRST_NAME,' ',D.LAST_NAME) FIRST_PERSON,
    CONCAT(E.FIRST_NAME,' ',E.LAST_NAME) SECOND_PERSON,
    C.TITLE
  FROM  PARTICIPANT A
    JOIN PARTICIPANT B
       ON A.MEETING_ID=B.MEETING_ID
      AND A.PERSON_ID !=B.PERSON_ID
      AND A.PERSON_ID < B.PERSON_ID
    JOIN PERSON D
       ON A.PERSON_ID=D.PERSON_ID
    JOIN PERSON E
       ON B.PERSON_ID=E.PERSON_ID
    JOIN MEETING C
       ON A.MEETING_ID=C.MEETING_ID
 ),

OUTE2 AS

(SELECT FIRST_PERSON,
    SECOND_PERSON,
    COUNT(*) MEETINGS
FROM OUTE
GROUP BY FIRST_PERSON,
         SECOND_PERSON)
,
OUTE3 AS
(SELECT X.FIRST_PERSON,
    X.SECOND_PERSON,
    X.TITLE,
    Y.MEETINGS
FROM OUTE X 
    JOIN OUTE2 Y
         ON X.FIRST_PERSON=Y.FIRST_PERSON
        AND X.SECOND_PERSON=Y.SECOND_PERSON
    ORDER BY Y.MEETINGS DESC)

SELECT FIRST_PERSON,
         SECOND_PERSON,
         TITLE
FROM OUTE3
    WHERE MEETINGS = (SELECT MEETINGS FROM OUTE3 LIMIT 1)
    ORDER BY TITLE;

The solution is to be improved.