Top Tools by Uptime | Dataford Interview Questions

Write a SQL query to find the top three highest-performing Applied Materials manufacturing tools per day based on sensor uptime, handling ties appropriately.

Use valid sensor readings only, and define daily uptime as total uptime seconds divided by total monitored seconds for each tool. Include only active tools. Ties should share the same rank, so a day may return more than three tools.

Output

One row per qualifying tool and day with columns day, tool_id, tool_name, site_name, uptime_pct, and performance_rank.
Include only ranks 1 through 3, ordered by day, rank, decreasing uptime percentage, and tool ID.

Schema

sensor_readings

Column	Type	Description
reading_idPK	INT	Unique sensor reading identifier
tool_id	INT	Manufacturing tool associated with the reading
reading_date	DATE	Calendar day of the reading
uptime_seconds	INT	Seconds the tool was operational
monitored_seconds	INT	Seconds covered by the sensor measurement

tools

Column	Type	Description
tool_idPK	INT	Unique manufacturing tool identifier
tool_name	VARCHAR(100)	Applied Materials tool name
tool_model	VARCHAR(80)	Tool model or product family
site_id	INT	Manufacturing site containing the tool
status	VARCHAR(20)	Operational status of the tool

manufacturing_sites

Column	Type	Description
site_idPK	INT	Unique manufacturing site identifier
site_name	VARCHAR(100)	Manufacturing site name
region	VARCHAR(50)	Geographic region of the site

Your question is Top Tools by Uptime. 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