top_products
Retrieve best-selling bakery products by quantity or revenue. Filter by date range and department to analyze sales performance.
Instructions
Get top selling products by quantity or revenue. Supports filtering by date range and department.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| start_date | No | Start date (YYYY-MM-DD format). Optional. | |
| end_date | No | End date (YYYY-MM-DD format). Optional. | |
| department_id | No | Filter by department ID. Optional. | |
| metric | No | Rank by quantity sold or total revenue. Default: revenue. | |
| limit | No | Number of top products to return. Default: 10. |
Input Schema (JSON Schema)
{
"properties": {
"department_id": {
"description": "Filter by department ID. Optional.",
"type": "number"
},
"end_date": {
"description": "End date (YYYY-MM-DD format). Optional.",
"type": "string"
},
"limit": {
"description": "Number of top products to return. Default: 10.",
"type": "number"
},
"metric": {
"description": "Rank by quantity sold or total revenue. Default: revenue.",
"enum": [
"quantity",
"revenue"
],
"type": "string"
},
"start_date": {
"description": "Start date (YYYY-MM-DD format). Optional.",
"type": "string"
}
},
"type": "object"
}
Implementation Reference
- src/bakery_data_mcp/server.py:461-516 (handler)Handler implementation for the 'top_products' tool. Builds and executes a SQL query to retrieve top-selling products based on quantity or revenue, with optional filters for date range and department ID. Returns JSON-formatted results.elif name == "top_products": metric = arguments.get("metric", "revenue") if metric == "quantity": order_by = "total_quantity DESC" else: order_by = "total_revenue DESC" query = f""" SELECT product_code, product_name, COUNT(*) as transaction_count, SUM(quantity) as total_quantity, SUM(amount) as total_revenue, AVG(unit_price) as avg_price FROM transactions WHERE 1=1 """ params = [] if "start_date" in arguments: query += " AND datetime >= ?" params.append(arguments["start_date"]) if "end_date" in arguments: query += " AND datetime <= ?" params.append(arguments["end_date"] + " 23:59:59") if "department_id" in arguments: query = """ SELECT t.product_code, t.product_name, COUNT(*) as transaction_count, SUM(t.quantity) as total_quantity, SUM(t.amount) as total_revenue, AVG(t.unit_price) as avg_price FROM transactions t JOIN products p ON t.product_code = p.plu_code WHERE p.department_id = ? """ params.insert(0, arguments["department_id"]) if "start_date" in arguments: query += " AND t.datetime >= ?" if "end_date" in arguments: query += " AND t.datetime <= ?" query += f" GROUP BY product_code, product_name ORDER BY {order_by} LIMIT ?" params.append(arguments.get("limit", 10)) cursor.execute(query, params) results = cursor.fetchall() return [TextContent( type="text", text=json.dumps(results, ensure_ascii=False, indent=2) )]
- src/bakery_data_mcp/server.py:174-203 (registration)Registration of the 'top_products' tool in the list_tools() function, defining its name, description, and input schema.Tool( name="top_products", description="Get top selling products by quantity or revenue. Supports filtering by date range and department.", inputSchema={ "type": "object", "properties": { "start_date": { "type": "string", "description": "Start date (YYYY-MM-DD format). Optional." }, "end_date": { "type": "string", "description": "End date (YYYY-MM-DD format). Optional." }, "department_id": { "type": "number", "description": "Filter by department ID. Optional." }, "metric": { "type": "string", "enum": ["quantity", "revenue"], "description": "Rank by quantity sold or total revenue. Default: revenue." }, "limit": { "type": "number", "description": "Number of top products to return. Default: 10." } } } ),
- Input schema for the 'top_products' tool, defining parameters for date range, department, ranking metric, and result limit.inputSchema={ "type": "object", "properties": { "start_date": { "type": "string", "description": "Start date (YYYY-MM-DD format). Optional." }, "end_date": { "type": "string", "description": "End date (YYYY-MM-DD format). Optional." }, "department_id": { "type": "number", "description": "Filter by department ID. Optional." }, "metric": { "type": "string", "enum": ["quantity", "revenue"], "description": "Rank by quantity sold or total revenue. Default: revenue." }, "limit": { "type": "number", "description": "Number of top products to return. Default: 10." } } }