I've been working on lab 5 Q7:

Find the po_ids that have ordered items costing more than the average price of all items, along with the total cost of those items.

Hint use a subquery to calculate the average price of items.

However the answer the checker expects doesn't seem to filter by average price or use subqueries at all. It just lists the grouped total_price for each po_id. In fact I only managed to solve this question by writing a query that did this without filtering by average price, or using a subquery at all. Literally just SELECT, SUM, FROM, JOIN and GROUP BY.

Checker Expected result:

|po_id | total_cost| |---------- | ----------| |AAA | 2217.5| |BBB | 255| |CCC | 5787.5| |DDD | 10000| |GGG | 1000| |HHH | 3030.0| |III | 2499.5 |

However because of the wording of the question and the mention of averages I think this may be incorrect and that the correct answer should be more filtered rather than just including the total grouped cost for every po_id. I think it should probably look more like this:

What I think the result should be

|po_id | total_cost| |----------| ----------| |AAA | 2217.5| |CCC | 5787.5| |GGG | 1000|

I think this is more likely the correct result because if you add up the prices for all the items then divide by how many types of items there are only AAA, CCC and GGG have ordered any items higher than the average price calculated in this way.

I'll probably still submit the checker correct answer anyway to get full marks through the automated marking system but I'm posting here just in case it's an unintentional error in the question that might need to be fixed or clarified for other students.

Hi,

I think the question means

Write an SQL query to find the purchase order IDs (po_id) whose total cost of ordered items exceeds the average price of all items. Include the po_id and the total cost for each purchase order that meets this condition. Of course, we should use a subquery.

Agree with you. Found that in the end it's requiring lines that do not do filtering, which may not require a subquery to finish indeed. Maybe the description is a bit confusing.

What was suggested above would be the best guide, aside for that, there aren't any formatting checks in the embedded Python code, so as long as you select the po_id and an aggregate function AS total_cost, that side of it should be fine.

If it can be done a different way then that's fine too, as mentioned there aren't any checks on how to complete it, but a subquery would be a more direct method of doing it.

ANONYMOUS wrote:

Agree with you. Found that in the end it's requiring lines that do not do filtering, which may not require a subquery to finish indeed. Maybe the description is a bit confusing.