Toolathlon

academic-warning

Join latest_quiz_scores.csv against seven historical score tables in BigQuery, write every student whose score fell more than 25% into bad_student.csv, and write a CRITICAL Cloud Logging entry for each one who fell more than 45%.

Task Prompt

The academic affairs system requires an automated academic warning mechanism. The latest unit test scores are recorded in the `latest_quiz_scores.csv` file. Please read this file and combine it with all the student scores stored in multiple test score tables (such as 2501, 2502, 2503, 2504, 2505, 2506, and 2507) in the `academic_warning` dataset of BigQuery to identify those students whose test scores have dropped by more than 25% compared to their average scores in the past. Write the list to the  `bad_student.csv`. For those students whose test scores have dropped by more than 45%, please immediately write a critical level warning log to the existing log bucket whose name is prefixed with `exam_log` for each of them with his/her name and student ID so that the system can automatically notify their counselor. During the entire process, there is no need to consider any log entries in the log bucket that existed before the task was executed.