QUESTION DEFINITIONS TEMPLATE
============================

This template defines what each question means, expected SQL, and result presentation format.

## DATABASE SCHEMA

### EARNING TABLE STRUCTURE:
- `id`: Primary key
- `name`: Agent name
- `amount`: Earnings amount
- `type`: Income type (stored as string)
  - `'primary_agent_commission'`: Primary agent commission
  - `'revenue_share'`: Revenue share earnings
- `date`: Date of earning
- `description`: Description of the earning
- `agent_id`: Agent ID reference

### MEMBER TABLE STRUCTURE:
- `id`: Agent ID
- `name`: Agent name
- `email`: Agent email
- `phone`: Agent phone number
- `referrer_id`: ID of referring agent (for downline tracking)

### TRANSACTION TABLE STRUCTURE:
- `id`: Transaction ID
- `agent_id`: Agent who completed the transaction
- `amount`: Transaction amount
- `date`: Transaction date

## QUESTION CATEGORIES

### A. PERFORMANCE QUESTIONS

Question: "Who is the top performer?"
Definition: Find the agent with the highest total earnings (primary agent commission + revenue share) across all time
Expected SQL: SELECT name, SUM(amount) as total_earnings FROM earning GROUP BY name ORDER BY total_earnings DESC LIMIT 1
Result Format: 
- Title: "🏆 Top Performer"
- Subtitle: "Agent with highest total earnings (primary agent commission + revenue share)"
- Display: Agent name, total earnings amount
- Additional: Show performance metrics if available

Question: "Who is the 3rd top performer?"
Definition: Find the 3rd highest earning agent (primary agent commission + revenue share) across all time
Expected SQL: SELECT name, SUM(amount) as total_earnings FROM earning GROUP BY name ORDER BY total_earnings DESC LIMIT 1 OFFSET 2
Result Format:
- Title: "🏆 3rd Top Performer"
- Subtitle: "3rd highest earning agent (primary agent commission + revenue share)"
- Display: Agent name, total earnings amount

Question: "Who is the top performer this month?"
Definition: Find the agent with the highest earnings (primary agent commission + revenue share) in the current month
Expected SQL: SELECT name, SUM(amount) as monthly_earnings FROM earning WHERE MONTH(date) = MONTH(NOW()) AND YEAR(date) = YEAR(NOW()) GROUP BY name ORDER BY monthly_earnings DESC LIMIT 1
Result Format:
- Title: "🏆 Top Performer This Month"
- Subtitle: "Agent with highest earnings in [Month Year]"
- Display: Agent name, monthly earnings amount

Question: "Top 5th performer in july 2025"
Definition: Find the 5th highest earning agent (primary agent commission + revenue share) in July 2025
Expected SQL: SELECT name, SUM(amount) as monthly_earnings FROM earning WHERE MONTH(date) = 7 AND YEAR(date) = 2025 GROUP BY name ORDER BY monthly_earnings DESC LIMIT 1 OFFSET 4
Result Format:
- Title: "🏆 5th Top Performer - July 2025"
- Subtitle: "5th highest earning agent in July 2025"
- Display: Agent name, monthly earnings amount

Question: "Top 10th performer in June 2025"
Definition: Find the 10th highest earning agent (primary agent commission + revenue share) in June 2025
Expected SQL: SELECT name, SUM(amount) as monthly_earnings FROM earning WHERE MONTH(date) = 6 AND YEAR(date) = 2025 GROUP BY name ORDER BY monthly_earnings DESC LIMIT 1 OFFSET 9
Result Format:
- Title: "🏆 10th Top Performer - June 2025"
- Subtitle: "10th highest earning agent in June 2025"
- Display: Agent name, monthly earnings amount

Question: "Who is the least performer?"
Definition: Find the agent with the lowest total earnings (primary agent commission + revenue share) across all time
Expected SQL: SELECT name, SUM(amount) as total_earnings FROM earning GROUP BY name ORDER BY total_earnings ASC LIMIT 1
Result Format:
- Title: "📉 Least Performer"
- Subtitle: "Agent with lowest total earnings (primary agent commission + revenue share)"
- Display: Agent name, total earnings amount

