Blame for kdd-benchmark/input/task_19/context/knowledge.md

# Enterprise Data Governance Knowledge Guide for Database: student_club

## 1. Introduction
This guide serves as a comprehensive semantic layer for the `student_club` database, providing authoritative definitions, metric formulations, and governance standards. It is designed to ensure consistent data interpretation and usage across the organization, supporting strategic decision-making and operational efficiency.

## 2. Core Entities & Fields

### Members
- **first_name, last_name**: Represents the full name of a club member. Always use both fields to ensure complete identification.
- **position**: Indicates the role of the member within the club (e.g., President, Vice President).
- **major_name**: The academic major associated with the member, linked through the `major` table.
- **t_shirt_size**: Preferred T-shirt size of the member, used for event merchandise planning.

### Events
- **event_name**: The title of the event, used for identification and reporting.
- **event_date**: The date on which the event occurs, formatted as 'YYYY-MM-DD'.
- **type**: Categorizes the event (e.g., Meeting, Fundraiser, Game).
- **status**: Indicates the current state of the event (e.g., Open, Closed).

### Financials
- **amount**: Represents monetary values, such as funds received or budgeted amounts. Ensure currency consistency (e.g., USD).
- **spent**: Total expenditure associated with an event or category.
- **expense_description**: Details the nature of expenses incurred (e.g., Pizza, Posters).

### Locations
- **location**: Venue or place where the event is held.
- **state, county**: Geographic identifiers for member addresses, used for demographic analysis.

## 3. Metric Definitions

### Key Performance Indicators (KPIs)
- **Event Attendance**: `COUNT(link_to_event)` - Measures the number of members attending an event.
- **Budget Utilization**: `DIVIDE(spent, amount)` - Calculates the ratio of spent funds to budgeted amounts.
- **Member Participation**: `COUNT(link_to_event) > X` - Evaluates member engagement based on event attendance thresholds.
- **Fundraising Effectiveness**: `SUM(amount WHERE source = 'Fundraising')` - Assesses total funds raised through fundraising activities.

### Calculation Logic
- **Average Cost**: `AVG(cost)` - Computes the mean expense for specified criteria.
- **Total Expenditure**: `SUM(spent)` - Aggregates all spending within a defined period or category.
- **Yearly Comparison**: `SUBTRACT(SUM(spent WHERE YEAR(event_date) = 2019), SUM(spent WHERE YEAR(event_date) = 2020))` - Compares annual spending across fiscal years.

## 4. Constraints & Conventions

### Filtering Criteria
- **Temporal Boundaries**: Use fiscal year format 'YYYY' for annual comparisons.
- **Currency Formatting**: All monetary values are in USD; ensure consistent representation.
- **Unit Conversion**: Apply standard conversion rates for non-monetary metrics (e.g., attendance counts).

### Common Filters
- **Event Type**: Filter by `type` to isolate specific event categories.
- **Member Position**: Use `position` to segment data by club roles.

## 5. Exemplar Use Cases

### Example 1: Calculating Event Attendance
- **Metric**: Event Attendance
- **SQL**: `SELECT COUNT(link_to_event) FROM attendance WHERE event_id = X`
- **Explanation**: This metric calculates the total number of members who attended a specific event, providing insights into engagement levels.

### Example 2: Assessing Budget Utilization
- **Metric**: Budget Utilization
- **SQL**: `SELECT DIVIDE(spent, amount) FROM budget WHERE event_id = X`
- **Explanation**: This formula evaluates how effectively the allocated budget was used, highlighting potential overspending or savings.

### Example 3: Comparing Yearly Expenditure
- **Metric**: Yearly Comparison
- **SQL**: `SELECT SUBTRACT(SUM(spent WHERE YEAR(event_date) = 2019), SUM(spent WHERE YEAR(event_date) = 2020))`
- **Explanation**: This comparison provides a clear view of spending trends across different fiscal years, aiding in budget planning.

### Example 4: Determining Fundraising Success
- **Metric**: Fundraising Effectiveness
- **SQL**: `SELECT SUM(amount) FROM income WHERE source = 'Fundraising'`
- **Explanation**: This metric quantifies the total funds raised through fundraising activities, essential for evaluating campaign success.

### Example 5: Calculating Average Member Participation
- **Metric**: Member Participation
- **SQL**: `SELECT COUNT(link_to_event) FROM attendance WHERE member_id = X GROUP BY member_id HAVING COUNT(link_to_event) > 7`
- **Explanation**: This formula measures member engagement by counting the number of events attended, identifying highly active members.

## 6. Ambiguity Resolution

### Potentially Ambiguous Fields
- **name**: Always specify whether referring to `event_name` or `member_name` to avoid confusion.
- **amount**: Clarify whether it pertains to `budgeted amount`, `spent amount`, or `funds received`.
- **date**: Distinguish between `event_date`, `expense_date`, and `date_received` for accurate temporal analysis.

### Recommended Usage
- Prioritize context-specific identifiers (e.g., `event_name` for event-related queries) to ensure clarity and prevent misinterpretation.

This guide is intended to be a living document, updated regularly to reflect changes in business processes and data structures. Adherence to these standards will ensure data integrity and facilitate effective governance across the organization.