financial-analytics

name: financial-analytics description: Analyze tuition payments and revenue, outstanding balances and payment status, scholarship awards and distribution, payment methods and transaction patterns, financial aid and coverage rates, net tuition after scholarships, and semester-wise financial breakdowns. Use when user asks about tuition, payments, scholarships, financial aid, or student finances.

Quick Start Guide

Before writing ANY SQL query:

1. Read metadata files (MANDATORY):

   • data/metadata/financial_summary_by_student.yaml
   • data/metadata/financial_summary_by_student_sample_data.csv

2. Identify query pattern from user's question:

   • "How many..." → Pattern 1 (Simple Counting)
   • "...for each [dimension]" → Pattern 2 (Aggregation by Dimension)
   • "Which majors..." → Pattern 3 (Ranking Dimensions)
   • "Top N students..." → Pattern 4 (Top N)
   • "Breakdown by..." → Pattern 5 (Distribution)

3. Apply Default Assumptions - Filter for active students unless explicitly stated otherwise

4. Check Standard Metric Definitions for ambiguous terms like "financial support"

5. Apply pattern guidelines (see Query Pattern Guidelines section)

6. Verify with Result Validation Checklist before executing

Default Assumptions (READ THIS FIRST)

⚠️ CRITICAL: These defaults apply to ALL queries unless user explicitly states otherwise:

Student Population Rule

Default: ALWAYS filter for Active students

Apply this filter to ALL query patterns (1-5) by default:

• Pattern 1 (Counting): "How many students have X?" → Filter Active students
• Pattern 2 (Aggregation): "Average Y for each major" → Filter Active students
• Pattern 3 (Ranking): "Which majors receive most X?" → Filter Active students
• Pattern 4 (Top N): Consider context - usually Active students
• Pattern 5 (Breakdown): "Breakdown by payment method" → Filter Active students

Only skip this filter when user explicitly says:

• "all students including graduated"
• "all students regardless of status"
• "historical data for all students"

Present tense verbs indicate current/active students:

• "How many students have outstanding balances?" → Active students
• "Which majors receive most support?" → Active students
• "Show me students with high GPA" → Active students

General Rules:

• CRITICAL: When generating SQL query, you should focus on answering user's question, rather than over-analyzing things you are not asked for.
• CRITICAL: Use Query Pattern Guidelines (1-5) for consistent approach
• CRITICAL: For ambiguous metrics like "financial support", include multiple related metrics (total, average, coverage)
• DO NOT sort by id columns
• DO NOT select unrequested columns
• Use ROUND(AVG(), 1) for averages operation
• Use ROUND(SUM(), 2) for dollar amounts when appropriate

Financial Analytics

Primary Table

financial_summary_by_student

• Metadata: data/metadata/financial_summary_by_student.yaml
• Sample data: data/metadata/financial_summary_by_student_sample_data.csv

CRITICAL: You MUST read BOTH files before writing ANY SQL query:

1. Read data/metadata/financial_summary_by_student.yaml for complete schema
2. Read data/metadata/financial_summary_by_student_sample_data.csv for actual data examples

Table Structure

One Record Per Student:

• Each row represents ONE student's complete financial summary
• All metrics are pre-aggregated at the student level
• No need to use COUNT(DISTINCT student_id) - simple COUNT(*) counts students
• Student count = Row count (when properly filtered)

Key Column Prefixes:

• student_* - Student information (id, name, major, GPA, status, enrollment date)
• student_department_* - Department for student's major
• total_* - Pre-aggregated totals (tuition due, paid, scholarships)
• *_scholarships_total - Breakdown by scholarship type
• last_payment_* - Most recent payment details
• Boolean flags: has_outstanding_balance, is_scholarship_recipient

⚠️ IMPORTANT - Columns That DON'T EXIST:

• ❌ credit_card_payments_total, bank_transfer_total, check_payments_total, financial_aid_total
• ❌ total_payments_count, completed_payments_count, pending_payments_count, failed_payments_count
• ❌ payment_success_rate_pct, avg_payment_amount
• ❌ fall_2024_tuition, fall_2024_scholarships, spring_2024_tuition, etc.
• ❌ is_payment_plan_active, is_financial_aid_recipient
• ✅ ONLY last_payment_method exists (categorical: 'Credit Card', 'Bank Transfer', 'Check', 'Financial Aid')

Natural Language to SQL Mappings

⚠️ CRITICAL: "Current Students" vs "All Students"

When user asks about current/active students, ALWAYS filter:

WHERE student_status = 'Active'

Triggers:

• "currently enrolled students"
• "active students"
• "students with outstanding balances" (implies current)
• "how many students have..." (present tense = current)
• "which students..." (without past context = current)

Exception: Only include ALL statuses when explicitly asked:

• "all students including graduated"
• "historical data"
• "students who have ever..."

⚠️ CRITICAL: Standard Metric Definitions

When user asks about "financial support" or "scholarships", use these standard metrics:

