SQL: List Courses Containing Users With A Specific Email Domain Enrolled

Summary

This query will return a list of courses where users with a specific email domain are enrolled in.

Use Case

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.

Code

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