# MASTER TEMPLATE: Examples by Category
# Comprehensive examples for each question type

## A. EARNINGS/INCOME QUESTIONS

### ✅ CORRECT EXAMPLES

Question: "When was the last time income was earned?"
Decomposition: General earnings question
SQL: SELECT MAX(date) as last_earning_date FROM earning
Reason: Looking for most recent earning date, not specific person

Question: "Can you tell when was the last time agent earned the income"
Decomposition: General earnings question
SQL: SELECT MAX(date) as last_earning_date FROM earning
Reason: "agent earned income" = general earnings, not person search

Question: "Show me earnings for the last 3 months"
Decomposition: Time-based earnings query
SQL: SELECT date, amount FROM earning WHERE date >= DATE_SUB(NOW(), INTERVAL 3 MONTH) ORDER BY date DESC
Reason: Time-based data retrieval from earnings table

Question: "Get earnings grouped by month"
Decomposition: Aggregation earnings query
SQL: SELECT CONCAT(YEAR(date), '-', LPAD(MONTH(date), 2, '0')) as month, SUM(amount) as total_earnings FROM earning GROUP BY CONCAT(YEAR(date), '-', LPAD(MONTH(date), 2, '0')) ORDER BY month
Reason: Aggregating earnings data by time period

### ❌ INCORRECT EXAMPLES

Question: "When was the last earning?"
WRONG: SELECT MAX(date) FROM income WHERE agent_id = 'specific_agent'
CORRECT: SELECT MAX(date) FROM earning
Reason: Wrong table ('income' instead of 'earning'), non-existent column ('agent_id')

Question: "agent earned the income"
WRONG: SELECT * FROM earning WHERE name = 'agent'
CORRECT: SELECT MAX(date) FROM earning
Reason: Over-interpretation - not looking for person named "agent"

## B. PERSON/AGENT SEARCH QUESTIONS

### ✅ CORRECT EXAMPLES

Question: "Find agent named John Smith"
Decomposition: Person search
SQL: SELECT * FROM member WHERE name LIKE '%John Smith%'
Reason: Looking for specific person in member table

Question: "Search for agent with email john@example.com"
Decomposition: Person search by email
SQL: SELECT * FROM member WHERE email = 'john@example.com'
Reason: Person lookup using email field

Question: "Who is the top earning agent?"
Decomposition: Person-based earnings analysis
SQL: SELECT name, SUM(amount) as total_earnings FROM earning GROUP BY name ORDER BY total_earnings DESC LIMIT 1
Reason: Finding person with highest earnings using earning table with GROUP BY

### ❌ INCORRECT EXAMPLES

Question: "Find agent John"
WRONG: SELECT * FROM earning WHERE name = 'John'
CORRECT: SELECT * FROM member WHERE name LIKE '%John%'
Reason: Person search should use member table, not earning table

Question: "Search for agent"
WRONG: SELECT agent_id, agent_name FROM member
CORRECT: SELECT id, name FROM member
Reason: Non-existent columns ('agent_id', 'agent_name')

## C. COMPARISON/ANALYSIS QUESTIONS

### ✅ CORRECT EXAMPLES

Question: "Who improved their earnings the most compared to last month?"
Decomposition: Multi-step comparison
Step 1: SELECT name, SUM(amount) as current_earnings FROM earning WHERE MONTH(date) = MONTH(NOW()) GROUP BY name
Step 2: SELECT name, SUM(amount) as last_month_earnings FROM earning WHERE MONTH(date) = MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH)) GROUP BY name
Step 3: ANALYSIS STEP - Calculate improvement percentages
Reason: Comparing data across time periods

Question: "Compare earnings between months"
Decomposition: Time-based comparison
SQL: SELECT CONCAT(YEAR(date), '-', LPAD(MONTH(date), 2, '0')) as month, SUM(amount) as total_earnings FROM earning GROUP BY CONCAT(YEAR(date), '-', LPAD(MONTH(date), 2, '0')) ORDER BY month
Reason: Aggregating and comparing by time period

