Spaces:
Running
Running
File size: 7,463 Bytes
adf2969 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 |
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."
}
] |