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, count(sessionid), 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-03' and '2020-02-04'
19.         AND eventtype in ('login')
20.         --AND  eve.objecttype in ('application-session')
21.         GROUP BY trunc(eventoccurrencedatetime) , dimuserenrollment.role_type
22. ) select * from login_data;
23.