AnsweredAssumed Answered

SQL query that shows all student page views in a sub-account

Question asked by Glenn Saito on Feb 5, 2020
Latest reply on Feb 11, 2020 by Glenn Saito

I'm fairly new to Canvas Data and SQL. Our IT department recently created an SQL Server for Canvas Data and I'm now able to run queries.  I tried to create a query to display terms, the sub-account, course numbers, course names, student names, page names, and page views, but when I added the user_dim table to display student names, it only displays teachers and TA's.  Any suggestions to display students?  Here is the query:
SELECT
enrollment_term_dim.name, account_dim.subaccount3, course_dim.code, course_dim.name, user_dim.name, wiki_page_fact.view_count, wiki_page_dim.title
FROM wiki_page_fact
JOIN wiki_page_dim
ON wiki_page_dim.id = wiki_page_fact.wiki_page_id
JOIN course_dim
ON course_dim.id = wiki_page_fact.parent_course_id
JOIN enrollment_term_dim
ON enrollment_term_dim.id = wiki_page_fact.enrollment_term_id
JOIN account_dim
ON account_dim.id = wiki_page_fact.account_id
JOIN user_dim
ON user_dim.id = wiki_page_fact.user_id
WHERE account_dim.subaccount3 = 'MS Psychology'

Outcomes