Use the `farm` database. Write queries to answer the following questions:
When did the outside sensor break and stop sending data?
select max(recorded_at) from sensor_data where device = 'outside'Result ---> 2019-01-26 11:57:47+00
Today is the 2019-02-17, so it has not sent data for over a month
Show the min and max temperature in the root cellar by year
Original attempt:
>>> SELECT recorded_at::date as date, extract(YEAR FROM recorded_at) as year, device, max(reading), min(reading) FROM sensor_data WHERE measurement = 'temperature' AND device = 'rootcellar' GROUP BY date, year, deviceUNION SELECT distinct year, max(reading),min(reading);^ I thought I needed to cast the date into something, but turns out I just needed to extract the year from the date, give it a new column name, along with the rest of the column names - I understand, now that the bit after the extract parenthesis() are naming the column titles of the output - and then execute the rest of the functions
>>> SELECT extract(YEAR FROM recorded_at) as year, device, max(reading), min(reading) FROM sensor_data WHERE measurement = 'temperature' AND device = 'rootcellar' GROUP BY year, device; year | device | max | min ------+------------+-------+------- 2016 | rootcellar | 69.00 | 35.10 2017 | rootcellar | 67.10 | 27.80 2018 | rootcellar | 70.90 | 34.90 2019 | rootcellar | 48.40 | 29.30(4 rows)
What was the lowest temperature recorded 2018?
>>> SELECT extract(YEAR FROM recorded_at) as year, device, min(reading) FROM sensor_data WHERE measurement = 'temperature' AND recorded_at BETWEEN '2018-01-01' and '2018-12-31' GROUP BY year, device; year | device | min ------+------------+------- 2018 | basement | 42.00 2018 | livingroom | 43.16 2018 | office | 38.30 2018 | outside | -7.42 2018 | rootcellar | 34.90 2018 | slab | 44.06 2018 | woodstove | 40.82Looks like the outside had the lowest temp in 2018, with a chilly -7.42 degrees!
Challenge: Which sensor recorded the lowest temperature 2018 and when? Hint: you need a subquery.
Well we know from above that the outside device had the lowest temp. So this works:
>>> SELECT extract(YEAR FROM recorded_at) as year, device, min(reading) FROM sensor_data WHERE measurement = 'temperature' AND device = 'outside' AND recorded_at BETWEEN '2018-01-01' and '2018-12-31' GROUP BY year, device;year | device | min ------+---------+------- 2018 | outside | -7.42But we need the date
>>> SELECT extract(YEAR FROM recorded_at) as year, (SELECT recorded_at::date) as date, device, min(reading) FROM sensor_data WHERE measurement = 'temperature' AND device = 'outside' AND recorded_at BETWEEN '2018-01-01' and '2018-12-31' GROUP BY year, device, date;^^^ this gives us every day of 2018 and the min values, not what we want, but luckily since the min value is -7.42, and you can see it right there, answer solved. However, what if we wanted the highest temp, or the lowest temp was somewhere else in the year? For that we need to do something else..
year | date | device | min ------+------------+---------+------- 2018 | 2018-01-01 | outside | -7.42 2018 | 2018-01-02 | outside | -4.36 2018 | 2018-01-03 | outside | 6.62 2018 | 2018-01-04 | outside | -4.54 2018 | 2018-01-05 | outside | -1.84 2018 | 2018-01-06 | outside | -3.82 2018 | 2018-01-07 | outside | 8.06 2018 | 2018-01-08 | outside | 24.26 2018 | 2018-01-09 | outside | 24.62>>> SELECT extract(YEAR FROM recorded_at) as year, (SELECT recorded_at::date) as date, device, min(reading) FROM sensor_data WHERE measurement = 'temperature' AND device = 'outside' AND recorded_at BETWEEN '2018-01-01' and '2018-12-31' GROUP BY year, device, date ORDER by min;^^ Ordering by min helps us see which date is the coldest. I would like to figure out how to see only the day with the minimum value. I was having trouble doing so, I think I have to execute a union function or something...
year | date | device | min ------+------------+---------+------- 2018 | 2018-01-01 | outside | -7.42 2018 | 2018-01-04 | outside | -4.54 2018 | 2018-01-02 | outside | -4.36 2018 | 2018-01-06 | outside | -3.82 2018 | 2018-02-05 | outside | -3.28 2018 | 2018-01-05 | outside | -1.84 2018 | 2018-01-14 | outside | 0.32 2018 | 2018-02-08 | outside | 1.22 2018 | 2018-01-13 | outside | 1.94 2018 | 2018-02-06 | outside | 2.303) Write two queries that use data from your sensor.
SELECT recorded_at::date as date, extract(MINUTE FROM recorded_at) as minute, device, max(reading), min(reading) FROM sensor_data WHERE measurement = 'temperature' AND device = 'sc_sensor' GROUP BY date, minute, device;date | minute | device | max | min ------------+--------+-----------+-------+------- 2019-02-17 | 0 | sc_sensor | 74.66 | 72.68 2019-02-17 | 1 | sc_sensor | 74.66 | 72.86 2019-02-17 | 2 | sc_sensor | 74.66 | 72.50 2019-02-17 | 3 | sc_sensor | 74.66 | 72.86 2019-02-17 | 4 | sc_sensor | 74.66 | 72.86 2019-02-17 | 5 | sc_sensor | 74.66 | 72.50 2019-02-17 | 6 | sc_sensor | 74.66 | 72.50 2019-02-17 | 7 | sc_sensor | 74.66 | 72.32 2019-02-17 | 8 | sc_sensor | 74.66 | 72.50 2019-02-17 | 9 | sc_sensor | 75.02 | 72.50This gives me minute by minute max and min values, which is interesting, but I'd rather see which minute was the hottest in the hour - so I revised the query a little bit
SELECT extract(MINUTE FROM recorded_at) as minute, round(avg(reading), 2) as avg FROM sensor_data WHERE measurement = 'temperature' AND device = 'sc_sensor' GROUP BY minute ORDER BY avg desc;minute | avg --------+------- 41 | 74.30 40 | 74.22 42 | 74.18 43 | 74.15 34 | 74.13 44 | 74.12 45 | 74.08And because all the inputs are on the same day, there's no need for the date column.