gistfile1.sql

with demo_surveys as (
  select distinct s.survey_id
  from survey s
  join team t on t.survey_id=s.survey_id
  join person student on student.person_id=t.person_id
  where substring(student.email from '@(.+)$') = 'sysiphus.com'
  group by s.survey_id),
demos as (select
  ds.survey_id,
  CASE
    WHEN
      substring(p.email from '@(.+)$') = 'sysiphus.com' 
      OR  p.person_id  IN (select person_id from demo_students) 
      OR  p.person_id = c.faculty_id
      OR  p.person_type <> 'student'
      OR  p.person_status <> 'active'
      OR p.last_login < (current_date - INTERVAL '120 days')
      THEN 'DEMO'
    ELSE 'REGULAR'
  END as student_type,
  count(ds.survey_id) counts
from demo_surveys ds
join survey s on s.survey_id=ds.survey_id
join team t on t.survey_id=ds.survey_id
join class c on c.class_id=t.class_id
join person p on p.person_id=t.person_id
where s.end_date < (current_date - INTERVAL '30 days')
group by ds.survey_id, student_type)
select
  survey_id, array_agg(student_type), array_agg(counts)
from demos
group by survey_id
having (array_agg(counts))[2] < 5 OR (array_agg(counts))[2] IS NULL
;