“Who are our biggest vendors by spend?”

Every step the AI took

1. 1
   Checked the saved queries
   6 saved queries
2. 2
   Read the Fishbowl notes index
   13 topic notes
3. 3
   Looked up a table · po
   51 columns
4. 4
   Looked up a table · poitem
   31 columns
5. 5
   Understand PO statuses and date range before ranking vendors by purchase spend

   ✓ passed read-only checkrow cap applied

   Show the SQL

   SELECT s.id, s.name, COUNT(*) AS poCount, MIN(po.dateIssued) AS firstIssued, MAX(po.dateIssued) AS lastIssued
   FROM po
   JOIN postatus AS s ON s.id = po.statusId
   GROUP BY s.id, s.name
   ORDER BY s.id
   LIMIT 101

   4 rows · 231 ms
6. 6
   Rank vendors by total purchase order spend, excluding bid requests

   Now aggregating PO line totals by vendor for committed POs (issued through closed short), credit lines netted

   ✓ passed read-only checkrow cap applied

   Show the SQL

   SELECT v.name AS vendor, COUNT(DISTINCT po.id) AS poCount, ROUND(SUM(CASE WHEN pit.name LIKE '%Credit%' THEN -1 ELSE 1 END * pi.totalCost), 2) AS orderedSpend, ROUND(SUM(CASE WHEN pit.name LIKE '%Credit%' THEN -1 ELSE 1 END * pi.unitCost * COALESCE(pi.qtyFulfilled, 0)), 2) AS receivedSpend, SUM(pit.name LIKE '%Credit%') AS creditLines, COUNT(DISTINCT po.currencyId) AS currencies, MIN(DATE(po.dateIssued)) AS firstPO, MAX(DATE(po.dateIssued)) AS lastPO
   FROM po
   JOIN vendor AS v ON v.id = po.vendorId
   JOIN poitem AS pi ON pi.poId = po.id
   JOIN poitemtype AS pit ON pit.id = pi.typeId
   WHERE po.statusId IN (20, 30, 40, 50, 55, 60, 70)
   GROUP BY v.name
   ORDER BY orderedSpend DESC
   LIMIT 101

   10 rows · 208 ms