Default Behavior for Ambiguous Queries:

• "financial support" without qualifier → Include ALL THREE metrics in the query
• "most financial support" → Order by total dollars (SUM) but include average for context
• Always include COUNT(*) as student_count to show population size

⚠️ CRITICAL: Ambiguous Terms

Student Status Values

Scholarship Coverage Thresholds

Outstanding Balance Patterns

Aggregation Patterns

Query Pattern Guidelines

Identify the query pattern based on user's question structure:

Pattern 1: Simple Counting

Trigger: "How many students [condition]?" Approach:

• Use COUNT(*) to count students
• Include WHERE student_status = 'Active' by default
• Apply the condition filter

Pattern 2: Aggregation by Dimension

Trigger: "[metric] for each [dimension]" OR "[metric] by [dimension]" Approach:

• ALWAYS filter for active students first using the default student status rule (unless user explicitly says "all students")
• SELECT the dimension column
• Apply appropriate aggregation (AVG, SUM) with ROUND()
• Include COUNT(*) as student_count for context
• GROUP BY the dimension
• ORDER BY the metric (DESC for "most/highest", ASC for "least/lowest")

Examples:

• "average scholarship coverage rate for each major" → Filter Active students + GROUP BY student_major
• "total revenue by department" → Filter Active students + GROUP BY department
• "payment distribution for each major" → Filter Active students + GROUP BY student_major

Pattern 3: Ranking Dimensions (Ambiguous Metrics)

Trigger: "Which [dimension] [verb] the most [metric]?" (e.g., "Which majors receive most financial support?") Approach:

• SELECT the dimension (e.g., student_major)
• Include COUNT(*) as student_count
• For ambiguous terms like "financial support", include ALL THREE metrics:
  • SUM(total_scholarships_received) as total
  • AVG(total_scholarships_received) as average per student
  • AVG(scholarship_coverage_rate_pct) as coverage rate
• GROUP BY dimension
• ORDER BY the primary interpretation (total for "most", average for "average")
• Filter active students by default

Key Point: This pattern addresses ambiguity by providing multiple metrics rather than guessing which one.

Pattern 4: Top N Individual Records

Trigger: "Top N students by [metric]" OR "Show me [N] students with [condition]" Approach:

• SELECT student identifiers (student_id, student_name)
• SELECT the ranking metric
• SELECT ONLY explicitly requested additional columns (e.g., "along with GPA" → include student_gpa)
• ORDER BY ranking metric DESC
• LIMIT N
• Consider whether to filter Active:
  • Include filter for "top students with highest balance" (current context)
  • Omit filter if user wants to see enrollment_status variety

Key Point: Minimize column selection - only include what user explicitly requested.

Pattern 5: Breakdown/Distribution

Trigger: "Breakdown by [dimension]" OR "Distribution of [dimension]" Approach:

• ALWAYS filter for active students first using the default student status rule (unless user explicitly says "all students")
• SELECT the dimension
• Include COUNT(*) as student_count
• Include percentage calculation: ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) as percentage
• GROUP BY dimension
• ORDER BY student_count DESC

Examples:

• "breakdown of students by payment method" → Filter Active students + GROUP BY last_payment_method
• "distribution of majors" → Filter Active students + GROUP BY student_major

Column Selection Guidelines

CRITICAL: Only SELECT columns that are:

1. Grouping dimensions (e.g., student_major when GROUP BY student_major)
2. Explicitly requested by user (e.g., "along with their GPA" → include student_gpa)
3. Calculated metrics (aggregations like COUNT, AVG, SUM)
4. Context count (COUNT(*) as student_count in GROUP BY queries)

DO NOT include:

• ❌ Unrequested additional context columns
• ❌ Extra scholarship breakdowns unless asked
• ❌ All possible metrics "just in case"
• ❌ Department info unless specifically asked

Examples of column selection:

• User: "average scholarship by major" → SELECT: student_major, AVG(total_scholarships_received), COUNT(*)
• User: "top 10 students by GPA" → SELECT: student_id, student_name, student_gpa (no other columns)
• User: "top 10 by GPA along with their major" → SELECT: student_id, student_name, student_gpa, student_major
• User: "which majors receive most support" → SELECT: student_major, SUM, AVG, coverage rate, COUNT (multiple metrics for ambiguous "support")

Payment Method Limitations

⚠️ CRITICAL: ONLY last_payment_method exists (categorical field)

When user asks about payment method totals:

• Explain limitation: "Only last payment method is available, not totals by method"
• Offer alternative: "I can show you student count and average payment amount by last payment method"

Data Quality Notes

• One row per student: Simple COUNT(*) counts students (no DISTINCT needed)
• All metrics pre-aggregated: Scholarships, payments already summed per student
• Boolean flags: Use has_outstanding_balance, is_scholarship_recipient for filtering
• Coverage rate can exceed 100%: Some students receive scholarships > tuition paid
• No temporal data: No semester breakdowns, only aggregated totals