Question: "Who has earned more than $100,000 USD?"
Definition: Find agents who have earned more than $100,000 USD (primary agent commission + revenue share)
Expected SQL: SELECT name, SUM(amount) as total_earnings FROM earning GROUP BY name HAVING SUM(amount) > 100000 ORDER BY total_earnings DESC
Result Format:
- Title: "💰 High Earners (>$100,000)"
- Subtitle: "Agents who have earned more than $100,000 USD"
- Display: Table with agent names and earnings
Note: This question requires validation to clarify if asking about individual transaction amounts or total earnings

Question: "Who has earned more than $50,000 in a single transaction?"
Definition: Find agents who have earned more than $50,000 USD in individual transactions
Expected SQL: SELECT name, MAX(amount) as highest_transaction FROM earning GROUP BY name HAVING MAX(amount) > 50000 ORDER BY highest_transaction DESC
Result Format:
- Title: "💰 High Individual Earners (>$50,000)"
- Subtitle: "Agents who have earned more than $50,000 in single transactions"
- Display: Table with agent names and highest transaction amounts

### B. RECRUITER QUESTIONS

Question: "Top recruiters"
Definition: Find agents with the highest number of direct referrals (downline members)
Expected SQL: SELECT ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS row_num, sponsor.name AS sponsor_name, sponsor.id AS sponsor_id, COUNT(*) AS direct_referrals FROM member AS m JOIN member AS sponsor ON m.sponsor = sponsor.id GROUP BY sponsor.id, sponsor.name ORDER BY direct_referrals DESC
Result Format:
- Title: "👥 Top Recruiters"
- Subtitle: "Agents with highest number of direct referrals"
- Display: Table with rank, agent names and referral counts

Question: "Who is the top recruiter?"
Definition: Find the agent with the highest number of direct referrals (downline members)
Expected SQL: SELECT ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS row_num, sponsor.name AS sponsor_name, sponsor.id AS sponsor_id, COUNT(*) AS direct_referrals FROM member AS m JOIN member AS sponsor ON m.sponsor = sponsor.id GROUP BY sponsor.id, sponsor.name ORDER BY direct_referrals DESC LIMIT 1
Result Format:
- Title: "👥 Top Recruiter"
- Subtitle: "Agent with highest number of direct referrals"
- Display: Agent name and referral count with rank

Question: "Top 5 recruiters"
Definition: Find the top 5 agents with the highest number of direct referrals
Expected SQL: SELECT ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS row_num, sponsor.name AS sponsor_name, sponsor.id AS sponsor_id, COUNT(*) AS direct_referrals FROM member AS m JOIN member AS sponsor ON m.sponsor = sponsor.id GROUP BY sponsor.id, sponsor.name ORDER BY direct_referrals DESC LIMIT 5
Result Format:
- Title: "👥 Top 5 Recruiters"
- Subtitle: "Agents with highest number of direct referrals"
- Display: Table with rank, agent names and referral counts

Question: "Who is the 3rd top recruiter?"
Definition: Find the 3rd agent with the highest number of direct referrals
Expected SQL: SELECT ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS row_num, sponsor.name AS sponsor_name, sponsor.id AS sponsor_id, COUNT(*) AS direct_referrals FROM member AS m JOIN member AS sponsor ON m.sponsor = sponsor.id GROUP BY sponsor.id, sponsor.name ORDER BY direct_referrals DESC LIMIT 1 OFFSET 2
Result Format:
- Title: "👥 3rd Top Recruiter"
- Subtitle: "3rd agent with highest number of direct referrals"
- Display: Agent name and referral count with rank

### F. DOWNLINE & REFERRAL QUESTIONS

Question: "What is the total count of downline members for the top performer?"
Definition: Find the number of downline/referral members for the top performing agent
Expected SQL: SELECT COUNT(*) as downline_count FROM member WHERE referrer_id = (SELECT id FROM member WHERE name = (SELECT name FROM earning GROUP BY name ORDER BY SUM(amount) DESC LIMIT 1))
Result Format:
- Title: "👥 Downline Members - Top Performer"
- Subtitle: "Total downline members for the top performer"
- Display: Count of downline members

### G. TRANSACTION QUESTIONS

Question: "How many transactions has the top performer completed?"
Definition: Count the total number of transactions completed by the top performing agent
Expected SQL: SELECT COUNT(*) as transaction_count FROM transaction WHERE agent_id = (SELECT id FROM member WHERE name = (SELECT name FROM earning GROUP BY name ORDER BY SUM(amount) DESC LIMIT 1))
Result Format:
- Title: "📊 Transaction Count - Top Performer"
- Subtitle: "Total transactions completed by the top performer"
- Display: Transaction count

