FHSU Blackboard Data Queries

FHSU-Blackboard-Data-Queries is a collection of 50 production SQL queries that analyze educational data from Fort Hays State University's Blackboard system through Anthology Illuminate and Snowflake. The queries extract insights about student engagement, course compliance, instructor activity, and tool adoption across thousands of courses to support data-driven decisions in the Teaching Innovation and Learning Technologies (TILT) department.

The Problem

FHSU's Blackboard system holds massive amounts of educational data, but extracting meaningful insights requires complex SQL queries across multiple tables. Administrators need answers: Which courses haven't updated their syllabus in over a year? How are students engaging with course tools? Which departments are adopting Ultra courses? Where are assignments overdue? Without optimized queries, these analyses become time-consuming and inconsistent, making it difficult to support faculty development, ensure quality standards, and inform technology decisions.

The Solution

I built a comprehensive suite of production-ready Snowflake queries organized into six analytical categories.

• Student Engagement Analysis tracks interaction patterns with course content, tools, and assessments to identify engagement trends and at-risk students requiring intervention.
• Course Content Compliance monitors syllabus age and last modified dates to ensure courses meet institutional standards and identify outdated materials needing updates.
• Assignment Analytics identifies overdue assignments, tracks upcoming due dates, and monitors submission patterns to help advisors and faculty intervene when students fall behind.
• Institutional Hierarchy Reporting extracts organizational structure data enabling aggregations by college, department, and division for administrative oversight and departmental comparisons.
• Tool Usage Analytics analyzes adoption of Blackboard features like discussion boards, assignments, and AI Design Assistant to guide training priorities and measure platform utilization.
• Faculty Activity Tracking aggregates instructor assignments, teaching loads, and contact information to support faculty development and outreach campaigns.

My Contributions

I architected and built this entire query collection from initial development through production deployment. I analyzed Anthology Illuminate's Common Data Model schema to understand table relationships and join patterns, then designed efficient queries that process thousands of courses and hundreds of thousands of student interactions quickly.

I implemented Snowflake-specific optimizations using aggregation functions like LISTAGG for combining instructor information, SPLIT_PART for parsing hierarchical data, and date interval filtering for term-based analyses. I established standardized patterns for alias naming, hierarchy extraction, and filtering to ensure maintainability across all 50 queries. I also created comprehensive documentation explaining business logic, expected outputs, and usage contexts so other TILT team members can understand and extend the collection for evolving reporting needs.