Mission: Practiced Data Analysis

Mission/open goal Description

Activity 1: Analytical Architecture Design

Title: Designing the Multi-Signal Quality Investigation Pipeline
Description: Created a comprehensive mermaid data-flow and architecture diagram showing how sales data, support tickets, chat JSON transcripts, and vector document embeddings are combined to isolate hidden product issues.

SELECT p.Category, SUM(sol.UnitPrice * sol.Quantity) AS TotalRevenue, SUM(sol.Quantity) AS TotalUnitsSold FROM [PromptathonDb].[dbo].[SalesOrderLines] sol JOIN [PromptathonDb].[dbo].[Products] p ON sol.ProductID = p.ProductID GROUP BY p.Category ORDER BY TotalRevenue DESC;

Activity 2: SQL Toolset Assessment

Title: Inventory of Available SQL Server & MCP Capabilities
Description: Mapped out the available SQL tools (operational servers, entity-level metadata exploration, and vector nearest-neighbor capabilities) to plan the analysis path.

SELECT TOP 20
p.Category,
p.ProductName,
SUM(sol.UnitPrice * sol.Quantity) AS TotalRevenue,
SUM(sol.Quantity) AS TotalUnitsSold
FROM [PromptathonDb].[dbo].[SalesOrderLines] sol
JOIN [PromptathonDb].[dbo].[Products] p
ON sol.ProductID = p.ProductID
GROUP BY p.Category, p.ProductName
ORDER BY TotalRevenue DESC;

Activity 3: Diagnostic Strategy Formulation

Title: Designing the End-to-End Database Exploration Strategy
Description: Drafted a structured, step-by-step SQL checklist to systematically connect to the database, aggregate sales metrics, pull qualitative ticket clusters, and execute vector similarity matches.
SELECT
p.Category,
p.ProductName,
COUNT(*) AS TicketCount,
AVG(CAST(st.SatisfactionScore AS float)) AS AvgSatisfactionScore,
SUM(CASE WHEN st.Priority = 'High' THEN 1 ELSE 0 END) AS HighPriorityTickets,
SUM(CASE WHEN st.Status = 'Open' THEN 1 ELSE 0 END) AS OpenTickets
FROM [PromptathonDb].[dbo].[SupportTickets] st
JOIN [PromptathonDb].[dbo].[Customers] c
ON st.CustomerID = c.CustomerID
JOIN [PromptathonDb].[dbo].[SalesOrders] SO
ON C.CustomerId = SO.CustomerId
JOIN [PromptathonDb].[dbo].[SalesOrderLines] sol
ON SO.OrderId = sol.OrderId
JOIN [PromptathonDb].[dbo].[Products] p
ON sol.ProductID = p.ProductID
GROUP BY p.Category, p.ProductName
ORDER BY TicketCount DESC;

Activity 4: Message JSON Deep Dive

Title: Advanced JSON Parsing of Support Chat Transcripts
Description: Corrected and optimized the query targeting SupportChats.MessagesJson using CROSS APPLY OPENJSON to isolate real customer complaint strings (e.g., defect, broken, poor fit) and correlate them to specific SKUs and tickets.

-- With Message as (
-- SELECT
-- sc.ChatID,
-- sc.TicketID,
-- JSON_VALUE(sc.MessagesJson, '$.text') AS Messages
-- FROM [PromptathonDb].[dbo].[SupportChats] sc
-- )

-- SELECT
-- TOP 20
-- sc.ChatID,
-- sc.TicketID,
-- sc.MessagesJson
-- FROM [PromptathonDb].[dbo].[SupportChats] sc
-- WHERE LOWER(CAST(sc.MessagesJson AS NVARCHAR(MAX))) LIKE '%defect%'
-- OR LOWER(CAST(sc.MessagesJson AS NVARCHAR(MAX))) LIKE '%broken%'
-- OR LOWER(CAST(sc.MessagesJson AS NVARCHAR(MAX))) LIKE '%poor fit%'
-- OR LOWER(CAST(sc.MessagesJson AS NVARCHAR(MAX))) LIKE '%quality%'
-- OR LOWER(CAST(sc.MessagesJson AS NVARCHAR(MAX))) LIKE '%return%'
-- ORDER BY sc.ChatID;

