Skip to content
Project: Evaluate a Manufacturing Process using SQL
  • AI Chat
  • Code
  • Report
  • Manufacturing processes for any product is like putting together a puzzle. Products are pieced together step by step and it's important to keep a close eye on the process.

    For this project, you're supporting a team that wants to improve the way they're monitoring and controlling 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 is working 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:

    Using SQL window functions, 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
    Unknown integration
    DataFrameavailable as
    alerts
    variable
    -- Write your query here
    
    -------------------------------------- FIRST ATTEMPT ------------------------------------------
    
    -- WITH 
    -- -- CTE to Calculate moving average and moving standard deviation
    -- avg_and_stddev AS (
    -- 	SELECT
    -- 		item_no,	
    -- 		operator,
    -- 		ROW_NUMBER() OVER (PARTITION BY operator ORDER BY item_no) AS row_number,
    -- 		height,
    -- 		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,
    -- 		COUNT(height) OVER
    -- 		(
    -- 			PARTITION BY operator 
    -- 			ORDER BY item_no 
    -- 			ROWS BETWEEN 4 PRECEDING AND CURRENT ROW		
    -- 		) AS row_count
    -- 	FROM manufacturing_parts
    -- ),
    -- --CTE to Calculate upper and lower control limits
    -- ucl_and_lcl AS (
    -- 	SELECT
    -- 		*,
    -- 		avg_height + 3 * (stddev_height / SQRT(5)) AS ucl,
    -- 		avg_height - 3 * (stddev_height / SQRT(5)) AS lcl
    -- 	FROM avg_and_stddev
    -- )
    
    -- -- Creating an alert to evaluate the manufacturing process
    -- SELECT
    -- 	operator,
    -- 	row_number,
    -- 	height,
    -- 	avg_height,
    -- 	stddev_height,
    -- 	ucl,
    -- 	lcl,
    -- 	CASE WHEN height > ucl OR height < lcl THEN TRUE
    -- 		 ELSE FALSE END AS alert
    -- FROM 
    -- 	ucl_and_lcl
    -- WHERE 
    -- 	row_count = 5
    
    
    ----------------------------------- IMPROVED SOLUTION -----------------------------------------
    
    SELECT
        sub.operator,
    	ROW_NUMBER() OVER (PARTITION BY sub.operator ORDER BY sub.item_no) AS row_number,
    	sub.height,
        sub.avg_height,
        sub.stddev_height,
        sub.ucl,
        sub.lcl,
        (sub.height > sub.ucl OR sub.height < sub.lcl) AS alert
    FROM (
        SELECT
    		item_no,
            height,
            operator,
            AVG(height) OVER wd AS avg_height,
            STDDEV(height) OVER wd AS stddev_height,
            AVG(height) OVER wd + 3 * (STDDEV(height) OVER wd / SQRT(5)) AS ucl,
            AVG(height) OVER wd - 3 * (STDDEV(height) OVER wd / SQRT(5)) AS lcl,
            COUNT(height) OVER wd AS row_count
        FROM
            manufacturing_parts
        WINDOW wd AS (
            PARTITION BY operator 
            ORDER BY item_no 
            ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
        )
    ) AS sub
    WHERE
        sub.row_count = 5;
    
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.