Using SQL window functions and Statistical Process Control (SPC) to detect manufacturing deviations and identify operators requiring attention.
Manufacturing processes require continuous monitoring to ensure that products consistently meet quality standards.
This project analyzes historical manufacturing measurements to determine whether the production process is operating within acceptable limits. Using SQL window functions, I calculated rolling process statistics and dynamic Upper Control Limits (UCL) and Lower Control Limits (LCL) to identify measurements that fall outside the expected range.
The analysis was then transformed into an interactive Excel dashboard for monitoring operator performance and communicating actionable findings.
- Which manufacturing measurements fall outside the expected process limits?
- Which operators have the highest alert rates?
- Where should further process investigation or training be considered?
The manufacturing team needs a more systematic way to monitor production quality.
Rather than adjusting the process whenever an individual measurement looks unusual, the team wants to use Statistical Process Control (SPC) to determine whether measurements are within an acceptable range based on recent process behavior.
The acceptable range is defined by:
- Upper Control Limit (UCL) — the highest expected measurement
- Lower Control Limit (LCL) — the lowest expected measurement
Measurements outside these limits are flagged as potential process deviations requiring further investigation.
The available dataset contains manufacturing measurements for:
| Column | Description |
|---|---|
item_no |
Item produced |
length |
Length of the manufactured item |
width |
Width of the manufactured item |
height |
Height of the manufactured item |
operator |
Operator/machine responsible for the production |
The project was completed in two main stages:
I used SQL to transform the raw manufacturing measurements into an analytical dataset.
The analysis included:
- Partitioning measurements by operator
- Ordering observations chronologically by item
- Calculating rolling averages
- Calculating rolling standard deviation
- Creating dynamic UCL and LCL values
- Identifying measurements outside the control limits
- Calculating operator-level alert rates
I used a five-observation rolling window when calculating the process statistics.
The resulting SQL dataset was imported into Excel and transformed into an interactive monitoring dashboard.
The dashboard includes:
- KPI summary
- Interactive operator filter
- Statistical Process Control chart
- Operator alert-rate ranking
- Height distribution
- Detailed operator performance table
- Data-driven recommendations
I designed the dashboard to filter the control chart by operator.
A key part of the analysis was using SQL window functions to calculate process behavior over a rolling five-observation window.
Conceptually:
AVG(height) OVER (
PARTITION BY operator
ORDER BY item_no
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
)
and
STDDEV(height) OVER (
PARTITION BY operator
ORDER BY item_no
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
)The rolling statistics were then used to calculate the control limits. The resulting logic classified each observation as either:
- TRUE → outside the control limits
- FALSE → within the control limits
This allowed the analysis to move from simple descriptive statistics to process monitoring and anomaly detection.
The analysis covered:
| Metric | Result |
|---|---|
| Manufacturing Items | 420 |
| Operators | 20 |
| Total Alerts | 57 |
| Overall Alert Rate | 13.57% |
| Highest Alert Rate | 23.53% |
| Operator Requiring Attention | Op-4 |
Op-4 recorded the highest alert rate at 23.53%, with 4 alerts across 17 analyzed items.
This does not automatically mean that Op-4 is responsible for poor performance. Instead, the result serves as a signal for further investigation, process review, and potentially targeted training.
At the other end of the analysis, Op-18 recorded a 4% alert rate, indicating relatively consistent measurements within the observed data.
Rather than immediately assigning more production to the operator, this result should be investigated further to determine whether their operating practices can provide useful insights for improving process consistency across other operators.
The Excel dashboard was designed to allow users to move from the overall manufacturing picture to individual operator performance.
Displays:
- Actual product height
- Dynamic Upper Control Limit
- Dynamic Lower Control Limit
This allows users to identify measurements that move outside the expected process range.
Operators can be filtered individually to examine:
- Number of items
- Alerts
- Alert rate
- Average height
- Standard deviation
Operators are ranked by alert rate to highlight those requiring further investigation.
Provides a high-level view of the distribution of manufactured part heights.
Based on the analysis:
Op-4 recorded the highest alert rate among the operators analyzed.
Further investigation should examine:
- Operating procedures
- Machine conditions
- Production environment
- Product mix
- Measurement consistency
- Potential training requirements
Operators such as Op-18 recorded relatively low alert rates.
Their operating practices could be reviewed to determine whether useful process-control techniques can be replicated across other operators.
The control chart provides a framework for identifying measurements that fall outside the expected process range and can support earlier investigation before deviations become persistent.
- PostgreSQL / SQL
- SQL Window Functions
- Statistical Process Control (SPC)
- Excel
- Data Analysis
- Data Visualization
- KPI Development
- Business Recommendations
manufacturing-process-control-analysis/
│
├── README.md
│
├── sql/
│ └── process_control_analysis.sql
│
├── excel/
│ └── manufacturing_process_control.xlsx
│
├── screenshots/
│ └── operator-performance-dashboard.png
│
└── data/
└── README.md
This project demonstrates my ability to move beyond writing SQL queries and build an end-to-end analytical solution:
- SQL
- Data Analysis
- Excel Visualization and Pivot Tables
- Business Analysis
The final solution transforms raw manufacturing measurements into an interactive process-monitoring dashboard that helps stakeholders identify potential production deviations and prioritize areas for investigation.
The project strengthened my ability to connect SQL-based statistical analysis with business-facing reporting, taking a problem from raw data, through analytical logic, to a decision-ready dashboard.
I'm an early-career Data Engineer and Analytics professional focused on SQL, PostgreSQL, data modeling, data quality, and analytics.
I enjoy building practical data solutions that connect technical data work with real business decisions.
Connect with me on LinkedIn