Mod 2 Ungraded lab 2 - total_sales seems wrong to me

Course Q&A Agentic AI
,
1

The initial SQL

SELECT color, SUM(qty_delta * unit_price) AS total_sales

produces an answer of

color total_sales
blue -190571.46

ok. fair enough. Then the reflected query uses

SELECT color, SUM(ABS(qty_delta) * unit_price) AS total_sales

which produces

color total_sales
white 358315.09

Something seems wrong to me. If sales are all negative, then SUM(ABS(qty_delta) unit_price) should merely reverse the sign to positive. But the magnitude of the final number is totally different. This suggests some sales have negative qty_delta and some positive qty_delta. In this case, using ABS (qty_delta unit_price) is very different from (-qty_delta * unit_price).

If this were a real business problem, I would want to understand the meaning of the sign of qty_delta and why the data were this way.

Thoughts?

2

I’m not a mentor for that course, but I noticed this:
Your first answer is for color ‘blue’, but your second is for color ‘white’.

1 Like
3

Thank you. I missed that.

That is even more puzzling.

4

@davidkrethallen , just to follow up on why you are seeing different colors and numbers for the different queries:

In the first case (with color blue and a negative total_sales), the full query is:

SELECT color, SUM(qty_delta * unit_price) AS total_sales
FROM transactions
WHERE action = 'sale'
GROUP BY color
ORDER BY total_sales DESC
LIMIT 1;

The reason you see a negative value for total sales is because qty_delta is a negative number for sales (because qty_delta in the table represents the change to the quantity in inventory, which is decreased when there is a sale).
Since qty_delta is negative, that means when you order by total_sales DESC, the first color in the result (blue) is actually the color with the smallest number of sales.

The lab is trying to demonstrate that if you include the result from this SQL query in the set of information you give the reflection step, that lets it see that the results are negative and figure out that qty_delta is likely negative for sales. This lets it make a new attempt at a sql statement to get a better answer.

The full SQL statement for the second attempt is:

SELECT color, SUM(ABS(qty_delta) * unit_price) AS total_sales
FROM transactions
WHERE action = 'sale'
GROUP BY color
ORDER BY total_sales DESC
LIMIT 1;

By taking the absolute value of qty_delta, we’re now putting the largest actual total sales first in the list, which is why we get white first for this second query.

Hope this helps.

2 Likes