Which complaints have type Product Failure?
select * from complaints where complaint_type_code = "Product Failure"
Which complaint statuses are among those records?
select distinct complaint_status_code from complaints where complaint_status_code in (select complaint_status_code from complaints where complaint_type_code = "Product Failure")
Count the occurrences of each complaint status in the above.
select complaint_status_code, count(*) from complaints where complaint_type_code = "Product Failure" group by complaint_status_code