docs/query_examples.md

# Query Examples

The following 15 natural language questions serve as a benchmark for the NL→SQL system.

## 1. Revenue & Sales
1. **Total revenue**: "What is our total revenue for completed orders?"
   - `SELECT SUM(total_amount) FROM orders WHERE order_status = 'completed'`
2. **Category breakdown**: "Show revenue by product category"
   - `SELECT p.category, SUM(o.total_amount) as revenue FROM orders o JOIN products p ON o.product_id = p.product_id GROUP BY 1`
3. **Monthly trend**: "Show monthly sales for the last 12 months"
   - `SELECT strftime('%Y-%m', order_date) as month, SUM(total_amount) FROM orders WHERE order_date >= date('now', '-12 months') GROUP BY 1`

## 2. Risk & Fraud
4. **Flagged orders**: "List all flagged orders over $1000"
   - `SELECT * FROM orders WHERE is_flagged = 1 AND total_amount > 1000`
5. **High risk customers**: "Show me customers with risk scores above 0.8"
   - `SELECT * FROM customers WHERE risk_score > 0.8`
6. **Fraud event summary**: "Count fraud events by type"
   - `SELECT event_type, COUNT(*) FROM fraud_events GROUP BY 1`

## 3. Shipping & Fulfillment
7. **Avg fulfillment**: "Average days to fulfill orders by brand"
   - `SELECT p.brand, AVG(o.fulfillment_days) FROM orders o JOIN products p ON o.product_id = p.product_id GROUP BY 1`
8. **Pending items**: "How many pending orders do we have currently?"
   - `SELECT COUNT(*) FROM orders WHERE order_status = 'pending'`

## 4. Product Insights
9. **Low stock**: "Which products have less than 50 units left?"
   - `SELECT name, stock_quantity FROM products WHERE stock_quantity < 50`
10. **Top sellers**: "Top 5 products by quantity sold"
    - `SELECT p.name, SUM(o.quantity) as total FROM orders o JOIN products p ON o.product_id = p.product_id GROUP BY 1 ORDER BY 2 DESC LIMIT 5`

## 5. Refunds & Returns
11. **Return reasons**: "What are the top 3 reasons for returns?"
    - `SELECT return_reason, COUNT(*) FROM returns GROUP BY 1 ORDER BY 2 DESC LIMIT 3`
12. **Refund total**: "Total amount refunded this year"
    - `SELECT SUM(refund_amount) FROM returns WHERE strftime('%Y', return_date) = strftime('%Y', 'now')`

## 6. Geographic Distribution
13. **Country revenue**: "Which 5 countries generate the most revenue?"
    - `SELECT c.country, SUM(o.total_amount) FROM orders o JOIN customers c ON o.customer_id = c.customer_id GROUP BY 1 ORDER BY 2 DESC LIMIT 5`
14. **Regional risk**: "Average risk score by region"
    - `SELECT region, AVG(risk_score) FROM customers GROUP BY 1`

## 7. Complex Business Logic
15. **Profit Margin**: "What are the most profitable product categories?"
    - `SELECT p.category, SUM(o.total_amount - (o.quantity * p.cost_price)) as profit FROM orders o JOIN products p ON o.product_id = p.product_id GROUP BY 1 ORDER BY 2 DESC`