SQL Coding Question Count | Dataford Interview Questions

Bosch Building Technologies uses binary sensor readings from building automation systems to monitor equipment states. Write a PostgreSQL query that counts how often readings with values 0 and 1 occur for each building with at least one active device.

Requirements

Join active devices to their sensor readings.
Count readings equal to 0 and readings equal to 1 separately for each building.
Include active buildings whose devices have no readings, returning zero counts where appropriate.
Ignore NULL readings and values other than 0 or 1, and sort the results alphabetically by building name.

Schema

devices

Column	Type	Description
device_idPK	INTEGER	Unique device identifier
device_name	VARCHAR(100)	Bosch Building Technologies device name
building_name	VARCHAR(100)	Building where the device is installed
sensor_type	VARCHAR(50)	Type of monitored sensor
is_active	BOOLEAN	Whether the device is currently active

sensor_readings

Column	Type	Description
reading_idPK	INTEGER	Unique reading identifier
device_id	INTEGER	Device that produced the reading
reading_value	SMALLINT	Binary or other recorded sensor value

Your question is SQL Coding Question Count. Start with the requirements and the two 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