This query will return a list of courses where users with a specific email domain are enrolled in.
Our university uses academic coaches that are contracted by another company. For reporting purposes, we needed to determine which courses contain an academic coach. Since all coaches use their company email address as their username and email, this report searches for all courses containing their domain in the username and email fields.
SELECT DISTINCT cm.course_name FROM PUBLIC.course_main cm INNER JOIN PUBLIC.course_users cu ON cm.pk1 = cu.crsmain_pk1 INNER JOIN PUBLIC.users u ON u.pk1 = cu.users_pk1 --Filter by Username and Email Address WHERE (u.user_id LIKE '%<<Insert Email Domain Here>>' OR u.email LIKE '%<<Insert Email Domain Here>>') --Filter by Course Name AND cm.course_name LIKE '%Spring2020Module1%' ORDER BY cm.course_name ASC