Top 3 Defects SQL | Dataford Interview Questions

Write a query to find the top 3 manufacturing defects per facility over the last trailing 30 days at ATC Manufacturing.

Use the provided facility, defect type, and defect event data. Treat the trailing period as inclusive of today and the date 30 days before today. Break ties alphabetically by defect name, then by defect type ID.

Output

One row per qualifying facility and defect type, with columns facility_id, facility_name, defect_name, defect_count, and defect_rank.
Include only the three highest-ranked defect types for each facility with events in the period.
Sort by facility_name, then defect_rank, then defect_name.

Schema

facilities

Column	Type	Description
facility_idPK	INT	Unique ATC Manufacturing facility identifier
facility_name	VARCHAR(100)	Facility name

defect_types

Column	Type	Description
defect_type_idPK	INT	Unique defect type identifier
defect_name	VARCHAR(100)	Manufacturing defect name

defect_events

Column	Type	Description
defect_event_idPK	INT	Unique defect event identifier
facility_id	INT	Facility where the defect was detected
defect_type_id	INT	Detected defect category
detected_on	DATE	Date the defect was detected

Your question is Top 3 Defects SQL. Start with the requirements and the three tables 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