Assignment
itp
and farm
databasesfarm
PostgreSQL database. Write queries to answer the followingQuestion 1 - When did outside sensor break and stop sending data?
SELECT * FROM sensor_data
WHERE device = 'outside'
ORDER BY id desc
LIMIT 10;
As per the above query and the results below I'd say it stopped sending around 8:38:45 on the 20th of January 2021.
Question 2 - Show the min and max temperature in the root cellar by year
SELECT
extract(YEAR FROM recorded_at) as year,
device,
min(reading) FILTER (WHERE measurement = 'temperature')::integer AS min,
max(reading) FILTER (WHERE measurement = 'temperature')::integer AS max
FROM sensor_data
WHERE measurement IN ('temperature') and device IN ('rootcellar')
GROUP BY year, device
ORDER BY device, year;
Question 3 - What was the lowest temperature recorded in 2018
SELECT
extract(YEAR FROM recorded_at) as year,
device,
min(reading) FILTER (WHERE measurement = 'temperature')::integer AS min
FROM sensor_data
WHERE measurement IN ('temperature') and extract(YEAR FROM recorded_at) = 2018
GROUP BY year, device
ORDER BY device, year;
As per this the lowest temperature recorded was -7 from the outside device. Here's a list of lowest temperatures for each place otherwise.