CODEWITHSUNDEEP
sql
Lightfoot712
Lightfoot712

Reputation: 11

How to use SQL to determine which two students share the most classes together?

I'm putting together a tool for students to determine what classes they share with other students.

I'm trying to figure out which two students have been in the most classes together. The result should print the first students name, then the second students name, then the class name, like this: first_student_name, second_student_name, class_name

The data is stored in a weird way. It's in three tables with the follow schema:

STUDENT

student_id INT, student_first_name varchar, student_second_name varchar

CLASS

class_id INT, class_name varchar, student_first_name varchar, student_second_name varchar

XWALK

class_id INT, student_id INT

Is there an easy function in SQL that would allow me to do this if I JOINed all the data into one table? I was thinking that maybe this could be solved with a matrix function in the inner query, counting the class_ids where student x and student y were in the same class.

Any advice on how to go about putting this together would be helpful!

Upvotes: 1

Views: 736

Answers (1)

GMB
GMB

Reputation: 222432

If I follow you correctly, you can self-join bridge table xwalk to identify pairs of students that attend the same class, then aggregate to count how many classes they have in common. Bringing the student names is another pair of joins. Finally, we can sort the result by class count, and keep the first row only.

select 
    s1.student_name as student_name_1,
    s2.student_name as student_name_2,
    count(*) cnt_classes
from xwalk w1
inner join xwalk x2 on x1.class_id = x2.class_id and x1.student_id < x2.student_id
inner join student s1 on s1.student_id = x1.student_id
inner join student s2 on s2.student_id = x2.student_id
group by s1.student_id, s2.student_id
order by count(*) desc limit 1

Upvotes: 2

Related Questions