WITH
	school_dates AS
	( SELECT
		d.date_id,
		d.date_value,
		child_organization_id AS school_id,
		parent_organization_id AS district_id
	FROM
		report.dim_date d,
		report.organization_hierarchy
	WHERE
	    child_organization_type  = 'DISTRICT' and parent_organization_type  = 'DISTRICT'
		AND d.date_id               > 20190801 --start date
		AND d.date_value            < DATE_TRUNC('day', GETDATE())
	)
	,
	logins AS
	( SELECT
		s.date_value,
		s.district_id,
		SUM(measure_login_count) login_count,
		COUNT(DISTINCT user_id)  user_count
	FROM
		school_dates s
		INNER JOIN
			report.summary_logins l ON s.school_id = l.school_id AND s.date_id = l.date_id
	GROUP BY
		s.date_value,
		s.district_id
	)
	,
	assessments_completed AS
	( SELECT
		s.date_value,
		s.district_id,
		COUNT(DISTINCT userassignment_id) assessments_completed_count
	FROM
		school_dates s
		INNER JOIN
			report.fact_content_scores cs ON s.school_id = cs.school_id AND s.date_id = cs.date_id AND content_type = 'TEST'
	GROUP BY
		s.date_value,
		s.district_id
	)
	,
	assignments_completed AS
	( SELECT
		s.date_value,
		s.district_id,
		COUNT(DISTINCT userassignment_id) assignments_completed_count
	FROM
		school_dates s
		INNER JOIN
			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
	GROUP BY
		s.date_value,
		s.district_id
	)
	,
	student_class_enrollments AS
	( SELECT
		s.date_value,
		s.district_id,
		COUNT(*) student_class_enrollements_count
	FROM
		school_dates s
		INNER JOIN
			--using the record insert date time since we don't have a clear enrollment creation date time in the table
			report.dim_class_student_enrollment se ON s.school_id = se.school_id 
			AND se.insert_datetime >= s.date_value
			AND se.insert_datetime  < DATEADD(DAY, 1, s.date_value)
	GROUP BY
		s.date_value,
		s.district_id
	)
	,
	classes_created AS
	( SELECT
		s.date_value,
		s.district_id,
		COUNT(*) classes_created_count
	FROM
		school_dates s
		INNER JOIN
			dw.dimclass c ON s.school_id = c.organizationid
			AND c.createddate >= s.date_value
			AND c.createddate  < DATEADD(DAY, 1, s.date_value)
	WHERE
		c.operation = 'Create'
	GROUP BY
		s.date_value,
		s.district_id
	)
	,
	assignments_created AS
	( SELECT
		s.date_value,
		s.district_id,
		COUNT(DISTINCT ca.contentassignmentid) assignments_created_count
	FROM
		dw.dimcontentassignment ca
		INNER JOIN
			report.dim_organization_user_enrollment ue ON ca.assigneeid=ue.user_id
		INNER JOIN
			school_dates s ON s.school_id = ue.school_id
	WHERE
		ca.intpartnerid         ='realize_prod'
		AND ca.createddatetime >= s.date_value
		AND ca.createddatetime  < DATEADD(DAY, 1, s.date_value)
	GROUP BY
		s.date_value,
		s.district_id
	)
SELECT
	distinct s.date_value,
	o.organizationname                                district_name,
	COALESCE(l.login_count, 0)                        login_count,
	COALESCE(l.user_count, 0)                         user_count,
	COALESCE(cc.classes_created_count, 0)             classes_created_count,
	COALESCE(sce.student_class_enrollements_count, 0) student_class_enrollements_count,
	COALESCE(assignments_created_count, 0)            user_assignments_created_count,
	COALESCE(assi.assignments_completed_count, 0)     user_assignments_completed_count,
	COALESCE(ac.assessments_completed_count, 0)       assessments_completed_count
FROM
	school_dates s
	INNER JOIN
		logins l ON s.district_id = l.district_id AND s.date_value = l.date_value
	INNER JOIN
		assessments_completed ac ON s.district_id = ac.district_id AND s.date_value = ac.date_value
	INNER JOIN
		assignments_completed assi ON s.district_id = assi.district_id AND s.date_value = assi.date_value
	INNER JOIN
		student_class_enrollments sce ON s.district_id = sce.district_id AND s.date_value = sce.date_value
	INNER JOIN
		classes_created cc ON s.district_id = cc.district_id AND s.date_value = cc.date_value
	INNER JOIN
		assignments_created a ON s.district_id = a.district_id AND s.date_value = a.date_value
	INNER JOIN
		dw.dimorganization o ON o.organizationid = l.district_id OR organizationid = ac.district_id OR o.organizationid = assi.district_id 
		OR o.organizationid = sce.district_id OR o.organizationid = cc.district_id OR o.organizationid = a.district_id
ORDER BY
	date_value