As an update to this question, I created a dashboard with the numbers that I need from CD2 data. Here is the SQL query I used to calculate aligned outcomes:
WITH total_outcomes_per_course AS (
SELECT
lo.context_id AS course_id,
COUNT(*) AS total_outcomes
FROM learning_outcomes lo
WHERE lo.workflow_state = 'active'
GROUP BY lo.context_id
),
aligned_outcomes_per_course AS (
SELECT
ct.context_id AS course_id,
COUNT(DISTINCT ct.learning_outcome_id) AS aligned_outcomes
FROM content_tags ct
JOIN learning_outcomes lo ON ct.learning_outcome_id = lo.id
WHERE ct.tag_type = 'learning_outcome'
AND lo.workflow_state = 'active'
GROUP BY ct.context_id
),
aligned_outcome_titles AS (
SELECT
ct.context_id AS course_id,
lo.id AS outcome_id,
lo.short_description AS outcome_title
FROM content_tags ct
JOIN learning_outcomes lo ON ct.learning_outcome_id = lo.id
WHERE ct.tag_type = 'learning_outcome'
AND lo.workflow_state = 'active'
)
SELECT
c.id AS course_id,
c.name AS course_name,
t.total_outcomes,
STRING_AGG(DISTINCT oat.outcome_title, '; ') AS aligned_outcome_titles
FROM total_outcomes_per_course t
LEFT JOIN aligned_outcomes_per_course a ON t.course_id = a.course_id
JOIN courses c ON t.course_id = c.id
LEFT JOIN aligned_outcome_titles oat ON oat.course_id = c.id
GROUP BY c.id, c.name, t.total_outcomes, a.aligned_outcomes
