Efficiency/Clean code in multiple subquery join

Question

I've built a large subquery and need to filter and then sum() it three different ways. I've aggregated it and then joined those aggregation together but this leaves me with a query with three identical sections (the subquery).

Is there a more efficient, programatic, or clean way to accomplish this? I understand that clean and efficient could be wildly different.

I've highlighted the changed portion of each join.

select 
total_times.student_id
, total_times.section_id
, present_times.total as present
, relation_times.total as relation
, total_times.total as total
from (
    select 
    s.student_id
    , sec.section_id
    , sum(sec_times.time) as total
    from students s
    join section_student_aff secsaff on secsaff.student_id = s.student_id
    join sections sec on sec.section_id = secsaff.section_id
    join (
        select distinct
        site.site_id
        , cal.date
        , sec.section_id
        , (time.end_time - time.start_time) as time
        from calendar_days cal
        join terms t on cal.date between t.start_date and t.end_date
        join sessions ses on ses.session_id = t.session_id
        join timeblocks blk on blk.session_id = ses.session_id
        join timeblock_times time on time.timeblock_id = blk.timeblock_id
        join sites site on site.site_id = ses.site_id
        join section_timeblock_aff sectaff on sectaff.timeblock_id = blk.timeblock_id
        join sections sec on sec.section_id = sectaff.section_id
        where ses.academic_year = 2016
        and time.day_of_week = extract(dow from cal.date)
        order by site_id, date, section_id
        ) sec_times
        on sec_times.section_id = sec.section_id

    --- Part 1 (no filter) ---

    group by s.student_id, sec.section_id
    ) total_times
join (
    select 
    s.student_id
    , sec.section_id
    , sum(sec_times.time) as total
    from students s
    join section_student_aff secsaff on secsaff.student_id = s.student_id
    join sections sec on sec.section_id = secsaff.section_id
    join (
        select distinct
        site.site_id
        , cal.date
        , sec.section_id
        , (time.end_time - time.start_time) as time
        from calendar_days cal
        join terms t on cal.date between t.start_date and t.end_date
        join sessions ses on ses.session_id = t.session_id
        join timeblocks blk on blk.session_id = ses.session_id
        join timeblock_times time on time.timeblock_id = blk.timeblock_id
        join sites site on site.site_id = ses.site_id
        join section_timeblock_aff sectaff on sectaff.timeblock_id = blk.timeblock_id
        join sections sec on sec.section_id = sectaff.section_id
        where ses.academic_year = 2016
        and time.day_of_week = extract(dow from cal.date)
        order by site_id, date, section_id
        ) sec_times
        on sec_times.section_id = sec.section_id

    --- Part 2 ---
    where exists (
        select null
        from student_attendance sa
        join attendance_flags af on af.attendance_flag_id = sa.attendance_flag_id
        where
        af.is_present=true
        and sa.date = sec_times.date
        and sa.sa_student_id = s.student_id
        )
    --------------

    group by s.student_id, sec.section_id
    ) present_times
    on present_times.student_id = total_times.student_id
    and present_times.section_id = total_times.section_id
join (
    select 
    s.student_id
    , sec.section_id
    , sum(sec_times.time) as total
    from students s
    join section_student_aff secsaff on secsaff.student_id = s.student_id
    join sections sec on sec.section_id = secsaff.section_id
    join (
        select distinct
        site.site_id
        , cal.date
        , sec.section_id
        , (time.end_time - time.start_time) as time
        from calendar_days cal
        join terms t on cal.date between t.start_date and t.end_date
        join sessions ses on ses.session_id = t.session_id
        join timeblocks blk on blk.session_id = ses.session_id
        join timeblock_times time on time.timeblock_id = blk.timeblock_id
        join sites site on site.site_id = ses.site_id
        join section_timeblock_aff sectaff on sectaff.timeblock_id = blk.timeblock_id
        join sections sec on sec.section_id = sectaff.section_id
        where ses.academic_year = 2016
        and time.day_of_week = extract(dow from cal.date)
        order by site_id, date, section_id
        ) sec_times
        on sec_times.section_id = sec.section_id

    --- Part 3 ---
    where sec_times.date between secsaff.entry_date and secsaff.leave_date
    --------------

    group by s.student_id, sec.section_id
    ) relation_times
    on relation_times.student_id = total_times.student_id
    and relation_times.section_id = total_times.section_id
order by student_id, section_id

Answer 1

Using WITH, and a conditional sum, it becomes much more manageable (and perhaps faster):

with
  sec_times as (
    select distinct
    site.site_id
    , cal.date
    , sec.section_id
    , (time.end_time - time.start_time) as time
    from calendar_days cal
    join terms t on cal.date between t.start_date and t.end_date
    join sessions ses on ses.session_id = t.session_id
    join timeblocks blk on blk.session_id = ses.session_id
    join timeblock_times time on time.timeblock_id = blk.timeblock_id
    join sites site on site.site_id = ses.site_id
    join section_timeblock_aff sectaff on sectaff.timeblock_id = blk.timeblock_id
    join sections sec on sec.section_id = sectaff.section_id
    where ses.academic_year = 2016
    and time.day_of_week = extract(dow from cal.date)
    ),
  sec_times_full as (
    select 
    s.student_id
    , sec.section_id
    , sec_times.time
    , exists (
        select null
        from student_attendance sa
        join attendance_flags af on af.attendance_flag_id = sa.attendance_flag_id
        where
        af.is_present=true
        and sa.date = sec_times.date
        and sa.sa_student_id = s.student_id
        ) as is_present
    , sec_times.date between secsaff.entry_date and secsaff.leave_date as is_relation
    from students s
    join section_student_aff secsaff on secsaff.student_id = s.student_id
    join sections sec on sec.section_id = secsaff.section_id
    join sec_times on sec_times.section_id = sec.section_id
    )
select 
  student_id
  , section_id
  , sum(CASE WHEN is_present  THEN time END) as present
  , sum(CASE WHEN is_relation THEN time END) as relation
  , sum(time) as total
from sec_times_full
group by student_id, section_id
order by 1, 2