SQLmediumAggregations~15m

IoTech Manufacturing operates a fleet of industrial sensors across multiple...

Problem

IoTech Manufacturing operates a fleet of industrial sensors across multiple factory floors. Each sensor generates temperature and pressure readings every hour. The operations team needs to identify sensors that have shown concerning performance patterns: sensors where the average temperature over the last 7 days exceeds 85°C AND the temperature variance (difference between max and min) exceeds 20°C within that same period. Additionally, they want to see the total number of readings collected from each concerning sensor and the timestamp of its most recent reading. Return results ordered by average temperature descending, then by sensor_id ascending for tie-breaking.

Examples

Example 1

Example 2

Constraints

›Return sensors where average temperature > 85°C AND temperature variance > 20°C
›Include only readings from the last 7 days (assume current date is 2024-01-21)
›Handle NULL temperature values by excluding them from calculations
›Order by average temperature descending, then sensor_id ascending

Reference solution

Reference solution available after you attempt the question.

Ready to solve it?

Start a session on Mockbit #61. Write your SQL, run it against real data, and get graded on correctness, efficiency, query structure, and business understanding.