1. WITH l_nyc_data_v AS (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
14.             FROM dw.learningevent eve
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.             INNER JOIN nyc_schools sch on dimuserenrollment.school_id = sch.school_id
18.           WHERE
19.             ((( eve.eventoccurrencedatetime ) >= (TIMESTAMP '2020-04-18') AND ( eve.eventoccurrencedatetime ) < (TIMESTAMP '2020-04-19')))
20.             AND eventtype in ('login')
21.             AND eve.objecttype in ('application-session')
22.             AND eve.intpartnerid = (select integrationpartnername from etl.integrationpartner where integrationpartnerid=3)
23.           )
24.           ,
25.           all_logins as ( SELECT login_data.event_date,
26.                   CASE
27.                       WHEN login_data.role_type::text = 'TEACHER'::character varying::text THEN count(login_data.sessionid)
28.                       ELSE NULL::bigint
29.                   END AS all_teacher_logins,
30.                   CASE
31.                       WHEN login_data.role_type::text = 'STUDENT'::character varying::text THEN count(login_data.sessionid)
32.                       ELSE NULL::bigint
33.                   END AS all_student_logins
34.              FROM login_data
35.              GROUP BY event_date, login_data.role_type
36.           )
37.           ,
38.           distinct_logins as ( SELECT login_data.event_date,
39.                   CASE
40.                       WHEN login_data.role_type::text = 'TEACHER'::character varying::text THEN count(distinct login_data.sessionid)
41.                       ELSE NULL::bigint
42.                   END AS u_teacher_logins,
43.                   CASE
44.                       WHEN login_data.role_type::text = 'STUDENT'::character varying::text THEN count(distinct login_data.sessionid)
45.                       ELSE NULL::bigint
46.                   END AS u_student_logins
47.              FROM login_data
48.              GROUP BY event_date, login_data.role_type
49.           )select al.event_date, al.all_teacher_logins, al.all_student_logins,
50.           d.u_teacher_logins, d.u_student_logins
51.           from all_logins al INNER JOIN distinct_logins d on al.event_date = d.event_date
52.     )
53. SELECT
54.         DATE(l_nyc_data_v.event_date ) AS "l_nyc_data_v.event_date",
55.         COALESCE(SUM(l_nyc_data_v.u_teacher_logins ), 0) AS "l_nyc_data_v.u_teacher_logins",
56.         COALESCE(SUM(l_nyc_data_v.all_teacher_logins ), 0) AS "l_nyc_data_v.all_teacher_logins",
57.         COALESCE(SUM(l_nyc_data_v.u_student_logins ), 0) AS "l_nyc_data_v.u_student_logins",
58.         COALESCE(SUM(l_nyc_data_v.all_student_logins ), 0) AS "l_nyc_data_v.all_student_logins"
59. FROM l_nyc_data_v
60.  
61. GROUP BY 1
62. ORDER BY 1 DESC
63. LIMIT 500