Advanced SQL: Find groups of students who take the same courses

How to group students who take the same courses using SQL? The trick is to assign a unique value to the combination of courses taken by a student, and then group students by their assigned values. There are 2 options for the assignment.

1. Assign every course a unique bit. For example, course A = 1 (0001), course B = 2 (0010), course C = 4 (0100), etc. Then, for all possible combinations of courses, their sum of the bits are unique.
2. Sort and concatenate course IDs.

Option 2's string concatenation is not SQL standard. Different RDBMS has different syntax. Option 1, on the other hand, can be implemented in standard SQL. Below is an example for option 1.

	with course_bitmap as (
	select
	Course_ID,
	power(2, row_number() over (order by course_id) - 1) as course_bit
	from Course
	)
	select
	sum(cb.course_bit) over (partition by sc.Student_id) as Group_id,
	sc.Student_id,
	sc.Course_id
	from
	Student_Course sc join course_bitmap cb on
	sc.Course_ID = cb.Course_ID
	order by Group_id, Student_ID, Course_ID

view raw StudentCourse.sql hosted with ❤ by GitHub