### ❌ INCORRECT EXAMPLES

Question: "Compare earnings"
WRONG: SELECT * FROM earning WHERE date = 'current' vs SELECT * FROM earning WHERE date = 'previous'
CORRECT: Use proper date functions and GROUP BY
Reason: Vague date references, missing aggregation

## D. TIME-BASED QUESTIONS

### ✅ CORRECT EXAMPLES

Question: "Show me earnings by month for this year"
Decomposition: Time-based aggregation
SQL: SELECT CONCAT(YEAR(date), '-', LPAD(MONTH(date), 2, '0')) as month, SUM(amount) as total_earnings FROM earning WHERE YEAR(date) = YEAR(NOW()) GROUP BY CONCAT(YEAR(date), '-', LPAD(MONTH(date), 2, '0')) ORDER BY month
Reason: Time-based grouping with proper date functions

Question: "Last 3 months earnings"
Decomposition: Time-based retrieval
SQL: SELECT date, amount FROM earning WHERE date >= DATE_SUB(NOW(), INTERVAL 3 MONTH) ORDER BY date DESC
Reason: Time-based filtering with proper date functions

### ❌ INCORRECT EXAMPLES

Question: "Recent earnings"
WRONG: SELECT * FROM earning WHERE date = 'recent'
CORRECT: SELECT * FROM earning WHERE date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
Reason: Vague time reference, should use specific date functions

## E. AGGREGATION QUESTIONS

### ✅ CORRECT EXAMPLES

Question: "What is the total earnings this month?"
Decomposition: Aggregation query
SQL: SELECT SUM(amount) as total_earnings FROM earning WHERE MONTH(date) = MONTH(NOW()) AND YEAR(date) = YEAR(NOW())
Reason: Summing data with proper time filtering

Question: "Average earnings per agent"
Decomposition: Aggregation with grouping
SQL: SELECT name, AVG(amount) as avg_earnings FROM earning GROUP BY name
Reason: Aggregating by person with proper grouping

### ❌ INCORRECT EXAMPLES

Question: "Total earnings"
WRONG: SELECT total FROM earning
CORRECT: SELECT SUM(amount) as total_earnings FROM earning
Reason: Non-existent column, should use aggregation function

## F. SIMPLE/GREETING QUESTIONS

### ✅ CORRECT EXAMPLES

Question: "Hi"
Response: "SIMPLE"
Reason: No data analysis needed

Question: "How are you?"
Response: "SIMPLE"
Reason: Casual conversation

Question: "What is this system?"
Response: "SIMPLE"
Reason: General explanation, not data query

### ❌ INCORRECT EXAMPLES

Question: "Hi"
WRONG: Attempt to decompose into data analysis steps
CORRECT: Return "SIMPLE"
Reason: Should not try to analyze simple greetings

## COMMON MISTAKE PATTERNS

### Pattern 1: Over-interpretation
❌ WRONG: "agent earned income" → WHERE name = 'agent'
✅ CORRECT: "agent earned income" → general earnings question

### Pattern 2: Wrong Table Usage
❌ WRONG: Person search using earning table
✅ CORRECT: Person search using member table

### Pattern 3: Non-existent Columns
❌ WRONG: agent_id, income_date, agent_name
✅ CORRECT: name, date, amount

### Pattern 4: Vague Time References
❌ WRONG: "recent", "current", "previous"
✅ CORRECT: Use specific date functions

### Pattern 5: Spaces in Aliases
❌ WRONG: "Total Earnings", "Last Date"
✅ CORRECT: total_earnings, last_date

## VALIDATION CHECKLIST

### For Each Example:
1. ✅ Question type correctly identified
2. ✅ Correct table selected
3. ✅ Valid columns used
4. ✅ Proper SQL syntax
5. ✅ Correct aliases (underscores)
6. ✅ Appropriate date functions
7. ✅ Logical decomposition steps

### Common Validation Points:
- EARNINGS questions → earning table
- PERSON questions → member table
- No non-existent columns
- Proper date function usage
- Underscores in aliases
- Logical question interpretation