Answer-first summary for fast verification
Answer: SUM, SIZE, FILTER
The goal is to count how many temperature readings exceed 100.00 degrees for each device. In Databricks SQL, the correct higher‑order function to filter elements in an array is filter(), not ARRAY_FILTER. The filter() function returns an array containing only the elements that satisfy the given condition. The solution works as follows: filter(deviceTemp, i -> i > 100.00) isolates temperature readings above the threshold. size() counts how many readings exceed 100 within each row. sum() aggregates these counts across all rows for each device. This combination efficiently calculates the total number of threshold exceedances per device. Therefore, the correct option is E, as it uses valid Databricks SQL functions and correctly applies array filtering, counting, and aggregation logic.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
You are tasked with identifying how often a temperature sensor exceeds a threshold temperature of 100.00 for each device. Each row in the dataset contains 5 readings collected every 5 minutes. Complete the SQL query by filling in the blanks with the appropriate functions.
Table Schema
SELECT
deviceId,
__(__(__ (deviceTemp, i -> i > 100.00)))
FROM devices
GROUP BY deviceId
SELECT
deviceId,
__(__(__ (deviceTemp, i -> i > 100.00)))
FROM devices
GROUP BY deviceId
A
SUM, COUNT, SIZE
B
SUM, SIZE, SLICE
C
SUM, SIZE, ARRAY_CONTAINS
D
SUM, SIZE, ARRAY_FILTER
E
SUM, SIZE, FILTER
