few_shots.py

few_shots = [
    {
        'Question': "Which customers have the highest aggregated purchase amount?",
        'SQLQuery': """
            SELECT c.customer_id, c.first_name, c.last_name, SUM(o.total_amount) as total_purchase
            FROM customers c
            JOIN orders o ON c.customer_id = o.customer_id
            GROUP BY c.customer_id
            ORDER BY total_purchase DESC
            LIMIT 1;
        """,
        'SQLResult': "(123, 'John', 'Doe', 543.21)",
        'Answer': "John Doe with customer ID 123 has the highest aggregated purchase amount of $543.21."
    },
    {
        'Question': "What is the total revenue generated by all orders?",
        'SQLQuery': """
            SELECT SUM(total_amount) as total_revenue
            FROM orders;
        """,
        'SQLResult': "(10000.00)",
        'Answer': "The total revenue generated by all orders is $10,000.00."
    },
    {
        'Question': "Which product has been ordered the most?",
        'SQLQuery': """
            SELECT p.product_name, COUNT(o.product_id) as order_count
            FROM orders o
            JOIN products p ON o.product_id = p.id
            GROUP BY o.product_id
            ORDER BY order_count DESC
            LIMIT 1;
        """,
        'SQLResult': "('iPhone 13', 50)",
        'Answer': "The iPhone 13 has been ordered the most, with 50 orders."
    },
    {
        'Question': "What is the average order value?",
        'SQLQuery': """
            SELECT AVG(total_amount) as average_order_value
            FROM orders;
        """,
        'SQLResult': "(50.00)",
        'Answer': "The average order value is $50.00."
    },
    {
        'Question': "Which customer has placed the most orders?",
        'SQLQuery': """
            SELECT c.customer_id, c.first_name, c.last_name, COUNT(o.order_id) as order_count
            FROM customers c
            JOIN orders o ON c.customer_id = o.customer_id
            GROUP BY c.customer_id
            ORDER BY order_count DESC
            LIMIT 1;
        """,
        'SQLResult': "(123, 'John', 'Doe', 10)",
        'Answer': "John Doe with customer ID 123 has placed the most orders, with 10 orders."
    },
    {
        'Question': "What is the total number of unique customers?",
        'SQLQuery': """
            SELECT COUNT(DISTINCT customer_id) as unique_customers
            FROM orders;
        """,
        'SQLResult': "(500)",
        'Answer': "There are 500 unique customers."
    },
    {
        'Question': "What is the most popular payment method?",
        'SQLQuery': """
            SELECT payment_method, COUNT(order_id) as order_count
            FROM orders
            GROUP BY payment_method
            ORDER BY order_count DESC
            LIMIT 1;
        """,
        'SQLResult': "('Credit Card', 300)",
        'Answer': "The most popular payment method is Credit Card, used in 300 orders."
    },
    {
        'Question': "Which product category has the highest total revenue?",
        'SQLQuery': """
            SELECT p.category, SUM(o.total_amount) as total_revenue
            FROM orders o
            JOIN products p ON o.product_id = p.id
            GROUP BY p.category
            ORDER BY total_revenue DESC
            LIMIT 1;
        """,
        'SQLResult': "('Electronics', 5000.00)",
        'Answer': "The Electronics category has the highest total revenue of $5,000.00."
    },
    {
        'Question': "What is the average shipping time for orders?",
        'SQLQuery': """
            SELECT AVG(DATEDIFF(delivery_date, order_date)) as average_shipping_time
            FROM orders;
        """,
        'SQLResult': "(3.5)",
        'Answer': "The average shipping time for orders is 3.5 days."
    },
    {
        'Question': "Which customer has the highest average order value?",
        'SQLQuery': """
            SELECT c.customer_id, c.first_name, c.last_name, AVG(o.total_amount) as average_order_value
            FROM customers c
            JOIN orders o ON c.customer_id = o.customer_id
            GROUP BY c.customer_id
            ORDER BY average_order_value DESC
            LIMIT 1;
        """,
        'SQLResult': "(123, 'John', 'Doe', 100.00)",
        'Answer': "John Doe with customer ID 123 has the highest average order value of $100.00."
    },
    {
        'Question': "What is the total number of orders by country?",
        'SQLQuery': """
            SELECT c.country, COUNT(o.order_id) as order_count
            FROM customers c
            JOIN orders o ON c.customer_id = o.customer_id
            GROUP BY c.country;
        """,
        'SQLResult': "([('USA', 200), ('Canada', 100), ('Mexico', 50)])",
        'Answer': "There are 200 orders from the USA, 100 orders from Canada, and 50 orders from Mexico."
    },
    {
        'Question': "Which product has the highest profit margin?",
        'SQLQuery': """
            SELECT p.product_name, (p.price - p.cost) / p.price as profit_margin
            FROM products p
            ORDER BY profit_margin DESC
            LIMIT 1;
        """,
        'SQLResult': "('iPhone 13', 0.30)",
        'Answer': "The iPhone 13 has the highest profit margin of 30%."
    },
    {
        'Question': "What is the total revenue by month?",
        'SQLQuery': """
            SELECT MONTH(o.order_date) as month, SUM(o.total_amount) as total_revenue
            FROM orders o
            GROUP BY MONTH(o.order_date);
        """,
        'SQLResult': "([(1, 1000.00), (2, 1200.00), (3, 1500.00)])",
        'Answer': "The total revenue for January is $1,000.00, February is $1,200.00, and March is $1,500.00."
    },
    {
        'Question': "Which customer has placed orders in the most categories?",
        'SQLQuery': """
            SELECT c.customer_id, c.first_name, c.last_name, COUNT(DISTINCT p.category) as category_count
            FROM customers c
            JOIN orders o ON c.customer_id = o.customer_id
            JOIN products p ON o.product_id = p.id
            GROUP BY c.customer_id
            ORDER BY category_count DESC
            LIMIT 1;
        """,
        'SQLResult': "(123, 'John', 'Doe', 5)",
        'Answer': "John Doe with customer ID 123 has placed orders in 5 different categories."
    },
    {
        'Question': "What is the average order value by payment method?",
        'SQLQuery': """
            SELECT o.payment_method, AVG(o.total_amount) as average_order_value
            FROM orders o
            GROUP BY o.payment_method;
        """,
        'SQLResult': "([('Credit Card', 50.00), ('PayPal', 40.00), ('Bank Transfer', 60.00)])",
        'Answer': "The average order value for Credit Card is $50.00, PayPal is $40.00, and Bank Transfer is $60.00."
    },
    {
        "Question": "how many orders were cancelled on monthly basis",
        'SQLQuery': """
            SELECT strftime('%m', order_date) as month, COUNT(order_id) as cancelled_orders
            FROM orders
            WHERE order_status = 'Cancelled'
            GROUP BY month;
        """,
        "SQLResult": "[('01', 108), ('02', 94), ('03', 111), ('04', 104), ('05', 108), ('06', 90), ('07', 117), ('08', 91), ('09', 102), ('10', 90), ('11', 103), ('12', 108)]",
        "Answer": "There were 108 cancelled orders in January, 94 in February, 111 in March, 104 in April, 108 in May, 90 in June, 117 in July, 91 in August, 102 in September, 90 in October, 103 in November, and 108 in December."
    }
]