IoT Applications.sql

-- production IoT application job
-- display range remaining every 15 seconds
SELECT boardid,
tumble_end(eventTimestamp, interval '15' second) as TS,
CAST(ROUND(MIN(CAST(battery_level as numeric)),2) AS varchar)||'%' as state_of_charge,
MIN(CAST(trip_distance AS numeric)) AS distance_covered,
100-MIN(CAST(battery_level as numeric)) AS battery_pct_used,
MIN(CAST(trip_distance AS numeric))/(100-MIN(CAST(battery_level as numeric))) AS foot_per_battery_pct,
MIN(CAST(battery_level as numeric))*(MIN(CAST(trip_distance AS numeric))/(100-MIN(CAST(battery_level as numeric)))) as range_in_feet
FROM kickflips
GROUP by boardid, tumble(eventTimestamp, interval '15' second)