Efficient Telemetry Aggregation Query

MediumSQL · PostgreSQL

Problem

Caterpillar's fleet monitoring system needs a daily summary of telemetry for active machines operating at North American sites. Write a PostgreSQL query that aggregates readings while preserving active machines that have no telemetry during the reporting period.

Requirements

Include active machines at sites where region = 'North America' for January 1 through January 3, 2026.
Return one row per machine and telemetry date with reading count, average engine temperature, and maximum fuel rate.
Preserve machines without readings using LEFT JOIN, and label each row as NO DATA, UNAVAILABLE, HIGH, ELEVATED, or NORMAL based on the aggregated temperature.
Order the result by machine ID and telemetry date, with machines without readings included at the end of their machine group.

Schema

cat_sites

Column	Type	Description
site_idPK	INT	Unique Caterpillar operating or test site identifier
site_name	VARCHAR(120)	Name of the operating or test site
region	VARCHAR(40)	Geographic region used for reporting

cat_machines

Column	Type	Description
machine_idPK	INT	Unique machine identifier
model	VARCHAR(40)	Caterpillar machine model
site_id	INT	Operating site reference
status	VARCHAR(20)	Current machine lifecycle status

telemetry_readings

Column	Type	Description
reading_idPK	INT	Unique telemetry reading identifier
machine_id	INT	Machine that emitted the reading
reading_at	TIMESTAMP	Timestamp when the reading was captured
engine_temp_c	DECIMAL(6,2)	Engine temperature in degrees Celsius
fuel_rate_lph	DECIMAL(8,2)	Fuel consumption rate in liters per hour

Tablescat_sitescat_machinestelemetry_readings