Skip to content

Analytics Examples

Now that you understand how to select, filter, group, and join tables, it's time to see those patterns come together to answer our goal question: which day of the week has the lowest attendance for female students, by school? This section builds on everything you've learned, using the same student and attendance example we've been developing.

Building up to the final query#

Throughout the previous sections, we've been building toward this question step by step. Let's see how all the pieces fit together.

Step 1: Start with student names#

We began by selecting student names from dim_student:

select
    safe_display_name
from analytics.prod_wh.dim_student
limit 10;

Step 2: Filter to female students#

Then we added a filter to focus on female students:

select
    safe_display_name,
    grade_level
from analytics.prod_wh.dim_student
where gender = 'Female'
    and school_year = 2026
limit 10;

Step 3: Join to attendance data#

Next, we joined to attendance facts to get daily attendance records:

select
    dim_student.safe_display_name,
    dim_student.grade_level,
    fct_student_daily_attendance.is_present,
    fct_student_daily_attendance.is_absent
from analytics.prod_wh.fct_student_daily_attendance
left join analytics.prod_wh.dim_student
    on fct_student_daily_attendance.k_student = dim_student.k_student
where dim_student.gender = 'Female'
    and dim_student.school_year = 2026
limit 10;

Step 4: Add a two more dimensions and aggregate to answer the question#

Now we bring it all together. We add dim_school for school_name, add dim_calendar_date for day_of_week (Mon-Fri), and aggregate to answer our goal question: which day of the week has the lowest attendance for female students, by school?

select
    dim_school.k_school,
    dim_school.school_name,
    dim_calendar_date.day_of_week,
    sum(fct_student_daily_attendance.is_present) as days_present,
    sum(fct_student_daily_attendance.is_enrolled) as days_enrolled,
    sum(fct_student_daily_attendance.is_present)::float
        / nullif(sum(fct_student_daily_attendance.is_enrolled), 0)
        as attendance_rate
from analytics.prod_wh.fct_student_daily_attendance
join analytics.prod_wh.dim_student
    on fct_student_daily_attendance.k_student = dim_student.k_student
join analytics.prod_wh.dim_school
    on fct_student_daily_attendance.k_school = dim_school.k_school
join analytics.prod_wh.dim_calendar_date
    on fct_student_daily_attendance.k_calendar_date = dim_calendar_date.k_calendar_date
where dim_student.gender = 'Female'
    and dim_student.school_year = 2026
group by
    dim_school.k_school,
    dim_school.school_name,
    dim_calendar_date.day_of_week
order by
    dim_school.school_name,
    attendance_rate asc;

What's in the group by?

It's important to be intentional with your group by to enforce the correct grain of your output. In this case, we are including k_school and day_of_week, which are both necessary to get to the grain we're interested in. We also include school_name for illustrative effect - we want the query to include school names, and if a column is in the select clause, it must be in the group by. But note that in EDU, k_school is what makes a school unique, so there'd never actually be a case where one k_school is linked to >1 school_name.

If you'd like to be more precise with your group by while still displaying extraneous columns like school_name, see this example from the Demographics Query Guide, and its use of the any_value aggregate function.

This final query:

  • Starts from the daily attendance fact table — the daily grain is what makes day-of-week analysis possible
  • Joins three dimensions: dim_student for demographics, dim_school for school names, dim_calendar_date for day of the week
  • Filters to female students in 2025-2026
  • Groups by school and day of week to compare attendance patterns
  • Calculates attendance rate as days_present / days_enrolled, using nullif to avoid division by zero
  • Orders by attendance rate ascending within each school, so the lowest-attendance days appear first

This kind of within-day-level analysis (day-of-week patterns, month-over-month trends, specific date ranges) is exactly the type of question that requires the daily-grain fact table.

When you don't need the daily grain

If you need overall attendance rates per student or per school — rather than day-level patterns — you may not need fct_student_daily_attendance at all. The msr_student_cumulative_attendance table in prod_wh contains pre-calculated cumulative attendance metrics (days enrolled, days present, days absent, attendance rate, chronic absenteeism status) at the student/school/year grain. It's faster to query and avoids the need to aggregate daily rows yourself. See the Attendance Query Guide for more details.

Wrapping up#

These examples show how the pieces you've learned come together in practice. Each query follows the same pattern:

  1. Start from a fact table (the events you care about)
  2. Join dimension tables (to add context and human-readable labels)
  3. Filter (to focus on the data you need)
  4. Group and aggregate (to summarize the data)
  5. Order (to make results easy to interpret)

The most important skill is recognizing this pattern: identify the event you care about, join in the dimensions that make it meaningful, and then aggregate or filter the data to answer a specific question.

With this foundation, you should be able to read and adapt existing queries, explore new tables with confidence, and begin building analyses that support real reporting and decision-making needs.

Continue learning

For more advanced SQL patterns and style guidelines, see the SQL Style Guide. It covers best practices for writing maintainable, readable SQL queries in EDU.

Previous
« Joins