Hey Leon, I had similar issue when trying the 10 item limit. My explanation for that is: the products lowest in stock might not be those bringing in highest revenue, especially when you set the limit to 10.
To solve it, I actually tried 2 different approaches which yielded 2 tables, one with 10 products and the other with only 5 (and these 5 fitted into the table with 10), and both told that Classic Cars are to be the focus.
Approach 1: consider products low in stock first, then those in which that have highest revenues
WITH low_stock_products AS (
SELECT productCode,
ROUND(SUM(quantityOrdered*1.0)/(SELECT quantityInStock
FROM products p
WHERE p.productCode = o.productCode),2) AS low_stock
FROM orderdetails o
GROUP BY productCode
HAVING low_stock >= 0.5
/*
Why 0.5? Because when the quantity sold accounts for (more than) half of the total quantity in stock,
a store should pay attention to the product in question and already plan for restocking.
*/
),
best_performance_products AS (
SELECT productCode, SUM(quantityOrdered*priceEach) as product_performance
FROM orderdetails
WHERE productCode IN (SELECT productCode
FROM low_stock_products)
GROUP BY productCode
ORDER BY product_performance DESC
LIMIT 10
)
SELECT productCode, productName, productLine
FROM products
WHERE productCode IN (SELECT productCode
FROM best_performance_products);
Approach 2: sort out products low in stock and products with best performance, then compare the two lists to sort out the common values
WITH low_stock_products AS (
SELECT productCode,
ROUND(SUM(quantityOrdered*1.0)/(SELECT quantityInStock
FROM products p
WHERE p.productCode = o.productCode),2) AS low_stock
FROM orderdetails o
GROUP BY productCode
HAVING low_stock >= 0.5
),
best_performance_products AS (
SELECT productCode, SUM(quantityOrdered*priceEach) as product_performance
FROM orderdetails
GROUP BY productCode
ORDER BY product_performance DESC
LIMIT 20
),
sorted_table AS (
SELECT lsp.productCode
FROM low_stock_products lsp
JOIN best_performance_products bpp
ON lsp.productCode = bpp.productCode
)
SELECT productCode, productName, productLine
FROM products
WHERE productCode IN (SELECT productCode
FROM sorted_table);
It’s very different from the given instructions but beside these I had tried 2 other sets of queries, and the ones above gave me confidence so I went for that. Let me know what you think about my approaches please.
)
