Overview

Configuration Parameters

• WARNING_THRESHOLD: Percentage drop to trigger inclusion in report (default: 25%)
• CRITICAL_THRESHOLD: Percentage drop to trigger critical alerts (default: 45%)
• OUTPUT_CSV: Path for the generated report (default: bad_student.csv)

Key Instructions

1. Validate Inputs

• Confirm the latest scores CSV file exists and has correct format
• Verify BigQuery dataset exists and contains expected score tables
• Check for available log buckets with exam_log prefix

2. Query Historical Data

• Use a UNION ALL query to combine all historical score tables
• Calculate average score per student across all historical tests
• Handle potential data truncation by querying in batches if needed

3. Perform Analysis

• Join latest scores with historical averages
• Calculate drop percentage: ((avg_score - latest_score) / avg_score) * 100
• Filter students where drop percentage exceeds WARNING_THRESHOLD
• Sort results by drop percentage (descending)

4. Generate Outputs

• Create CSV with columns: student_id, name, avg_score, latest_score, drop_percentage
• Save to specified output path

5. Handle Critical Cases

• For students exceeding CRITICAL_THRESHOLD:
  • Write CRITICAL severity log to the exam_log bucket
  • Include student name and ID in log message
  • Format: "CRITICAL ACADEMIC WARNING: Student [Name] (ID: [ID]) has experienced a severe score drop of [X]% compared to their historical average. Immediate counselor notification required."

Error Handling

• Gracefully handle missing tables or files
• Validate data types and ranges
• Log errors appropriately without exposing sensitive information

Notes

• Historical tables may have prefixes (e.g., scores_2501 instead of just 2501)
• Output CSV should be sorted by severity (highest drop percentage first)
• Only consider logs written during current execution (ignore pre-existing entries)