week 4 More complex SQL queries

Query from Farm database

a. When did the outside sensor break and stop sending data?

SELECT device, max(recorded_at) as last_reported FROM sensor_data Group By device;

select the max recording from recorded_at to see when is the last time the data was posted to the database

b. Show the min and max temperature in the root cellar by year

SELECT extract(YEAR FROM recorded_at) as year, device, max(reading),min(reading) FROM sensor_data WHERE measurement = 'temperature' AND device = 'rootcellar' AND recorded_at BETWEEN '2015-12-26' AND '2021-02-25' GROUP BY year,device;

c. What was the lowest temperature recorded in 2018?

SELECT device, min(reading) FROM sensor_data WHERE measurement = 'temperature' AND recorded_at BETWEEN '2018-01-01' AND '2018-12-31' GROUP BY device ;

I found that the query needs to be in order for example GROUP BY needed to be the last query

---

Trying with my own sensor_reading

get data when is the start date and the last reported date

SELECT min(recorded_at), max(recorded_at) FROM sensor_data WHERE device = 'device_14' Group By device;

get the average temperature each day

SELECT recorded_at::date as day, device, round(avg(reading),2) FROM sensor_data WHERE device = 'device_14' AND measurement = 'temperature' AND recorded_at BETWEEN '2021-02-10' AND '2021-03-04' GROUP BY day,device;

Before casting the date, I extracted the day out of the recorded_at. The data did not align as it should be, so I decided to cast the data instead.

Question I had

- Is there any way that we can use the command (ie. min, max ) in the WHERE clause. For example, if i want to set the recorded_at between min(recorded_at) and max(recorded_at)