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 
	LEFT JOIN nyc_schools sch on eve.organizationid = sch.school_id
    INNER JOIN report.dim_organization_user_enrollment AS dimuserenrollment ON eve.actorid = dimuserenrollment.user_id
    AND dimuserenrollment.role_type in ('TEACHER','STUDENT') 
WHERE 
	eventoccurrencedatetime between '2020-02-01' and '2020-02-02'
	AND eventtype in ('login','exited')
	AND  eve.objecttype in ('application-session')
) 
,
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
) select * from all_logins