Question: "What is the highest commission earned by agent in a single transaction?"
Definition: Find the highest commission amount earned by any agent in a single transaction
Expected SQL: SELECT name, MAX(amount) as highest_commission FROM earning WHERE type = 'primary_agent_commission' GROUP BY name ORDER BY highest_commission DESC LIMIT 1
Result Format:
- Title: "💰 Highest Single Transaction Commission"
- Subtitle: "Highest commission earned in a single transaction"
- Display: Agent name and commission amount

### H. REVENUE SHARE QUESTIONS

Question: "What is the revenue share earned by agent ID-4655758(Lisa)?"
Definition: Find the revenue share earnings (excluding primary agent commission) for a specific agent
Expected SQL: SELECT name, SUM(amount) as revenue_share FROM earning WHERE agent_id = '4655758' AND type = 'revenue_share'
Result Format:
- Title: "💸 Revenue Share Earnings"
- Subtitle: "Revenue share earned by [Agent Name] (excluding primary agent commission)"
- Display: Agent name and revenue share amount

### B. REVENUE QUESTIONS

Question: "What is the total revenue?"
Definition: Sum of all earnings across all time
Expected SQL: SELECT COALESCE(SUM(amount), 0) as total_revenue FROM earning
Result Format:
- Title: "💰 Total Revenue"
- Subtitle: "All-time earnings"
- Display: Total amount with currency formatting

Question: "What is the total revenue this month?"
Definition: Sum of all earnings in the current month
Expected SQL: SELECT COALESCE(SUM(amount), 0) as monthly_revenue FROM earning WHERE MONTH(date) = MONTH(NOW()) AND YEAR(date) = YEAR(NOW())
Result Format:
- Title: "💰 Monthly Revenue"
- Subtitle: "Total earnings for [Month Year]"
- Display: Monthly amount with currency formatting

### C. DATE RANGE QUESTIONS

Question: "total revenue generated in month from july 1 to july 10, 2025"
Definition: Sum of earnings for the specified date range
Expected SQL: SELECT COALESCE(SUM(amount), 0) as total_revenue FROM earning WHERE date BETWEEN '2025-07-01' AND '2025-07-10'
Result Format:
- Title: "💰 Revenue for July 1-10, 2025"
- Subtitle: "Total earnings for specified period"
- Display: Total amount with currency formatting

### D. AGENT SEARCH QUESTIONS

Question: "Find agent named John"
Definition: Search for agent by name
Expected SQL: SELECT * FROM member WHERE name LIKE '%John%'
Result Format:
- Title: "👤 Agent Search Results"
- Subtitle: "Agents matching 'John'"
- Display: Agent details in table format

### E. EARNINGS ANALYSIS QUESTIONS

Question: "Show me earnings by month"
Definition: Group earnings by month with totals
Expected SQL: SELECT CONCAT(YEAR(date), '-', LPAD(MONTH(date), 2, '0')) as month, COALESCE(SUM(amount), 0) as total_earnings FROM earning GROUP BY CONCAT(YEAR(date), '-', LPAD(MONTH(date), 2, '0')) ORDER BY month
Result Format:
- Title: "📊 Monthly Earnings Breakdown"
- Subtitle: "Earnings grouped by month"
- Display: Table with month and total earnings

## RESULT PRESENTATION RULES

### 1. TITLE FORMATTING
- Use emojis for visual appeal
- Be descriptive and clear
- Include time period if relevant

### 2. SUBTITLE FORMATTING
- Explain what the result represents
- Include context (time period, criteria)

### 3. DATA DISPLAY
- Use currency formatting for amounts
- Show percentages for comparisons
- Include relevant metrics

### 4. EMPTY RESULTS
- Show "No data found" instead of empty
- Explain why no results (e.g., "No earnings in specified period")
- Suggest alternative queries

### 5. ERROR HANDLING
- Show user-friendly error messages
- Suggest similar queries
- Provide troubleshooting tips

## TEMPLATE USAGE

When processing a question:
1. Match question to definition
2. Generate appropriate SQL
3. Format result using defined presentation rules
4. Include relevant context and explanations