Example Queries

this section provides sample queries to help you get started with the data warehouse these examples demonstrate common reporting use cases such as admissions tracking, student performance, financial reporting, and attendance analysis you can use them as a starting point and modify them based on your institution’s needs before you begin queries are written in sql and can be run using tools such as amazon athena or connected bi tools like microsoft power bi if you are new to sql, you can still use these examples by adapting filters, fields, or grouping based on what you are trying to report on admissions leads and conversions by advisor this query shows how many leads each advisor has handled and how many of those leads converted into enrolled students select p admission advisor, count(case when sh stage = 'lead' then 1 end) as leads, count(case when sh stage = 'enrolled' then 1 end) as enrolled, count(case when sh stage = 'enrolled' then 1 end) 1 0 / nullif(count(case when sh stage = 'lead' then 1 end), 0) as conversion rate from dw prospect stage history sh join dw prospects p on p student key = sh prospect key group by p admission advisor order by conversion rate desc; leads by source this query shows where your leads are coming from select p source, count( ) as total leads from dw prospects p group by p source order by total leads desc; students student count by program this query shows the number of students in each program select current program name, count( ) as student count from dw student overview group by current program name order by student count desc; students at risk (attendance) this query identifies students with high absence counts select student name, total absences, attendance status from dw student overview where total absences > 10 order by total absences desc; financial outstanding balances this query shows students with outstanding balances select student name, balance due from dw student financial summary where balance due > 0 order by balance due desc; aging breakdown this query summarizes balances by aging category select sum(balance due current) as current due, sum(balance due 30 days) as due 30, sum(balance due 60 days) as due 60, sum(balance due 90 days) as due 90 from dw student financial summary; attendance attendance ratio this query calculates attendance ratios for students select student name, total attended, total absences, total attended 1 0 / nullif(total attended + total absences, 0) as attendance ratio from dw student attendance summary order by attendance ratio asc; daily attendance this query shows total attendance records per day select attendance date, count( ) as total records from dw student attendance group by attendance date order by attendance date desc; academic gpa distribution this query shows how students are distributed across gpa ranges select grade point average, count( ) as student count from dw student academic summary group by grade point average order by grade point average desc; top students this query returns the highest performing students based on gpa select student name, grade point average from dw student academic summary order by grade point average desc limit 10; courses course completion rates this query calculates completion rates for each course select course name, num completed 1 0 / nullif(num enrolled, 0) as completion rate from dw courses order by completion rate desc; instructor workload this query shows how many courses each instructor is assigned to select instructor name, count( ) as course count from dw course instructors group by instructor name order by course count desc; cross domain analysis financial and academic risk this query identifies students who may be at risk both academically and financially select o student name, o grade point average, o balance due from dw student overview o where o grade point average < 2 0 and o balance due > 1000 order by o balance due desc; next steps you can use these examples as a starting point and adjust them based on your reporting needs for more advanced reporting, you can combine multiple tables using shared keys add filters based on location, program, or dates connect your data to bi tools for visualization learn how to connect the data warehouse to external tools docid\ j0tpcbbrvjek6oeh6p4en you can also review docid\ ptudjpwbbjsb18lwlhzo3 docid\ wnunql02lsdb1o7g a8qz