cte.sql

             with students_in_license as (
                  select
                    p.person_id, p.first_name, p.last_name, p.email, s.license_id
                  from person p
                  join team t on t.person_id=p.person_id
                  join class c on c.class_id=t.class_id
                  left join license_person ls on ls.person_id=p.person_id
                  left join license l on l.license_id=ls.license_id
                  left join survey s on s.class_id=c.class_id and s.survey_id=t.survey_id
                  where s.license_id=?
                  and (
                    ls.license_id IS NULL or (
                      CASE
                        WHEN l.valid_from < s.start_date AND l.expires_on < s.start_date THEN true
                        WHEN l.valid_from > s.end_date AND l.expires_on > s.end_date THEN true
                        ELSE false
                      END
                    )
                  )
              )
              select 
                students_in_license.*,
                string_agg(distinct p.email,', ') as instructors
              from person p
              join class c on c.faculty_id = p.person_id
              join survey s on s.class_id = c.class_id
              join team t on t.survey_id = s.survey_id
              join students_in_license on students_in_license.person_id = t.person_id
              join license l on l.license_id = students_in_license.license_id
              where s.end_date >= l.valid_from
              and s.start_date <= l.expires_on
              and substring(p.email from '@(.+)$') = substring(? from '@(.+)$')
              group by students_in_license.person_id,
                students_in_license.first_name,
                students_in_license.last_name,
                students_in_license.email,
                students_in_license.license_id
              order by students_in_license.last_name