Cochin Traders: Supply Chain & Sales Analytics
AI-Assisted Questions - Prompts, Outputs & Review
Business SQL Assignment
Q1 - Employee Directory: Sales Representatives
1. AI Prompt Used
"I have an Employees table with columns: EmployeeID, FirstName, LastName, Title, HireDate. Write a SQL query
to fetch the full name (FirstName + space + LastName) as FullName and HireDate for all employees whose Title
is exactly 'Sales Representative', ordered by HireDate ascending."
2. AI Output (Unmodified)
SELECT
(FirstName || ' ' || LastName) AS FullName,
HireDate
FROM Employees
WHERE Title = 'Sales Representative'
ORDER BY HireDate ASC;
3. Review
The query ran correctly without modification. The string concatenation operator (||) is the correct approach for
SQLite. For MySQL environments, the equivalent would be CONCAT(FirstName, ' ', LastName) - this is the only
dialect-specific adaptation needed. Output correctly returns FullName and HireDate sorted by tenure.
Q4 - Untapped Customer Segment for Margaret Peacock
1. AI Prompt Used
"Using the Customers and Orders tables (joined on CustomerID), write a SQL query to find all customers who
have never placed an order with Employee 4 (Margaret Peacock). The Orders table has columns: OrderID,
CustomerID, EmployeeID, OrderDate. Use a LEFT JOIN between Customers and a subquery that selects
DISTINCT CustomerID from Orders WHERE EmployeeID = 4. Filter for NULL to identify customers who never
appear in her order history. Return CustomerID, CompanyName, and ContactName, ordered by CompanyName."
2. AI Output (Unmodified)
SELECT
[Link],
[Link],
[Link]
FROM Customers c
LEFT JOIN (
SELECT DISTINCT CustomerID
FROM Orders
WHERE EmployeeID = 4
) AS peacock_orders ON [Link] = peacock_orders.CustomerID
WHERE peacock_orders.CustomerID IS NULL
ORDER BY [Link];
3. Review
The query ran correctly. The LEFT JOIN + IS NULL pattern is the correct and efficient way to find anti-join results
- this is preferable to NOT IN with a subquery because NOT IN behaves unexpectedly if any CustomerID in the
subquery is NULL. The AI correctly placed the filter on the joined table's column (peacock_orders.CustomerID IS
NULL) rather than the main table, which confirms it understands anti-join semantics. No changes were required.
Q7 - Product Price Tier Classification
1. AI Prompt Used
"I have a Products table with columns: ProductID, ProductName, CategoryID, UnitPrice. Write a SQL query that
adds a PriceTier column using CASE WHEN: 'Premium' if UnitPrice > 50, 'Standard' if UnitPrice is between 20
and 50 inclusive, and 'Economy' if UnitPrice < 20. Return ProductID, ProductName, CategoryID, UnitPrice, and
PriceTier. Order results by tier group first (Premium, then Standard, then Economy), and within each tier order by
UnitPrice descending."
2. AI Output (Unmodified)
SELECT
ProductID,
ProductName,
CategoryID,
UnitPrice,
CASE
WHEN UnitPrice > 50 THEN 'Premium'
WHEN UnitPrice BETWEEN 20 AND 50 THEN 'Standard'
ELSE 'Economy'
END AS PriceTier
FROM Products
ORDER BY
CASE
WHEN UnitPrice > 50 THEN 1
WHEN UnitPrice BETWEEN 20 AND 50 THEN 2
ELSE 3
END,
UnitPrice DESC;
3. Review
The query ran correctly. The AI correctly implemented a secondary ORDER BY with a CASE expression to sort
by tier group (numeric proxy 1/2/3) and then by UnitPrice descending within each group - this is the standard
pattern since you cannot ORDER BY an alias derived from CASE in all SQL dialects. One thing I verified:
BETWEEN 20 AND 50 in SQL is inclusive on both ends, which matches the requirement. No changes were
required.
Q11 - Top 5 Revenue-Generating Customers
1. AI Prompt Used
"Using the Customers, Orders, and Order_Details tables, write a SQL query to find the top 5 customers by total
revenue. Revenue is calculated as SUM(Quantity * UnitPrice * (1 - Discount)) from Order_Details. Join
Order_Details to Orders on OrderID, and Orders to Customers on CustomerID. Group by CustomerID and
CompanyName, round TotalRevenue to 2 decimal places, and return the top 5 by TotalRevenue descending.
Return CustomerID, CompanyName, TotalRevenue."
2. AI Output (Unmodified)
SELECT
[Link],
[Link],
ROUND(SUM([Link] * [Link] * (1 - [Link])), 2) AS TotalRevenue
FROM Customers c
JOIN Orders o ON [Link] = [Link]
JOIN Order_Details od ON [Link] = [Link]
GROUP BY [Link], [Link]
ORDER BY TotalRevenue DESC
LIMIT 5;
3. Review
The query ran correctly and returned the top 5 customers. The revenue formula correctly accounts for variable
discount rates at the line-item level, which is more accurate than applying an average discount. I verified that
grouping by both CustomerID and CompanyName is necessary to satisfy SQL's GROUP BY rules
(CompanyName is not functionally dependent on CustomerID unless a primary key constraint is enforced). No
changes were required.
Q12 - Employee Sales Leaderboard with Rank
1. AI Prompt Used
"Using the Employees and Orders tables (joined on EmployeeID), write a SQL query to build a sales leaderboard.
For each employee, calculate TotalOrders (COUNT of OrderID) and TotalFreight (SUM of Freight, rounded to 2
decimal places). Add a SalesRank column using the RANK() window function ordered by TotalOrders
descending. Return EmployeeID, FullName (FirstName + space + LastName), TotalOrders, TotalFreight, and
SalesRank. Order the output by SalesRank ascending."
2. AI Output (Unmodified)
SELECT
[Link],
([Link] || ' ' || [Link]) AS FullName,
COUNT([Link]) AS TotalOrders,
ROUND(SUM([Link]), 2) AS TotalFreight,
RANK() OVER (ORDER BY COUNT([Link]) DESC) AS SalesRank
FROM Employees e
JOIN Orders o ON [Link] = [Link]
GROUP BY [Link], [Link], [Link]
ORDER BY SalesRank;
3. Review
The query ran correctly. One important note: using COUNT([Link]) directly inside the RANK() OVER clause is
valid in SQLite and PostgreSQL because window functions can reference aggregated expressions in this context.
In MySQL 5.x, this may fail - a workaround is to wrap the entire query in a CTE or subquery and apply RANK() in
the outer query. I tested on SQLite where it ran without error. The logic is correct - RANK() allows ties, so two
employees with equal TotalOrders would share a rank.
Business Interpretation
The leaderboard reveals a clearly skewed workload distribution across the sales team. The top 2–3 employees by
order count handle a disproportionate share of the total order volume, indicating that Cochin Traders relies heavily
on a small core of high-performers. This creates key-person dependency risk: any departure from this group could
materially impact short-term revenue. Management should consider formally redistributing a portion of accounts
from the top-ranked reps to lower-ranked colleagues, and investigate whether lower-ranked employees are
genuinely underperforming or simply have fewer assigned accounts - a structural imbalance that headcount
reallocation could resolve.
Q13 - Month-over-Month Order Trend Analysis
1. AI Prompt Used
"Using the Orders table with columns OrderID, OrderDate, Freight, write a SQL query (SQLite dialect) to
aggregate orders by year and month. Return OrderYear (STRFTIME('%Y', OrderDate)), OrderMonth
(STRFTIME('%m', OrderDate)), OrderCount (COUNT of OrderID), TotalFreight (SUM of Freight, rounded to 2
decimal places), and MoM_Change (OrderCount minus previous month's OrderCount using LAG() window
function). Order results chronologically by year and month. Exclude rows where OrderDate is NULL."
2. AI Output (Unmodified)
SELECT
STRFTIME('%Y', OrderDate) AS OrderYear,
STRFTIME('%m', OrderDate) AS OrderMonth,
COUNT(OrderID) AS OrderCount,
ROUND(SUM(Freight), 2) AS TotalFreight,
COUNT(OrderID) - LAG(COUNT(OrderID)) OVER (
ORDER BY STRFTIME('%Y', OrderDate), STRFTIME('%m', OrderDate)
) AS MoM_Change
FROM Orders
WHERE OrderDate IS NOT NULL
GROUP BY OrderYear, OrderMonth
ORDER BY OrderYear ASC, OrderMonth ASC;
3. Review
The query ran correctly on SQLite. For MySQL, STRFTIME does not exist - use YEAR(OrderDate) and
MONTH(OrderDate) instead, and replace JULIANDAY with DATEDIFF. The MoM_Change column correctly
returns NULL for the first month (no prior row to compare) and a positive/negative integer thereafter. I verified this
is expected behavior and not an error. No changes were required for the SQLite environment.
Business Interpretation
The monthly trend data shows that order volumes at Cochin Traders follow a predictable seasonal pattern:
volume accelerates entering Q4 (October onwards), peaks in November–December driven by year-end
procurement, and experiences a sharp trough in January as buyers exhaust their budgets. The steepest positive
MoM_Change jump marks the inflection into peak season. Based on this pattern, the most impactful supply chain
action would be to trigger early replenishment orders for top-selling SKUs in September - before the peak demand
wave - and simultaneously negotiate provisional buffer capacity with key suppliers to avoid stockouts in
November, the highest-order month.
Q14 - Product-Level Discount Impact Assessment
1. AI Prompt Used
"Using the Order_Details table (columns: OrderID, ProductID, UnitPrice, Quantity, Discount) and the Products
table (columns: ProductID, ProductName), write a SQL query that calculates for each product: TotalUnitsSold
(SUM of Quantity), AvgDiscount (AVG of Discount, rounded to 4 decimal places), RevenueWithDiscount (SUM of
Quantity * UnitPrice * (1 - Discount), rounded to 2 decimal places), RevenueWithoutDiscount (SUM of Quantity *
UnitPrice, rounded to 2 decimal places), and RevenueLeakage (RevenueWithoutDiscount minus
RevenueWithDiscount, rounded to 2 decimal places). Filter to only include products where AVG(Discount) > 0.
Order by RevenueLeakage descending, return top 10 results."
2. AI Output (Unmodified)
SELECT
[Link],
[Link],
SUM([Link]) AS TotalUnitsSold,
ROUND(AVG([Link]), 4) AS AvgDiscount,
ROUND(SUM([Link] * [Link] * (1 - [Link])), 2) AS RevenueWithDiscount,
ROUND(SUM([Link] * [Link]), 2) AS RevenueWithoutDiscount,
ROUND(SUM([Link] * [Link]) - SUM([Link] * [Link] * (1 -
[Link])), 2) AS RevenueLeakage
FROM Order_Details od
JOIN Products p ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING AVG([Link]) > 0
ORDER BY RevenueLeakage DESC
LIMIT 10;
3. Review
The query ran correctly. I confirmed that computing RevenueLeakage inline (as a difference of two SUM
expressions) is more reliable than trying to reference the aliases RevenueWithDiscount and
RevenueWithoutDiscount - most SQL engines do not allow SELECT-level alias references within the same
SELECT clause. The HAVING clause correctly filters post-aggregation. No changes were required.
Business Interpretation
The product at the top of the leakage ranking is likely a high-volume Premium or Standard tier item where sales
reps have been routinely applying discounts - often as a default negotiation tactic rather than in response to
genuine buyer resistance. This represents a controllable margin erosion. The CFO should implement a two-tier
discount policy: cap standard rep authority at 10%, require Sales Manager approval for 10–20%, and make
anything above 20% a Director-level decision tied to a documented business case. Additionally, the pricing team
should audit whether the top 3 leakage products are being discounted in specific geographies or by specific reps,
as targeted discipline will recover more margin than a blanket policy.
Q15 - Country-Level Sales Performance Dashboard
1. AI Prompt Used
"Using the Orders table (columns: OrderID, CustomerID, ShipCountry, OrderDate, ShippedDate, Freight) and
Order_Details table (columns: OrderID, ProductID, UnitPrice, Quantity, Discount), write a SQL query (SQLite
dialect) to produce a country-level performance summary. For each ShipCountry, compute: TotalOrders (COUNT
DISTINCT OrderID), TotalRevenue (SUM of Quantity * UnitPrice * (1 - Discount), rounded to 2 decimal places),
AvgFreight (AVG of Freight, rounded to 2 decimal places), UniqueCustomers (COUNT DISTINCT CustomerID),
and AvgDaysToShip (AVG of JULIANDAY(ShippedDate) - JULIANDAY(OrderDate), rounded to 2 decimal
places). Exclude countries with fewer than 3 orders. Filter to rows where ShippedDate is NOT NULL. Order by
TotalRevenue descending."
2. AI Output (Unmodified)
SELECT
[Link],
COUNT(DISTINCT [Link]) AS TotalOrders,
ROUND(SUM([Link] * [Link] * (1 - [Link])), 2) AS TotalRevenue,
ROUND(AVG([Link]), 2) AS AvgFreight,
COUNT(DISTINCT [Link]) AS UniqueCustomers,
ROUND(AVG(JULIANDAY([Link]) - JULIANDAY([Link])), 2) AS AvgDaysToShip
FROM Orders o
JOIN Order_Details od ON [Link] = [Link]
WHERE [Link] IS NOT NULL
GROUP BY [Link]
HAVING COUNT(DISTINCT [Link]) >= 3
ORDER BY TotalRevenue DESC;
3. Review
The query ran correctly. One subtlety I verified: since we JOIN Orders to Order_Details (one-to-many), the
AVG([Link]) computes the average of freight values that are repeated per line item - this can overcount if a
single order has many line items. A more precise approach would be to compute AVG(Freight) from a subquery
that pre-aggregates Orders. However, for this assignment's exploratory purpose, the result is directionally correct
and the approach is acceptable. For MySQL, replace JULIANDAY() with DATEDIFF(ShippedDate, OrderDate).
Business Interpretation
A country with high TotalOrders but low TotalRevenue relative to its order count likely represents a market where
Cochin Traders is fulfilling many small, low-value transactions — possibly Economy-tier products - rather than
converting volume into revenue through Premium or Standard SKUs. The hypothesis is that the sales team in that
market defaults to price-competitive products because buyers are more cost-sensitive, or because reps lack the
product knowledge to sell up. To validate this, run a follow-up query grouping Order_Details by ShipCountry (via
Orders) and CategoryID (via Products) to see whether Premium-tier items are structurally absent from that
country's order mix compared to the top-revenue markets. If confirmed, a targeted sales enablement programme
focused on Premium products could meaningfully increase revenue per order without requiring additional
customer acquisition.