WITH ComplaintMessages AS
(
SELECT
sc.TranscriptId AS ChatId,
sc.TicketId,
sc.RelatedSKU,
p.Category,
p.ProductName,
sc.Channel,
sc.Language,
sc.CustomerName,
sc.SatisfactionScore,
j.seq,
j.sender,
j.text AS MessageText
FROM [PromptathonDb].[dbo].[SupportChats] sc
LEFT JOIN [PromptathonDb].[dbo].[Products] p
ON p.SKU = sc.RelatedSKU
CROSS APPLY OPENJSON(sc.MessagesJson)
WITH
(
seq INT '$.seq',
sender NVARCHAR(50) '$.sender',
text NVARCHAR(MAX) '$.text'
) AS j
WHERE LOWER(j.sender) = 'customer'
AND (
LOWER(j.text) LIKE '%defect%'
OR LOWER(j.text) LIKE '%broken%'
OR LOWER(j.text) LIKE '%poor fit%'
OR LOWER(j.text) LIKE '%quality%'
OR LOWER(j.text) LIKE '%return%'
OR LOWER(j.text) LIKE '%damaged%'
OR LOWER(j.text) LIKE '%doesn''t fit%'
OR LOWER(j.text) LIKE '%missing%'
OR LOWER(j.text) LIKE '%leak%'
)
)
SELECT
ChatId,
TicketId,
RelatedSKU,
Category,
ProductName,
Channel,
Language,
CustomerName,
SatisfactionScore,
seq,
MessageText
FROM ComplaintMessages
ORDER BY ChatId, seq;

--4) Find negative complaint themes in chat JSON

WITH ComplaintMessages AS
(
SELECT
sc.TranscriptId AS ChatId,
sc.RelatedSKU,
p.Category,
p.ProductName,
j.text AS MessageText
FROM [PromptathonDb].[dbo].[SupportChats] sc
LEFT JOIN [PromptathonDb].[dbo].[Products] p
ON p.SKU = sc.RelatedSKU
CROSS APPLY OPENJSON(sc.MessagesJson)
WITH
(
seq INT '$.seq',
sender NVARCHAR(50) '$.sender',
text NVARCHAR(MAX) '$.text'
) AS j
WHERE LOWER(j.sender) = 'customer'
AND (
LOWER(j.text) LIKE '%defect%'
OR LOWER(j.text) LIKE '%broken%'
OR LOWER(j.text) LIKE '%poor fit%'
OR LOWER(j.text) LIKE '%quality%'
OR LOWER(j.text) LIKE '%return%'
OR LOWER(j.text) LIKE '%damaged%'
OR LOWER(j.text) LIKE '%doesn''t fit%'
OR LOWER(j.text) LIKE '%missing%'
OR LOWER(j.text) LIKE '%leak%'
)
)
SELECT
Category,
ProductName,
RelatedSKU,
COUNT(DISTINCT ChatId) AS ComplaintChatCount,
STRING_AGG(MessageText, ' | ') AS ComplaintExcerpts
FROM ComplaintMessages
GROUP BY Category, ProductName, RelatedSKU
ORDER BY ComplaintChatCount DESC;

Activity 5: Negative Document Investigation

Title: Exploratory Query Tuning for Document Sentiment & SKU Links
Description: Deep-dived into the Docs table, corrected non-existent columns, used the actual Body, Title, and RelatedSKU fields to group negative customer reviews, and prepared the ground for the vector search phase.

-- SELECT TOP 50
-- d.DocID,
-- d.SourceType,
-- d.DocumentText,
-- d.ProductID,
-- d.CustomerID,
-- d.Language,
-- d.Country
-- FROM [PromptathonDb].[dbo].[Docs] d
-- WHERE LOWER(CAST(d.DocumentText AS NVARCHAR(MAX))) LIKE '%defect%'
-- OR LOWER(CAST(d.DocumentText AS NVARCHAR(MAX))) LIKE '%broken%'
-- OR LOWER(CAST(d.DocumentText AS NVARCHAR(MAX))) LIKE '%poor fit%'
-- OR LOWER(CAST(d.DocumentText AS NVARCHAR(MAX))) LIKE '%quality%'
-- OR LOWER(CAST(d.DocumentText AS NVARCHAR(MAX))) LIKE '%return%'
-- ORDER BY d.DocID;

