CREATE TEMP FUNCTION CALC_CHURN(ar ARRAY<STRUCT<
  skill_id FLOAT64,
  question_id	FLOAT64,
  total_classes	FLOAT64,
  was_correct	BOOL,
  resp_time	FLOAT64,
  timestamp	TIMESTAMP,
  total_chances	FLOAT64,
  curriculum_grade_num	FLOAT64,
  curriculum_id	FLOAT64,
  curriculum_location_id	FLOAT64,
  current_strand_id	FLOAT64,
  current_theta	FLOAT64,
  algorithm_type	STRING,
  play_location	STRING,
  was_answered_on_mobile	BOOL,
  algorithm_type_version	FLOAT64,
  is_a_member	BOOL,
  question_type	STRING,
  delay_to_hb_toggle	FLOAT64,
  hb_toggled_manually	BOOL>>)
RETURNS ARRAY<STRUCT<
      last_skill_of_session INT64,
      last_question_of_session INT64,
      number_of_class_at_session_end INT64,
      number_of_corrects INT64,
      average_session_response_time FLOAT64,
      curriculum_grade_at_session_end INT64,
      curriculum_id_at_session_end INT64,
      curriculum_location_at_session_end INT64,
      strand_at_session_end INT64,
      theta_at_session_end FLOAT64,
      algorithm_type_at_session_end STRING,
      was_on_mobile_at_session_end BOOL,
      chosen_grade_at_session_end INT64,
      was_member_at_session_end BOOL,
      average_delay_to_hint_on_last_chance FLOAT64,
      number_of_hints_opened_manually_on_last_chance INT64,
      number_of_hints_opened_on_last_chance INT64,
      day_of_week_session_ended INT64,
      month_session_ended INT64,
      grind_duration INT64,
      time_between_grind INT64,
      total_answered_in_session INT64
 >>
LANGUAGE js AS """
sessions = []
sessionStartedTimestamp = ar[0].timestamp
lastTimestamp = ar[0].timestamp
lastChurnDuration = 0

sessionSkillQuestions = []
numberHintBubbleUsedInSession = 0
numberOfCorrectsInSession = 0
numberOfAnswersInSession = 0
cumulativeResponseTime = 0
cumulativeTotalChances = 0
cumulativeDelayToHintOnLastChance = 0
numberTimesHintBubbleOpenedManuallyOnLastChance = 0
numberTimesHintWasOpenedOnLastChance = 0
for (let inc = 0; inc < ar.length; inc++) {
  current_answer = ar[inc]
  churnDuration = current_answer.timestamp - lastTimestamp

  if (churnDuration >= (60 * 60000) || inc + 1 === ar.length) {
    session_ended_answer = ar[inc-1]
    sessions.push({
      last_skill_of_session: session_ended_answer.skill_id,
      last_question_of_session: session_ended_answer.question_id,
      number_of_class_at_session_end: session_ended_answer.total_classes,
      number_of_corrects: numberOfCorrectsInSession,
      cumulative_response_time: cumulativeResponseTime,
      average_session_response_time: cumulativeResponseTime*1.0/cumulativeTotalChances,
      curriculum_grade_at_session_end: session_ended_answer.curriculum_grade_num,
      curriculum_id_at_session_end: session_ended_answer.curriculum_id,
      curriculum_location_at_session_end: session_ended_answer.curriculum_location_id,
      strand_at_session_end: session_ended_answer.current_strand_id,
      theta_at_session_end: session_ended_answer.current_theta,
      algorithm_type_at_session_end: session_ended_answer.algorithm_type,
      was_on_mobile_at_session_end: session_ended_answer.was_answered_on_mobile,
      chosen_grade_at_session_end: session_ended_answer.chosen_grade,
      was_member_at_session_end: session_ended_answer.is_a_member,
      average_delay_to_hint_on_last_chance: cumulativeDelayToHintOnLastChance*1.0/numberTimesHintWasOpenedOnLastChance,
      number_of_hints_opened_manually_on_last_chance: numberTimesHintBubbleOpenedManuallyOnLastChance,
      number_of_hints_opened_on_last_chance: numberTimesHintWasOpenedOnLastChance,
      day_of_week_session_ended: session_ended_answer.timestamp.getDay(),
      month_session_ended: session_ended_answer.timestamp.getMonth(),
      skill_questions: JSON.stringify(sessionSkillQuestions),
      grind_duration: lastTimestamp-sessionStartedTimestamp,
      time_between_grind: lastChurnDuration,
      total_answered_in_session: numberOfAnswersInSession
    })
    sessionStartedTimestamp = current_answer.timestamp
    lastChurnDuration = churnDuration

    sessionSkillQuestions = []
    numberHintBubbleUsedInSession = 0
    numberOfCorrectsInSession = 0
    numberOfAnswersInSession = 0
    cumulativeResponseTime = 0
    cumulativeTotalChances = 0
    cumulativeDelayToHintOnLastChance = 0
    numberTimesHintBubbleOpenedManuallyOnLastChance = 0
    numberTimesHintWasOpenedOnLastChance = 0
  }

  numberTimesHintWasOpenedOnLastChance += current_answer.delay_to_hb_toggle != null ? 1 : 0
  numberTimesHintBubbleOpenedManuallyOnLastChance += current_answer.hb_toggled_manually ? 1 : 0
  cumulativeDelayToHintOnLastChance += current_answer.delay_to_hb_toggle || 0
  cumulativeTotalChances += current_answer.total_chances
  cumulativeResponseTime += current_answer.resp_time
  numberOfCorrectsInSession += current_answer.was_correct ? 1 : 0;
  numberOfAnswersInSession += 1
  sessionSkillQuestions.push({skill_id: current_answer.skill_id, question_id: current_answer.question_id})
  lastTimestamp = current_answer.timestamp
}
return sessions;
""";

SELECT user_id, CALC_CHURN(
  ARRAY_AGG(
    (
      CAST(skill_id AS FLOAT64),
      CAST(question_id AS FLOAT64),
      CAST(total_classes AS FLOAT64),
      was_correct,
      CAST(resp_time AS FLOAT64),
      timestamp,
      CAST(total_chances AS FLOAT64),
      CAST(curriculum_grade_num AS FLOAT64),
      CAST(curriculum_id AS FLOAT64),
      CAST(curriculum_location_id AS FLOAT64),
      CAST(current_strand_id AS FLOAT64),
      current_theta,
      algorithm_type,
      play_location,
      was_answered_on_mobile,
      algorithm_type_version,
      is_a_member,
      question_type,
      CAST(delay_to_hb_toggle AS FLOAT64),
      hb_toggled_manually
    )
  ORDER BY timestamp)) AS sessions
FROM educational_data.answers
GROUP BY user_id
HAVING COUNT(*)>10