Guided Project: Customers and Products Analysis Using SQL - Screen 4

thuy-thuy · November 7, 2021, 4:49pm

To find out the products that are almost out of stock, the guidance says: “The low stock represents the quantity of each product sold divided by the quantity of product in stock. We can consider the ten lowest rates. These will be the top ten products that are (almost) out-of-stock.”
SQL Fundamentals Guided Project | Dataquest
But to my understanding, the ten highest rates should be considered, not the lowest. When the quantity in stock is small and the quantity ordered is large, the division of SUM(quantityordered) by quantityinstock should result in large number and indicates that the product’s selling is good.

The instruction left me so confused. Anyone find same issue? And if you took the highest quotients, did it affect the rest of the answer to Question 1?

jesmaxavier · November 8, 2021, 6:39am

I agree with you on this. The formula did not seem to make sense. I also tried Googling for a low stock formula that would make more sense but could not find one.

This is one of the sites I came across that highlights some of the formulas associated to inventory
management but I could not find anything close to the formula given here.

Hopefully DQ could shed some light on this.

leonhekkert · November 23, 2021, 3:01pm

I automatically read it like

low stock = stock / SUM(ordered).

However, when I limit to 10 items and also limit the best performing items to 10 there is no overlap between those.

Did you succeed in solving this question in the meantime?

thuy-thuy · November 24, 2021, 2:31am

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.

thuy-thuy · November 24, 2021, 2:42am

I think the formula instructed does make sense but we have to add different condition or limit to the result (instead of 10 as instructed), and I still think going for the lowest quotients is counter-logic.

Did you successfully find more reliable formula(s) for the low stock anyway?

jesmaxavier · November 24, 2021, 5:35am

@thuy-thuy I was talking about the formula being wrong with respect to the desired output. If we wanted the items with the lowest stock, as you have suggested, I would have agreed with the ten highest rates and not the ten lowest ones.

E.g
Item: Chips
Ordered: 15
In-stock: 3
Low Stock = 15/3= 5

Item: Coca-Cola can
Ordered:5
Instock: 20
Low Stock = 5/20= 0.25

Clearly we should be ordering more Chips than Coca-Cola.
So either this is the wrong formula given the condition is to select the lowest low-stock rates or the formula is right and the condition is wrong.

I think your second approach is best. However I think the below query requires a second look (I could be wrong. Please do correct me if I am wrong, still learning )

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
),

If you have to give HAVING low_stock >= 0.5, I believe you have to change the formula to
Low Stock = In Stock / Ordered. With the current way its written I do not think you would ever get 0.5. Consider the example below:

Item: Snickers
Ordered:30
Instock: 15
Low Stock = 30/15= 2

Based on what you were after i.e.

the Ordered value is definitely more than half but based on your formula we would get a ratio of 2 instead of 0.5.

thuy-thuy · November 24, 2021, 8:07am

@jesmaxavier I maintain that my formula order and the limit 0.5 are valid. Try running the query with reversed limit (i.e.: HAVING low_stock < 0.5) you will yield a long list of products whose quantityOrdered accounts for less than half of the quantityInStock (more than 50 products as I vaguely remember).

However, I wouldn’t say that it has to be 0.5, it could be 0.75, or 1, or even 2 (in this case the table yielded will include only a couple of items), but >= is a must. In case you try the other way around, going for quantityInStock/SUM(quantityOrdered), it should work too, but then the statement should be reversed too, to HAVING low_stock < X.

The example you gave was exactly that of low stock product, and if, say, it were among the products of the store, it would have showed up in the result table of my query. The table my query yielded included one item with SUM(quantityOrdered)/quantityInStock quotient at 67 something, but it was an outlier, most of the other products’ were somewhere between 0.5 and 2.

leonhekkert · November 24, 2021, 9:06am

Hi @thuy-thuy, @jesmaxavier,