Semantic Keywords Reference

Use this table to identify query intent and select the correct pattern:

Result Validation Checklist

Before executing SQL, verify these items:

Filtering & Data Scope

• ⚠️ CRITICAL: Applied default student status filter - Used Active students filter UNLESS user explicitly requested "all students"
• Did NOT filter Active when user asks about "enrollment status" variety or distribution by status
• Used is_scholarship_recipient = true when analyzing only scholarship recipients
• Applied correct threshold for outstanding balances (e.g., > 3000 not > 1000)

Query Pattern Compliance

• Matched query to correct Standard Query Pattern (1-5)
• Followed template structure exactly for chosen pattern
• Included COUNT(*) as student_count in GROUP BY queries for context
• Used LIMIT N for "top N" queries

Column Selection

• Selected ONLY explicitly requested columns
• Did NOT add unrequested context columns or extra metrics
• Included grouping dimension in SELECT when using GROUP BY
• Used appropriate column for requested attribute (e.g., student_gpa not cumulative_gpa)

Ambiguous Terms Handling

• For "financial support" queries: included total, average, AND coverage rate metrics
• Ordered by primary metric (total dollars for "most")
• Used correct metric definition from Standard Metrics table

SQL Syntax

• Used ROUND(AVG(), 1) for averages
• Used ROUND(SUM(), 2) for dollar amounts when needed
• Did NOT sort by ID columns (student_id, department_id, etc.)
• Did NOT use COUNT(DISTINCT student_id) (unnecessary - one row per student)
• Used percentage calculation: ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) for breakdowns

Column Existence

• Did NOT reference non-existent columns:
  • Payment method totals (credit_card_payments_total, etc.)
  • Payment counts (total_payments_count, etc.)
  • Semester fields (fall_2024_tuition, etc.)
• Used ONLY last_payment_method for payment method analysis (categorical only)

Edge Cases

• Handled NULL values appropriately
• Considered that scholarship_coverage_rate_pct can exceed 100%
• Used window function for percentages in breakdown queries

Common Mistakes to Avoid

Query Pattern Violations

❌ DON'T:

• Write ad-hoc queries without considering Pattern Guidelines (1-5)
• Provide only single metric for ambiguous terms like "financial support"
• Add unrequested columns

✅ DO:

• Identify which Pattern (1-5) best matches the user's question
• Follow the pattern approach guidelines consistently
• For ambiguous metrics, include multiple related metrics (total, average, coverage)

Column Selection Errors

❌ DON'T over-select columns:

• Including all scholarship breakdowns when user asks for "average scholarship"
• Adding context columns not requested (e.g., department when not asked)
• Selecting all possible metrics "just in case"

✅ DO:

• Select ONLY: grouping columns + requested metrics + student_count
• Add columns ONLY when explicitly requested by user
• For ambiguous terms: include multiple metrics of SAME concept (total, average, coverage for "financial support")

Non-Existent Columns

❌ DON'T reference columns that don't exist:

• credit_card_payments_total, bank_transfer_total, check_payments_total
• payment_success_rate_pct, total_payments_count, avg_payment_amount
• fall_2024_tuition, spring_2024_tuition (no semester breakdowns)
• is_payment_plan_active, is_financial_aid_recipient

✅ DO:

• Use ONLY last_payment_method (categorical) for payment method analysis
• Use pre-aggregated totals: total_tuition_paid, total_scholarships_received
• Check metadata files before using any column

Filtering Mistakes

❌ DON'T:

• Forget student_status = 'Active' when user asks about "current students"
• Filter Active when user wants to see "enrollment status" variety
• Use wrong threshold (e.g., > 1000 when user said > 3000)

✅ DO:

• Always filter Active for present-tense queries ("how many students have...")
• Omit status filter when showing status as a dimension
• Use exact thresholds specified by user

Aggregation Mistakes

❌ DON'T:

• Use COUNT(DISTINCT student_id) (unnecessary - one row per student)
• Provide only SUM OR AVG for ambiguous "financial support" queries
• Sort by ID columns (student_id, department_id)

✅ DO:

• Use simple COUNT(*) to count students
• Include multiple metrics (total, average, coverage) for ambiguous terms
• Order by meaningful metrics, not IDs

Consistency Guidelines

These pattern guidelines were validated through consistency testing. Following them ensures:

• ✅ Consistent SQL approach across similar queries
• ✅ Predictable results for similar questions
• ✅ Reduced ambiguity in metric interpretation
• ✅ Standard handling of ambiguous terms

Reference: See consistency_analysis_Jan_27__10_13/ for detailed analysis that informed these guidelines.

Visualizations

• Pie Chart: Payment method distribution (last payment), student status distribution
• Bar Chart: Revenue by department, scholarship by major, outstanding balances by major
• Stacked Bar Chart: Scholarship types by major
• Histogram: Outstanding balance distribution, scholarship coverage rate distribution
• Scatter Plot: GPA vs scholarship amount, GPA vs coverage rate