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:

Connecting to BI Tools

You can also review:

Overview

Tables