first of all, I still think the formula for finding items that are low on stock should be stock / ordered where items with a low ratio are low on stock.
Intuitively this feels much better than items with high ratios given ordered / stock.
Nevertheless, I was able to get the same results as given on page 8 by running the following code (using the formula ordered / stock and looking for low ratio’s which I do not agree with.

WITH
pr_perf AS (
SELECT
	productCode,
	CAST(SUM(quantityOrdered) AS FLOAT) AS ordered,
	ROUND(SUM(quantityOrdered*priceEach)) AS performance
FROM orderdetails
GROUP BY productCode
),

low AS (
SELECT 
	products.productCode,
	products.productName,
	products.productLine,
	products.quantityInStock AS stock,
	ROUND(SUM(pr_perf.ordered / products.quantityInStock),2) AS low_stock
FROM products
JOIN pr_perf ON products.productCode = pr_perf.productCode
GROUP BY products.productCode
ORDER BY low_stock
LIMIT 10
)

SELECT 
	low.productName, 
	low.productCode,
	low.productLine, 
	low.low_stock, 
	pr_perf.ordered, 
	low.stock, 
	pr_perf.performance
FROM low
JOIN pr_perf ON low.productCode = pr_perf.productCode
ORDER BY pr_perf.performance DESC

This code:

Calculates the performance for all items.
Searches for 10 items with lowest ratio of ordered / stock.
Then it joins the 10 items with low_stock ratio with the performance.
Sorts on performance.

regarding your code @thuy-thuy, it think it calculates what you want correctly.
However, you’re still using subqueries within your WITH statement. Should’nt this be avoided?

thuy-thuy · November 24, 2021, 3:16pm

Hi Leon, I’m not against your thinking at all, to me both stock/ordered and ordered/stock make sense, the only thing that won’t make sense is pick out highest ratios for the former, or lowest for the latter (as per DQ instructions).

I’ve tried queries with stock/ordered formula and they resulted in the same table I got from my above queries, on 2 conditions for low_stock: ORDER BY low_stock DESC and LIMIT 20 (instead of 10).

There’s an issue in the instructions, also in the solution suggested at the end of the project (I found it in your above queries too): to focus on 10 products, the join query limits the number of outputs on the low_stock basis to 10. What doesn’t make sense is, if low_stock outputs are already limited to 10, you will always get performance of those 10 products whether or not the performance is high (meaning, it could be low or average, but it’s still included because its productCode appears in the limited low_stock table). Therefore, there’s no point in looking at the performance anymore. (I’m not sure I’m making my point clear or not, please let me know if you get me here, I’m still learning effective expression and explanation.)

Regarding the subqueries in my code, I don’t recall that subqueries should be avoided within WITH clause, but indeed subqueries should be avoided in general, except when it’s really necessary. I could’ve rewritten my queries to break that down, but since mine took less than 30ms to run, I didn’t bother to do so.

mikegohcs · April 25, 2022, 3:47am

I got one overlapping product: S12_1099.
You may refer to my codes.

WITH 
low_stock_pc AS (
SELECT productCode, ROUND (sum_ordered/quantityInStock,3) AS low_stock
  FROM ( SELECT p.productCode, p.quantityInStock, SUM(quantityOrdered)  AS sum_ordered
		   FROM products  p
		   JOIN orderdetails o
			 ON p.productCode = o.productCode
		  GROUP BY p.productCode) AS po
          ORDER BY low_stock DESC
          LIMIT 10
),
prod_perf AS(
SELECT productCode, SUM(quantityOrdered*priceEach) AS p_perf
  FROM orderdetails
 GROUP BY productCode
 ORDER BY p_perf DESC
 LIMIT 10
)
SELECT productCode
  FROM low_stock_pc
 WHERE productCode IN (SELECT productCode FROM prod_perf);

scout42389 · June 29, 2022, 4:38am

Screen 4 tasks us with using data to optimize the business’ inventory. I find the prompts that were given to be a strange way to go about improving profitability through inventory management. As other have pointed out, their formula for low stock is backwards - it would have you reorder items that are already massively overstocked. Additionally, we are given 30 months of data. It makes little business sense to base your inventory purchase quantity on 30 cumulative months of sales data. I decided to look at just the previous 6 months’ worth of data. This does two things - it allows you to order a quantity sufficient for 6 months’ worth of sales (can easily be reduced to fewer months if the business needs to run a leaner inventory) instead of a quantity sufficient for 30 months of sales, AND it takes new trends into account. Items that sold well years ago but are no longer selling shouldn’t be restocked. Frankly it would be best to break the timeframes down into quarters or months to identify sales trends. Maybe I’ll try that exercise in the future.

Additionally, inventory management requires goals. I have set a goal of carrying inventory of what is typically sold between 6 months and two years. So 6 months to 24 months of stock (or a ‘stock’ of 1 to 4).

Based on my results, I would recommend the following for this business.
Purchase:
1960 BSA Gold Star DBD34 x 500
1968 Ford Mustang x 250
1997 BMW F650 ST x 150
1928 Ford Phaeton Deluxe x 100

More importantly, there are 99 items which have enough product on the shelf to last between 2.5 and 38 years at current sales volume. That’s an unhealthy amount of inventory to be carrying, I would recommend to significantly reduce that inventory by whatever means are available to the business owner.

Lastly, in addition to product performance = quantity ordered * price each, I would look at product profitability = quantity ordered * (price each - buy price) to determine profitability. Revenue is an incomplete picture, profits are much more important. Additionally, product performance and profitability should not have much of an impact on inventory management. Inventory management is based on demand. The business would be best served by ordering items that sell and not ordering items that don’t sell, regardless of the revenue or profits generated. That is more of problem for marketing, and a successful marketing campaign would be reflected in the inventory and allow the inventory manager to adjust purchases accordingly.

I haven’t continued to the remaining screens so maybe a lot of this is covered there, but I thought it was a fun thought experiment.

SELECT p.productname, 
		p.quantityinstock, 
		SUM(od.quantityordered) AS quantity_ordered, 
		od.orderNumber,
		ROUND( 1.0 * p.quantityinstock / SUM(od.quantityordered),2) AS stock
   FROM orderdetails od
   JOIN products p
     ON p.productcode = od.productcode
  WHERE od.ordernumber IN (SELECT o.orderNumber
							 FROM orders o
							WHERE SUBSTR(o.orderdate,1,4) LIKE '2005')
  GROUP BY p.productName
  ORDER BY stock

Elena_Kosourova · June 29, 2022, 2:59pm

Hi @scout42389,

Could you please share your feedback:

I find the prompts that were given to be a strange way to go about improving profitability through inventory management. As other have pointed out, their formula for low stock is backwards - it would have you reorder items that are already massively overstocked. Additionally, we are given 30 months of data. It makes little business sense to base your inventory purchase quantity on 30 cumulative months of sales data.

with the Content & Product teams of Dataquest?Just click the ? button in the upper-right corner of any screen of the Dataquest learning platform, select Share Feedback, fill in the form, and send it. Thanks!