SELECT
d.DocId,
d.SourceType,
d.SourceId,
d.Title,
d.Body,
d.Category,
d.RelatedOrderId,
d.RelatedTicketId,
d.RelatedCustomerId,
d.RelatedSKU,
p.ProductName,
p.Category,
j.tag AS DocTag
FROM [PromptathonDb].[dbo].[Docs] d
LEFT JOIN [PromptathonDb].[dbo].[Products] p
ON p.SKU = d.RelatedSKU
CROSS APPLY OPENJSON(d.TagsJson)
WITH (tag NVARCHAR(100) '$') AS j
WHERE
LOWER(COALESCE(d.Title, '')) LIKE '%defect%'
OR LOWER(COALESCE(d.Title, '')) LIKE '%broken%'
OR LOWER(COALESCE(d.Title, '')) LIKE '%poor fit%'
OR LOWER(COALESCE(d.Title, '')) LIKE '%quality%'
OR LOWER(COALESCE(d.Title, '')) LIKE '%return%'
OR LOWER(COALESCE(d.Body, '')) LIKE '%defect%'
OR LOWER(COALESCE(d.Body, '')) LIKE '%broken%'
OR LOWER(COALESCE(d.Body, '')) LIKE '%poor fit%'
OR LOWER(COALESCE(d.Body, '')) LIKE '%quality%'
OR LOWER(COALESCE(d.Body, '')) LIKE '%return%'
ORDER BY d.DocId DESC;

--Aggregate negative documents by SKU / product

SELECT
d.RelatedSKU,
p.ProductName,
p.Category,
COUNT(*) AS NegativeDocCount,
STRING_AGG(LEFT(d.Title, 120), ' | ') AS SampleTitles
FROM [PromptathonDb].[dbo].[Docs] d
LEFT JOIN [PromptathonDb].[dbo].[Products] p
ON p.SKU = d.RelatedSKU
WHERE
LOWER(COALESCE(d.Title, '')) LIKE '%defect%'
OR LOWER(COALESCE(d.Title, '')) LIKE '%broken%'
OR LOWER(COALESCE(d.Title, '')) LIKE '%poor fit%'
OR LOWER(COALESCE(d.Title, '')) LIKE '%quality%'
OR LOWER(COALESCE(d.Title, '')) LIKE '%return%'
OR LOWER(COALESCE(d.Body, '')) LIKE '%defect%'
OR LOWER(COALESCE(d.Body, '')) LIKE '%broken%'
OR LOWER(COALESCE(d.Body, '')) LIKE '%poor fit%'
OR LOWER(COALESCE(d.Body, '')) LIKE '%quality%'
OR LOWER(COALESCE(d.Body, '')) LIKE '%return%'
GROUP BY d.RelatedSKU, p.ProductName, p.Category
ORDER BY NegativeDocCount DESC;

--If you want to inspect only review/support source types

SELECT
d.DocId,
d.SourceType,
d.SourceId,
d.Title,
d.Body,
d.Category,
d.RelatedSKU,
p.ProductName,
p.Category
FROM [PromptathonDb].[dbo].[Docs] d
LEFT JOIN [PromptathonDb].[dbo].[Products] p
ON p.SKU = d.RelatedSKU
WHERE d.SourceType IN ('review', 'support')
AND (
LOWER(COALESCE(d.Title, '')) LIKE '%defect%'
OR LOWER(COALESCE(d.Title, '')) LIKE '%broken%'
OR LOWER(COALESCE(d.Title, '')) LIKE '%poor fit%'
OR LOWER(COALESCE(d.Title, '')) LIKE '%quality%'
OR LOWER(COALESCE(d.Body, '')) LIKE '%defect%'
OR LOWER(COALESCE(d.Body, '')) LIKE '%broken%'
OR LOWER(COALESCE(d.Body, '')) LIKE '%poor fit%'
OR LOWER(COALESCE(d.Body, '')) LIKE '%quality%'
)
ORDER BY d.DocId;

--6) Run vector similarity on one representative negative doc

EXEC dbo.FindSimilarDocsByDocId @DocId = 1, @TopN = 5;

--7) Correlate the strongest product with the support/document cluster

