Skip to content

Briefing

Manufacturing department wants to improve how they monitor and control a manufacturing process. The goal is to implement a more methodical approach known as statistical process control (SPC). Processes are only adjusted if measurements fall outside of an acceptable range.

Objective

Create an alert that flags whether the height of a product is within the control limits for each operator using a formula that was provided.

Spinner
DataFrameas
df
variable
-- Then took a preview of the dataset
SELECT *
FROM manufacturing_parts
LIMIT 5;
Spinner
DataFrameas
alerts
variable
-- this CTE gathers the avg and std height which is used later in the formula 
WITH measures AS(
	SELECT item_no,
	AVG(height) 
		OVER(partition by operator order by item_no ROWS BETWEEN 4 PRECEDING AND CURRENT ROW ) 
			AS avg_height,
	STDDEV(height) 
		OVER(partition by operator order by item_no ROWS BETWEEN 4 PRECEDING AND CURRENT ROW ) 
			AS stddev_height 
FROM public.manufacturing_parts),

limits AS(
SELECT 
	row_number() 
		OVER(partition by operator order by item_no) 
			AS row_number,
	operator,
	height,
	avg_height,
	stddev_height,
-- below is the formula provided ucl for upper limit, lcl lower limit
	avg_height +3 * stddev_height/sqrt(5) AS ucl,
	avg_height - 3 * stddev_height /sqrt(5) AS lcl
FROM public.manufacturing_parts
JOIN measures
USING (item_no) 
)

SELECT 
	limits.*,
-- the CASE clause creates the alert
	CASE WHEN 
		height <= ucl AND height >= lcl THEN FALSE
	ELSE TRUE END AS alert
FROM limits 
WHERE row_number >= 5

Conclusion

57 products were outside of the control limits. Further investigation required.