Skip to content

Welcome! You are now in DataLab.

You successfully completed your project and are looking for some additional related challenges. This DataLab workbook contains the official solution from our curriculum staff, along with Additional Challenges at the bottom. If you would like a quick overview of DataLab, please refer to the help menu. You can easily share your project with your friends and colleagues when you're done.

Good luck with your additional challenges!

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
	*,
	case
		when sq.ucl < sq.height or sq.height < sq.lcl
	then true
	else false
	end as alert
from (
	select
		parts.operator,
		stat.row_number,
		parts.height,
		stat.avg_height,
		stat.stddev_height,
		stat.avg_height + 3 * stat.stddev_height / sqrt(5) as ucl,
		stat.avg_height - 3 * stat.stddev_height / sqrt(5) as lcl
	from (
		select
			p.item_no,
			row_number() over w as row_number,
			avg(p.height) over w as avg_height,
			stddev(p.height) over w as stddev_height
		from public.manufacturing_parts as p
		window w as (
			partition by p.operator order by p.item_no
			rows between 4 preceding and current row
		)
	) as stat
	join public.manufacturing_parts as parts using (item_no)
	where stat.row_number >= 5
	order by parts.item_no asc
) as sq;

Extended Project below

After identifying individual out-of-control products, the team suspects that certain operators may need further training. They want to pinpoint operators whose machines consistently produce parts outside control limits.

Using common table expressions and aggregations you will identify operators whose machines have a higher-than-average number of alerts compared to the total alerts for all operators.

Spinner
DataFrameas
df
variable
with alerts as (
	select
		*,
		case
			when sq.ucl < sq.height or sq.height < sq.lcl
		then true
		else false
		end as alert
	from (
		select
			parts.operator,
			stat.row_number,
			parts.height,
			stat.avg_height,
			stat.stddev_height,
			stat.avg_height + 3 * stat.stddev_height / sqrt(5) as ucl,
			stat.avg_height - 3 * stat.stddev_height / sqrt(5) as lcl
		from (
			select
				p.item_no,
				row_number() over w as row_number,
				avg(p.height) over w as avg_height,
				stddev(p.height) over w as stddev_height
			from public.manufacturing_parts as p
			window w as (
				partition by p.operator order by p.item_no
				rows between 4 preceding and current row
			)
		) as stat
		join public.manufacturing_parts as parts using (item_no)
		where stat.row_number >= 5
		order by parts.item_no asc
	) as sq
)

select
	*
from (
	select
		a.operator,
		sum(a.alert::int)::float / count(*) as alert_rate,
		avg.alert_rate as avg_alert_rate
	from alerts as a
	join (
		select sum(alerts.alert::int)::float / count(*) as alert_rate
		from alerts
	) as avg on true
	group by a.operator, avg.alert_rate
) as op
where op.alert_rate > op.avg_alert_rate
order by op.alert_rate desc;