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 
    b.operator, 
	b.row_number, 
	b.height, 
	b.avg_height, 
	b.stddev_height, 
	b.ucl, 
	b.lcl,
	CASE 
        WHEN b.height BETWEEN b.lcl AND b.ucl THEN TRUE
        ELSE FALSE
    END AS alert
FROM ( -- query from step 3
    SELECT 
        a.*,
        a.avg_height + 3 * (a.stddev_height / SQRT(5)) AS ucl, 
        a.avg_height - 3 * (a.stddev_height / SQRT(5)) AS lcl
    FROM ( -- subquery
        SELECT 
            ROW_NUMBER() OVER my_window AS row_number,
            AVG(height) OVER my_window AS avg_height, 
            STDDEV(height) OVER my_window AS stddev_height,
            height, 
            item_no, 
            operator
        FROM 
            manufacturing_parts
        WINDOW my_window AS (
            PARTITION BY operator
            ORDER BY item_no
            ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
        )
    ) AS a
WHERE a.row_number >= 5
) AS b
LIMIT 65;
Hidden output
Spinner
DataFrameas
alerts1
variable
WITH calculated_stats AS (
    SELECT 
        operator,
        item_no,
        height,
        ROW_NUMBER() OVER my_window AS row_number,
        AVG(height) OVER my_window AS avg_height,
        STDDEV(height) OVER my_window AS stddev_height
    FROM manufacturing_parts
    WINDOW my_window AS (
        PARTITION BY operator
        ORDER BY item_no
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    )
)
, control_limits AS (
    SELECT 
        operator,
        row_number,
        height,
        avg_height,
        stddev_height,
        avg_height + 3 * (stddev_height / SQRT(5)) AS ucl,
        avg_height - 3 * (stddev_height / SQRT(5)) AS lcl
    FROM calculated_stats
    WHERE row_number >= 5  -- Exclure les fenêtres incomplètes
)
SELECT 
    operator,
    row_number,
    height,
    avg_height,
    stddev_height,
    ucl,
    lcl,
    CASE 
        WHEN height BETWEEN lcl AND ucl THEN TRUE
        ELSE FALSE
    END AS alert
FROM control_limits
LIMIT 65;
Hidden output
1 hidden cell
Spinner
DataFrameas
alerts3
variable
WITH height_stats AS (
    SELECT 
        item_no,
        operator,
        height,
        ROW_NUMBER() OVER (PARTITION BY operator ORDER BY item_no) AS row_number,
        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 
        manufacturing_parts
)
SELECT 
    operator,
    row_number,
    height,
    avg_height,
    stddev_height,
    avg_height + (3 * stddev_height) AS ucl,
    avg_height - (3 * stddev_height) AS lcl,
    CASE 
        WHEN height > (avg_height + (3 * stddev_height/SQRT(5) )) AND height < (avg_height - (3 * stddev_height/SQRT(5))) 
        THEN TRUE 
        ELSE FALSE 
    END AS alert
FROM 
    height_stats
WHERE 
    row_number >= 5
ORDER BY 
    item_no
LIMIT 65;