Bucket Records with CASE | Dataford Interview Questions

The Airbus Helicopters HCare maintenance quality dashboard needs a consistent classification for inspection scores. Write a PostgreSQL query using CASE WHEN to bucket each inspection record.

Requirements

Return the inspection ID, helicopter model, original score, and a score_category.
Classify scores as Critical below 50, Review from 50 through 79, Acceptable from 80 through 100, Invalid Score outside that range, and Not Scored when the score is NULL.
Order the results by inspection_id ascending.

Schema

inspection_records

Column	Type	Description
inspection_idPK	INTEGER	Unique inspection identifier
helicopter_model	VARCHAR(50)	Airbus Helicopters model inspected
inspection_score	INTEGER	Inspection score, normally from 0 to 100
inspection_status	VARCHAR(20)	Current inspection workflow status

Your question is Bucket Records with CASE. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

The Airbus Helicopters HCare maintenance quality dashboard needs a consistent classification for inspection scores. Write a PostgreSQL query using CASE WHEN to bucket each inspection record.

Requirements

Return the inspection ID, helicopter model, original score, and a score_category.
Classify scores as Critical below 50, Review from 50 through 79, Acceptable from 80 through 100, Invalid Score outside that range, and Not Scored when the score is NULL.
Order the results by inspection_id ascending.

Schema

inspection_records

Column	Type	Description
inspection_idPK	INTEGER	Unique inspection identifier
helicopter_model	VARCHAR(50)	Airbus Helicopters model inspected
inspection_score	INTEGER	Inspection score, normally from 0 to 100
inspection_status	VARCHAR(20)	Current inspection workflow status

Your question is Bucket Records with CASE. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.