single-row-per-user-class.sql

WITH activities_1 AS (SELECT *, cardinality(questions) AS num_questions FROM "report-service"."activity_structure" WHERE structure_id = '534002d3-0a86-48ad-8b36-d3c902f08cf0'),

  activities_2 AS (SELECT *, cardinality(questions) AS num_questions FROM "report-service"."activity_structure" WHERE structure_id = '43b612a2-db8c-4f50-987c-e2d4f465f2c5'),

  activities_3 AS (SELECT *, cardinality(questions) AS num_questions FROM "report-service"."activity_structure" WHERE structure_id = '360fe0a4-df2a-4ff0-911b-b4524cf3928b'),

  unique_user_class AS (
SELECT class_id, user_id,
  arbitrary(student_id) as student_id,
  arbitrary(student_name) as student_name,
  arbitrary(username) as username,
  arbitrary(school) as school,
  arbitrary(class) as class,
  arbitrary(permission_forms) as permission_forms,
  -- We could just select arbitrary(teachers) here and then do the transform in the main query
  array_join(transform(arbitrary(teachers), teacher -> teacher.user_id), ',') AS teacher_user_ids,
  array_join(transform(arbitrary(teachers), teacher -> teacher.name), ',') AS teacher_names,
  array_join(transform(arbitrary(teachers), teacher -> teacher.district), ',') AS teacher_districts,
  array_join(transform(arbitrary(teachers), teacher -> teacher.state), ',') AS teacher_states,
  array_join(transform(arbitrary(teachers), teacher -> teacher.email), ',') AS teacher_emails
FROM "report-service"."learners" l
-- I don't think it is necessary to have 3 query_ids here. The query creator could upload all of the
-- learners under a single query_id
WHERE l.query_id IN ('534002d3-0a86-48ad-8b36-d3c902f08cf0', '43b612a2-db8c-4f50-987c-e2d4f465f2c5', '360fe0a4-df2a-4ff0-911b-b4524cf3928b')
GROUP BY class_id, user_id),

  grouped_answers_1 AS (
SELECT l.run_remote_endpoint remote_endpoint, map_agg(a.question_id, a.answer) kv1, map_agg(a.question_id, a.submitted) submitted, map_agg(a.question_id, a.source_key) source_key
FROM "report-service"."partitioned_answers" a
INNER JOIN "report-service"."learners" l
ON (l.query_id = '534002d3-0a86-48ad-8b36-d3c902f08cf0' AND l.run_remote_endpoint = a.remote_endpoint)
WHERE a.escaped_url = 'https---authoring-staging-concord-org-activities-21353'
GROUP BY l.run_remote_endpoint),

  grouped_answers_2 AS (
SELECT l.run_remote_endpoint remote_endpoint, map_agg(a.question_id, a.answer) kv1, map_agg(a.question_id, a.submitted) submitted, map_agg(a.question_id, a.source_key) source_key
FROM "report-service"."partitioned_answers" a
INNER JOIN "report-service"."learners" l
ON (l.query_id = '43b612a2-db8c-4f50-987c-e2d4f465f2c5' AND l.run_remote_endpoint = a.remote_endpoint)
WHERE a.escaped_url = 'https---authoring-staging-concord-org-activities-21354'
GROUP BY l.run_remote_endpoint),

  grouped_answers_3 AS (
SELECT l.run_remote_endpoint remote_endpoint, map_agg(a.question_id, a.answer) kv1, map_agg(a.question_id, a.submitted) submitted, map_agg(a.question_id, a.source_key) source_key
FROM "report-service"."partitioned_answers" a
INNER JOIN "report-service"."learners" l
ON (l.query_id = '360fe0a4-df2a-4ff0-911b-b4524cf3928b' AND l.run_remote_endpoint = a.remote_endpoint)
WHERE a.escaped_url = 'https---authoring-staging-concord-org-activities-21370'
GROUP BY l.run_remote_endpoint),

  learners_and_answers_1 AS ( SELECT run_remote_endpoint remote_endpoint, runnable_url as resource_url, learner_id, student_id, user_id, offering_id, student_name, username, school, class, class_id, permission_forms, last_run, teachers, grouped_answers_1.kv1 kv1, grouped_answers_1.submitted submitted, grouped_answers_1.source_key source_key,
IF (kv1 is null, 0, cardinality(array_intersect(map_keys(kv1),map_keys(activities_1.questions)))) num_answers,
  cardinality(filter(map_values(activities_1.questions), x->x.required=TRUE)) num_required_questions,
IF (submitted is null, 0, cardinality(filter(map_values(submitted), x->x=TRUE))) num_required_answers
FROM activities_1, "report-service"."learners" l
LEFT JOIN grouped_answers_1
ON l.run_remote_endpoint = grouped_answers_1.remote_endpoint
WHERE l.query_id = '534002d3-0a86-48ad-8b36-d3c902f08cf0'),

  learners_and_answers_2 AS ( SELECT run_remote_endpoint remote_endpoint, runnable_url as resource_url, learner_id, student_id, user_id, offering_id, student_name, username, school, class, class_id, permission_forms, last_run, teachers, grouped_answers_2.kv1 kv1, grouped_answers_2.submitted submitted, grouped_answers_2.source_key source_key,
IF (kv1 is null, 0, cardinality(array_intersect(map_keys(kv1),map_keys(activities_2.questions)))) num_answers,
  cardinality(filter(map_values(activities_2.questions), x->x.required=TRUE)) num_required_questions,
IF (submitted is null, 0, cardinality(filter(map_values(submitted), x->x=TRUE))) num_required_answers
FROM activities_2, "report-service"."learners" l
LEFT JOIN grouped_answers_2
ON l.run_remote_endpoint = grouped_answers_2.remote_endpoint
WHERE l.query_id = '43b612a2-db8c-4f50-987c-e2d4f465f2c5'),

  learners_and_answers_3 AS ( SELECT run_remote_endpoint remote_endpoint, runnable_url as resource_url, learner_id, student_id, user_id, offering_id, student_name, username, school, class, class_id, permission_forms, last_run, teachers, grouped_answers_3.kv1 kv1, grouped_answers_3.submitted submitted, grouped_answers_3.source_key source_key,
IF (kv1 is null, 0, cardinality(array_intersect(map_keys(kv1),map_keys(activities_3.questions)))) num_answers,
  cardinality(filter(map_values(activities_3.questions), x->x.required=TRUE)) num_required_questions,
IF (submitted is null, 0, cardinality(filter(map_values(submitted), x->x=TRUE))) num_required_answers
FROM activities_3, "report-service"."learners" l
LEFT JOIN grouped_answers_3
ON l.run_remote_endpoint = grouped_answers_3.remote_endpoint
WHERE l.query_id = '360fe0a4-df2a-4ff0-911b-b4524cf3928b')

SELECT
  unique_user_class.student_id,
  unique_user_class.student_name,
  unique_user_class.username,
  unique_user_class.school,
  unique_user_class.class,
  unique_user_class.permission_forms,
  learners_and_answers_1.remote_endpoint as res_1_remote_endpoint,
  learners_and_answers_2.remote_endpoint as res_2_remote_endpoint,
  learners_and_answers_3.remote_endpoint as res_3_remote_endpoint
FROM activities_1, activities_2,  activities_3, unique_user_class
LEFT JOIN learners_and_answers_1 ON unique_user_class.user_id = learners_and_answers_1.user_id AND unique_user_class.class_id = learners_and_answers_1.class_id
LEFT JOIN learners_and_answers_2 ON unique_user_class.user_id = learners_and_answers_2.user_id AND unique_user_class.class_id = learners_and_answers_2.class_id
LEFT JOIN learners_and_answers_3 ON unique_user_class.user_id = learners_and_answers_3.user_id AND unique_user_class.class_id = learners_and_answers_3.class_id
ORDER BY class NULLS FIRST