1. With nyc_schools as (
2. SELECT
3.         child_organization_id AS school_id
4. FROM
5.         report.organization_hierarchy
6.         INNER JOIN
7.                 dw.dimorganization ON child_organization_id = organizationid
8. WHERE
9.         parent_organization_id      = '8a97b09d3d96517f013fad817713497a'
10.         AND child_organization_type = 'SCHOOL'
11. ),
12. login_data as (
13. SELECT trunc(eventoccurrencedatetime) as event_date, sessionid, actorid as user_id, dimuserenrollment.role_type from dw.learningevent eve
14.         LEFT JOIN nyc_schools sch on eve.organizationid = sch.school_id
15.     INNER JOIN report.dim_organization_user_enrollment AS dimuserenrollment ON eve.actorid = dimuserenrollment.user_id
16.     AND dimuserenrollment.role_type in ('TEACHER','STUDENT')
17. WHERE
18.         eventoccurrencedatetime between '2020-02-01' and '2020-02-02'
19.         AND eventtype in ('login','exited')
20.         AND  eve.objecttype in ('application-session')
21. )
22. ,
23. all_logins as ( SELECT login_data.event_date,
24.         CASE
25.             WHEN login_data.role_type::text = 'TEACHER'::character varying::text THEN count(login_data.sessionid)
26.             ELSE NULL::bigint
27.         END AS all_teacher_logins,
28.         CASE
29.             WHEN login_data.role_type::text = 'STUDENT'::character varying::text THEN count(login_data.sessionid)
30.             ELSE NULL::bigint
31.         END AS all_student_logins
32.    FROM login_data
33.    GROUP BY event_date, login_data.role_type
34. ) select * from all_logins