SELECT
p.Category,
p.ProductName,
SUM(sol.UnitPrice * sol.Quantity) AS TotalRevenue,
SUM(sol.Quantity) AS TotalUnitsSold,
COUNT(DISTINCT st.TicketID) AS SupportTickets,
AVG(CAST(st.SatisfactionScore AS float)) AS AvgSatisfaction
FROM [PromptathonDb].[dbo].[SalesOrderLines] sol
JOIN [PromptathonDb].[dbo].[Products] p
ON sol.ProductID = p.ProductID
LEFT JOIN [PromptathonDb].[dbo].[SupportTickets] st
ON st.RelatedSKU = p.SKU
GROUP BY p.Category, p.ProductName
ORDER BY TotalRevenue DESC;

Harness and model

Gemini 3.5 Flash

Turn-by-turn journey

Prompt : You are the Zava evidence-first SQL investigator. Before answering, list the SQL MCP tools and entities you can access. Then wait until you have tool evidence before making claims. Start by discovering the schema.

Agent Response 👍
I'll list the SQL MCP tools and entities available to me, and then start by exploring the schema of the PromptathonDb database to establish our baseline evidence.

Running query on connection 'sql, PromptathonDb (sa)' (ID: ce27fbbc-ab50-4b15-a254-7b0237caab5f)
Completed with input: {
"connectionId": "ce27fbbc-ab50-4b15-a254-7b0237caab5f",
"queryIntent": "schema_exploration",
"queryTypes": [
"SELECT"
],
"query": "SELECT TABLE_NAME, TABLE_SCHEMA FROM PromptathonDb.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'"
}

As the Zava evidence-first SQL investigator, I will guide our investigation step-by-step using only verified database evidence.

Accessible SQL MCP Tools

I have access to the following categories of SQL and database tools:

1. Database Connection & Schema Exploration:
   • mssql_list_servers, mssql_connect, mssql_change_database, mssql_disconnect, mssql_get_connection_details, mssql_list_databases, mssql_list_schemas, mssql_list_tables, mssql_list_views, mssql_list_functions
2. Query Execution & Schema Modification:
   • mssql_run_query (For executing detailed, multi-table SELECTs, aggregation joins, and text-parsing queries)
   • mssql_schema_designer (For viewing overview and tables)
3. High-Level Semantic/Entity Layer:
   • mcp_sql_mcp_serve_describe_entities (Provides catalog descriptions and tool permissions)
   • mcp_sql_mcp_serve_aggregate_records (For direct high-level data summaries)
   • mcp_sql_mcp_serve_find_similar_docs_by_doc_id (For vector similarity comparisons across document embeddings)

---

Accessible Entities

In the catalog, the following primary entities are registered and available in the PromptathonDb database under the dbo schema:

• dbo.Products — Enriched product catalog containing SKUs, pricing, categories, types, channels, launch dates, and notes.
• dbo.Customers — Customer profile information (demographics, countries, languages, segments).
• dbo.Employees — Employee profiles and operational/facility context.
• dbo.SalesOrders — Headings of completed B2B and B2C sales orders.
• dbo.SalesOrderLines — Transaction detail lines connecting sales orders with products.
• dbo.SupportTickets — Service ticket cases, tracking priorities, categories, and satisfaction scores.
• dbo.SupportChats — Live chat support transcripts including structured conversation logs in JSON format.
• dbo.Docs — Enriched vector document storage combining customer reviews and tickets with precalculated text embeddings.

---

Initial Discoveries: Database Schema Verified

I have executed a schema query to verify the physical tables in the database. The tables are successfully loaded under dbo as shown:

1. Products
2. Customers
3. Employees
4. SalesOrders
5. SalesOrderLines
6. SupportTickets
7. SupportChats
8. Docs

I will now wait for your next command before executing analytical queries or making claims. Let me know if you would like me to begin the quantitative analysis of Zava's revenue, volumes, and support burden to pinpoint the quality crisis.

Completion

• Yes, the agent completed the mission or goal.
• No, the agent did not complete the mission or goal.

Bonus work

No response

This issue is a mission report rather than a scoped change request. Review the PromptathonDb schema exploration, the SalesOrderLines, SupportTickets, SupportChats, Docs, and Products queries, and dbo.FindSimilarDocsByDocId; a contribution would need a defined documentation target and completion criteria.