WITH l_nyc_data_v AS (With nyc_schools as (
          SELECT
            child_organization_id AS school_id
          FROM
            report.organization_hierarchy
            INNER JOIN
              dw.dimorganization ON child_organization_id = organizationid
          WHERE
            parent_organization_id      = '8a97b09d3d96517f013fad817713497a'
            AND child_organization_type = 'SCHOOL'
          ),
          login_data as (
          SELECT trunc(eventoccurrencedatetime) as event_date, sessionid, actorid as user_id, dimuserenrollment.role_type
            FROM dw.learningevent eve
            INNER JOIN report.dim_organization_user_enrollment AS dimuserenrollment ON eve.actorid = dimuserenrollment.user_id
              AND dimuserenrollment.role_type in ('TEACHER','STUDENT')
            INNER JOIN nyc_schools sch on dimuserenrollment.school_id = sch.school_id
          WHERE
            ((( eve.eventoccurrencedatetime ) >= (TIMESTAMP '2020-04-18') AND ( eve.eventoccurrencedatetime ) < (TIMESTAMP '2020-04-19')))
            AND eventtype in ('login')
            AND eve.objecttype in ('application-session')
            AND eve.intpartnerid = (select integrationpartnername from etl.integrationpartner where integrationpartnerid=3)
          )
          ,
          all_logins as ( SELECT login_data.event_date,
                  CASE
                      WHEN login_data.role_type::text = 'TEACHER'::character varying::text THEN count(login_data.sessionid)
                      ELSE NULL::bigint
                  END AS all_teacher_logins,
                  CASE
                      WHEN login_data.role_type::text = 'STUDENT'::character varying::text THEN count(login_data.sessionid)
                      ELSE NULL::bigint
                  END AS all_student_logins
             FROM login_data
             GROUP BY event_date, login_data.role_type
          )
          ,
          distinct_logins as ( SELECT login_data.event_date,
                  CASE
                      WHEN login_data.role_type::text = 'TEACHER'::character varying::text THEN count(distinct login_data.sessionid)
                      ELSE NULL::bigint
                  END AS u_teacher_logins,
                  CASE
                      WHEN login_data.role_type::text = 'STUDENT'::character varying::text THEN count(distinct login_data.sessionid)
                      ELSE NULL::bigint
                  END AS u_student_logins
             FROM login_data
             GROUP BY event_date, login_data.role_type
          )select al.event_date, al.all_teacher_logins, al.all_student_logins,
          d.u_teacher_logins, d.u_student_logins
          from all_logins al INNER JOIN distinct_logins d on al.event_date = d.event_date
    )
SELECT 
	DATE(l_nyc_data_v.event_date ) AS "l_nyc_data_v.event_date",
	COALESCE(SUM(l_nyc_data_v.u_teacher_logins ), 0) AS "l_nyc_data_v.u_teacher_logins",
	COALESCE(SUM(l_nyc_data_v.all_teacher_logins ), 0) AS "l_nyc_data_v.all_teacher_logins",
	COALESCE(SUM(l_nyc_data_v.u_student_logins ), 0) AS "l_nyc_data_v.u_student_logins",
	COALESCE(SUM(l_nyc_data_v.all_student_logins ), 0) AS "l_nyc_data_v.all_student_logins"
FROM l_nyc_data_v

GROUP BY 1
ORDER BY 1 DESC
LIMIT 500