Academic Performance Analyzer

---
name: academic-performance-analyzer
description: When the user needs to monitor student academic performance by comparing current test scores against historical averages to identify significant performance declines. This skill reads CSV files containing latest scores, queries historical data from BigQuery tables, calculates percentage drops, identifies at-risk students based on configurable thresholds (e.g., >25% drop), and generates comprehensive reports. Triggers include requests for academic warning systems, student performance analysis, score trend monitoring, or identifying students needing intervention.
---
# Academic Performance Analyzer

## Overview
This skill automates the detection of significant declines in student academic performance by comparing the latest test scores against their historical averages. It identifies students requiring academic intervention and can trigger immediate counselor notifications for critical cases.

## Core Workflow
1. **Input Processing**: Read the latest student scores from a CSV file.
2. **Historical Analysis**: Query and aggregate historical test scores from multiple BigQuery tables.
3. **Performance Calculation**: Compute the percentage drop between historical average and latest score for each student.
4. **Threshold Filtering**: Identify students exceeding warning thresholds (configurable).
5. **Output Generation**: Create a CSV report of at-risk students.
6. **Critical Alerting**: Write critical warning logs for students exceeding severe thresholds.

## Required Inputs
- **Latest Scores CSV**: Path to a CSV file with columns: `student_id`, `name`, `score`
- **BigQuery Dataset**: Dataset containing historical score tables (e.g., `scores_2501`, `scores_2502`, etc.)
- **Log Bucket**: Cloud Logging bucket with name prefixed with `exam_log` for critical alerts

## 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)