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
df
variable
-- Step 1: Define a common table expression (CTE) 's' to calculate row numbers, 
-- average height, and standard deviation of height for manufacturing parts 
-- using window functions.

WITH s AS (
    SELECT 
        item_no,
        ROW_NUMBER() OVER window_def AS row_number,  -- Assign row numbers to each item
        height,
        AVG(height)  OVER window_def AS avg_height,  -- Calculate average height over window
        STDDEV(height) OVER window_def AS stddev_height  -- Calculate standard deviation of height
    FROM manufacturing_parts AS m
    WINDOW window_def AS (  -- Define window: partition by operator, ordered by item number
        PARTITION BY operator 
        ORDER BY item_no 
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)  -- Look at current row and previous 4 rows
), 

-- Step 2: Create another CTE 'full_t' to calculate upper and lower control limits (UCL, LCL)
-- and set up an alert system to flag when heights fall outside control limits.
full_t AS (
    SELECT 
        s.item_no,
        s.row_number,
        s.height,
        s.avg_height, 
        s.stddev_height,
        ucl, 
        lcl,
        CASE 
            WHEN height BETWEEN lcl AND ucl THEN FALSE  -- No alert if height is within limits
            ELSE TRUE  -- Alert if height is outside control limits
        END AS alert
    FROM (
        SELECT 
            item_no,
            (avg_height + (3*stddev_height/SQRT(5))) AS ucl,  -- Calculate upper control limit
            (avg_height - (3*stddev_height/SQRT(5))) AS lcl  -- Calculate lower control limit
        FROM s
        WHERE row_number >= 5  -- Use only rows with sufficient preceding data
    ) AS a
    JOIN s USING(item_no)
    ORDER BY item_no
)

-- Step 3: Final SELECT query to view the complete table with item numbers, control limits, 
-- and any alerts triggered.
SELECT * FROM full_t;