1. WITH
2.         school_dates AS
3.         ( SELECT
4.                 d.date_id,
5.                 d.date_value,
6.                 child_organization_id AS school_id,
7.                 parent_organization_id AS district_id
8.         FROM
9.                 report.dim_date d,
10.                 report.organization_hierarchy
11.         WHERE
12.             child_organization_type  = 'DISTRICT' and parent_organization_type  = 'DISTRICT'
13.                 AND d.date_id               > 20190801 --start date
14.                 AND d.date_value            < DATE_TRUNC('day', GETDATE())
15.         )
16.         ,
17.         logins AS
18.         ( SELECT
19.                 s.date_value,
20.                 s.district_id,
21.                 SUM(measure_login_count) login_count,
22.                 COUNT(DISTINCT user_id)  user_count
23.         FROM
24.                 school_dates s
25.                 INNER JOIN
26.                         report.summary_logins l ON s.school_id = l.school_id AND s.date_id = l.date_id
27.         GROUP BY
28.                 s.date_value,
29.                 s.district_id
30.         )
31.         ,
32.         assessments_completed AS
33.         ( SELECT
34.                 s.date_value,
35.                 s.district_id,
36.                 COUNT(DISTINCT userassignment_id) assessments_completed_count
37.         FROM
38.                 school_dates s
39.                 INNER JOIN
40.                         report.fact_content_scores cs ON s.school_id = cs.school_id AND s.date_id = cs.date_id AND content_type = 'TEST'
41.         GROUP BY
42.                 s.date_value,
43.                 s.district_id
44.         )
45.         ,
46.         assignments_completed AS
47.         ( SELECT
48.                 s.date_value,
49.                 s.district_id,
50.                 COUNT(DISTINCT userassignment_id) assignments_completed_count
51.         FROM
52.                 school_dates s
53.                 INNER JOIN
54.                         report.fact_content_scores cs ON s.school_id = cs.school_id AND s.date_id = cs.date_id AND assignment_id IS NOT NULL
55.         GROUP BY
56.                 s.date_value,
57.                 s.district_id
58.         )
59.         ,
60.         student_class_enrollments AS
61.         ( SELECT
62.                 s.date_value,
63.                 s.district_id,
64.                 COUNT(*) student_class_enrollements_count
65.         FROM
66.                 school_dates s
67.                 INNER JOIN
68.                         --using the record insert date time since we don't have a clear enrollment creation date time in the table
69.                         report.dim_class_student_enrollment se ON s.school_id = se.school_id
70.                         AND se.insert_datetime >= s.date_value
71.                         AND se.insert_datetime  < DATEADD(DAY, 1, s.date_value)
72.         GROUP BY
73.                 s.date_value,
74.                 s.district_id
75.         )
76.         ,
77.         classes_created AS
78.         ( SELECT
79.                 s.date_value,
80.                 s.district_id,
81.                 COUNT(*) classes_created_count
82.         FROM
83.                 school_dates s
84.                 INNER JOIN
85.                         dw.dimclass c ON s.school_id = c.organizationid
86.                         AND c.createddate >= s.date_value
87.                         AND c.createddate  < DATEADD(DAY, 1, s.date_value)
88.         WHERE
89.                 c.operation = 'Create'
90.         GROUP BY
91.                 s.date_value,
92.                 s.district_id
93.         )
94.         ,
95.         assignments_created AS
96.         ( SELECT
97.                 s.date_value,
98.                 s.district_id,
99.                 COUNT(DISTINCT ca.contentassignmentid) assignments_created_count
100.         FROM
101.                 dw.dimcontentassignment ca
102.                 INNER JOIN
103.                         report.dim_organization_user_enrollment ue ON ca.assigneeid=ue.user_id
104.                 INNER JOIN
105.                         school_dates s ON s.school_id = ue.school_id
106.         WHERE
107.                 ca.intpartnerid         ='realize_prod'
108.                 AND ca.createddatetime >= s.date_value
109.                 AND ca.createddatetime  < DATEADD(DAY, 1, s.date_value)
110.         GROUP BY
111.                 s.date_value,
112.                 s.district_id
113.         )
114. SELECT
115.         distinct s.date_value,
116.         o.organizationname                                district_name,
117.         COALESCE(l.login_count, 0)                        login_count,
118.         COALESCE(l.user_count, 0)                         user_count,
119.         COALESCE(cc.classes_created_count, 0)             classes_created_count,
120.         COALESCE(sce.student_class_enrollements_count, 0) student_class_enrollements_count,
121.         COALESCE(assignments_created_count, 0)            user_assignments_created_count,
122.         COALESCE(assi.assignments_completed_count, 0)     user_assignments_completed_count,
123.         COALESCE(ac.assessments_completed_count, 0)       assessments_completed_count
124. FROM
125.         school_dates s
126.         INNER JOIN
127.                 logins l ON s.district_id = l.district_id AND s.date_value = l.date_value
128.         INNER JOIN
129.                 assessments_completed ac ON s.district_id = ac.district_id AND s.date_value = ac.date_value
130.         INNER JOIN
131.                 assignments_completed assi ON s.district_id = assi.district_id AND s.date_value = assi.date_value
132.         INNER JOIN
133.                 student_class_enrollments sce ON s.district_id = sce.district_id AND s.date_value = sce.date_value
134.         INNER JOIN
135.                 classes_created cc ON s.district_id = cc.district_id AND s.date_value = cc.date_value
136.         INNER JOIN
137.                 assignments_created a ON s.district_id = a.district_id AND s.date_value = a.date_value
138.         INNER JOIN
139.                 dw.dimorganization o ON o.organizationid = l.district_id OR organizationid = ac.district_id OR o.organizationid = assi.district_id
140.                 OR o.organizationid = sce.district_id OR o.organizationid = cc.district_id OR o.organizationid = a.district_id
141. ORDER BY
142.         date_value
143.