Skip to content

Manufacturing processes for any product is like putting together a puzzle. Products are pieced together step by step, and keeping a close eye on the process is important.

For this project, you're supporting a team that 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). SPC is an established strategy that uses data to determine whether the process works well. Processes are only adjusted if measurements fall outside of an acceptable range.

This acceptable range is defined by an upper control limit (UCL) and a lower control limit (LCL), the formulas for which are:

The UCL defines the highest acceptable height for the parts, while the LCL defines the lowest acceptable height for the parts. Ideally, parts should fall between the two limits.

Using SQL window functions and nested queries, you'll analyze historical manufacturing data to define this acceptable range and identify any points in the process that fall outside of the range and therefore require adjustments. This will ensure a smooth running manufacturing process consistently making high-quality products.

The data

The data is available in the manufacturing_parts table which has the following fields:

  • item_no: the item number
  • length: the length of the item made
  • width: the width of the item made
  • height: the height of the item made
  • operator: the operating machine
Spinner
DataFrameas
alerts
variable
SELECT
	mp.operator,
	mp.row_number,
	mp.height,
	mp.avg_height,
	mp.stddev_height,
	mp.ucl,
	mp.lcl,
	CASE WHEN mp.height BETWEEN mp.lcl AND mp.ucl THEN FALSE ELSE TRUE END AS alert --checks whether a value is or is not BETWEEN two other values and flagging them as either TRUE or FALSE
FROM (
	SELECT
		m.*,
		m.avg_height - 3 * m.stddev_height / SQRT(5) AS lcl, --lower control limit
		m.avg_height + 3 * m.stddev_height / SQRT(5) AS ucl --upper control limit
	FROM (
		SELECT
			operator,
			item_no,
			ROW_NUMBER() OVER manufacturing_window AS row_number,
			height,
			AVG(height) OVER manufacturing_window AS avg_height,
			STDDEV(height) OVER manufacturing_window AS stddev_height
		FROM manufacturing_parts
		WINDOW --WINDOW clause to be reused across a few functions (stddev_height, avg_height, row_number)
			manufacturing_window AS (
				PARTITION BY operator 
				ORDER BY item_no 
				ROWS BETWEEN 4 PRECEDING AND CURRENT ROW --a window function of length 5 to calculate the control limits, considering rows up to and including the current row
			)
	) AS m
	WHERE row_number >= 5 --a filter to remove incomplete window rows
) AS mp
ORDER BY mp.item_no