top_products
Retrieve top-selling bakery products by quantity or revenue. Filter results 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
TableJSON 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. |
Implementation Reference
- src/bakery_data_mcp/server.py:461-515 (handler)Handler for the 'top_products' tool. Constructs and executes a SQL query to retrieve top-selling products based on specified metric (quantity or revenue), date range, department filter, and limit. Joins with products table if department filter is applied. 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, including name, description, and input schema definition for MCP tool discovery.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." } } }