SQL: Total Number of Announcements Created Per Course

Summary

Identifies the total number of announcements created per course.

Data Source: Blackboard DDA on SaaS

Code

SELECT cm.course_name, count(a.pk1) as total_number_of_announcements
FROM PUBLIC.announcements a
INNER JOIN PUBLIC.course_main cm
ON cm.pk1 = a.crsmain_pk1
WHERE cm.course_id LIKE '%.202020' -- Filters by Course ID
AND cm.row_status = '0' -- Only Active Courses
AND a.start_date BETWEEN '2020-01-01 00:00:00' AND '2020-05-11 23:59:59' -- Filters never accessed and accessed more than 7 days
GROUP BY cm.course_name
ORDER BY total_number_of_announcements desc