# what were my top product sales, and who bought them?

> A real Claude session on a Fishbowl database (ILC's demo company, sample data), connected through ILC MCP for Fishbowl. The answer below is what the AI said; the queries are what it ran. Page: https://israellopezconsulting.com/fishbowl-mcp/questions/top-products

## Question

what were my top product sales, and who bought them?

### Answer

Three cookies make up about 77% of your $29,544.54 in product sales, and Allen's Groceries is the main buyer of all three. This covers all 48 sales orders on file (issued July 10 to August 30, 2026), ranked by revenue.

| Rank | Product | Revenue | Share | Who bought it |
|---|---|---|---|---|
| 1 | BB2004 Snickerdoodle | $11,361.60 | 38.5% | Allen's Groceries $8,400.00; Eric Washburn $2,800.00; Candie Walker $105.00; Mountaineer Catering $42.60; three others under $10 each |
| 2 | BB2001 Sugar Cookie | $8,194.26 | 27.7% | Allen's Groceries $4,536.00; Candie Walker $3,492.00; Mountaineer Catering $152.76; Jim's Bake Shop $9.00; Jim Raynor $4.50 |
| 3 | BB2005 Chocolate Chip | $3,112.50 | 10.5% | Allen's Groceries $3,030.00; Mountaineer Catering $75.00; Karen Smith $5.00; Jim Raynor $2.50 |
| 4 | BP203 Butter | $1,515.00 | 5.1% | Allen's Groceries $1,500.00; Baker Frontier $15.00 |
| 5 | BB2002 Chocolate Fudge | $1,173.30 | 4.0% | Mark Bennett $1,089.75; Mountaineer Catering $80.30; Eric Washburn $3.25 |

- **Customer concentration:** Allen's Groceries accounts for $17,466.00 across the top four products, about 59% of all product revenue, from only a handful of orders.
- **Other products:** the remaining 31 each sold under $450.
- **Unit counts:** I ranked by revenue because quantities aren't comparable across lines. Snickerdoodle works out to $350 per unit for Allen's Groceries and $3.50 for Candie Walker, which suggests cases versus eaches.

"Sales" here means sale and drop-ship lines on issued, in-progress, fulfilled, and closed-short orders. The one estimate is excluded, and credit returns are not netted out.

## Behind the scenes

Every step the AI took, as recorded in the query log. All queries are read-only and row-capped.

1. Checked the saved queries — 4 saved queries
2. Read the Fishbowl notes index — 12 topic notes
3. Looked up a table (soitem) — 35 columns
4. Looked up a table (so) — 66 columns
5. Ran a read-only query: Top selling products and which customers bought them; first checking line item types — 5 rows, 313 ms

   ```sql
   SELECT t.id, t.name, COUNT(*) AS n
   FROM soitem AS si
   JOIN soitemtype AS t ON t.id = si.typeId
   GROUP BY t.id, t.name
   LIMIT 101
   ```

6. Ran a read-only query: Top selling products and which customers bought them; checking order statuses and date range — 5 rows, 230 ms

   ```sql
   SELECT s.id, s.name, COUNT(*) AS n, MIN(so.dateIssued) AS firstIssued, MAX(so.dateIssued) AS lastIssued
   FROM so
   JOIN sostatus AS s ON s.id = so.statusId
   GROUP BY s.id, s.name
   LIMIT 101
   ```

7. Ran a read-only query: Top selling products by revenue across issued/fulfilled sales orders — 36 rows, 295 ms

   ```sql
   SELECT si.productNum, MAX(p.description) AS description, ROUND(SUM(si.totalPrice), 2) AS revenue, ROUND(SUM(si.qtyOrdered), 2) AS qtyOrdered, ROUND(SUM(si.qtyFulfilled), 2) AS qtyFulfilled, COUNT(DISTINCT si.soId) AS orders, COUNT(DISTINCT so.customerId) AS customers
   FROM soitem AS si
   JOIN so ON so.id = si.soId
   LEFT JOIN product AS p ON p.id = si.productId
   WHERE si.typeId IN (10, 12) AND so.statusId IN (20, 25, 60, 70)
   GROUP BY si.productNum
   ORDER BY revenue DESC
   LIMIT 101
   ```

8. Ran a read-only query: Which customers bought each product, with revenue and quantity — 84 rows, 284 ms

   ```sql
   SELECT si.productNum, c.name AS customer, ROUND(SUM(si.totalPrice), 2) AS revenue, ROUND(SUM(si.qtyOrdered), 2) AS qtyOrdered, COUNT(DISTINCT si.soId) AS orders
   FROM soitem AS si
   JOIN so ON so.id = si.soId
   JOIN customer AS c ON c.id = so.customerId
   WHERE si.typeId IN (10, 12) AND so.statusId IN (20, 25, 60, 70)
   GROUP BY si.productNum, c.name
   ORDER BY si.productNum, revenue DESC
   LIMIT 1001
   ```

9. Ran a read-only query: Total product sales revenue for share calculation — 1 row, 285 ms

   ```sql
   SELECT ROUND(SUM(si.totalPrice), 2) AS revenue, COUNT(DISTINCT si.soId) AS orders, COUNT(DISTINCT si.productNum) AS products
   FROM soitem AS si
   JOIN so ON so.id = si.soId
   WHERE si.typeId IN (10, 12) AND so.statusId IN (20, 25, 60, 70)
   LIMIT 101
   ```

Learn more: https://israellopezconsulting.com/fishbowl-mcp · More examples: https://israellopezconsulting.com/fishbowl-mcp/questions