Practice writing SQL queries against the
Since I am using Windows, it was a little challenging to follow the tutorials. I tried using psql on my VPS Ubuntu and whenever there were issues with queries it would just quit. Same thing happened in command prompt/powershell. Constant restarting and poor visualization of data made it hard for me to focus on extracting data, so I used pgAdmin 4 to view the data instead. I still had to use command lines, but the clear organization of data helped a lot. In addition, there is no need to login each time.
When did the outside sensor break and stop sending data?
I am not sure if I am running correct queries. First I ran a query to see if there was a day when sensor didn’t record by check with NULL. There were none.
SELECT * FROM sensor_data WHERE device = 'outside' AND recorded_at IS NULL;
Then I checked if NOT NULL command worked. It did.
Finally it turned out that I have been answering the question wrong. Jake pointed me into the right direction and the day it has stopped working last is January 20th at 03:38
SELECT device, min(recorded_at), max(recorded_at) FROM sensor_data GROUP BY device;
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), round(avg(reading), 2) as avg FROM sensor_data WHERE measurement = 'temperature' AND device = 'rootcellar' AND recorded_at BETWEEN '2016-12-01' and '2020-02-25' GROUP BY year, device ORDER BY device, year;
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 ORDER BY device, year;
It’s not the best query because it’s by year and to make sure that I am getting the most minimum from any device and not an average value of any kind (by some bizarre mistake), I ran this query:
SELECT recorded_at, device, min(reading) FROM sensor_data WHERE measurement = 'temperature' AND recorded_at BETWEEN '2018-01-01' and '2018-12-31' AND reading BETWEEN '-100' and '-7' GROUP BY recorded_at, device;
It seems that the above query was correct and the coldest it has been is -7.42. At first I thought that my results were completely wrong because I interpreted the numbers in Celcius and thought to my self that there is no way that the coldest day in Wisconsin is only -7.42 Celcius. Then, I realized that it’s Fahrenheit and felt a bit silly.
It was useful to find out the values anyway for the challenge question.
First query I ran is just to see when device started sending data and when it stopped.
SELECT min(recorded_at), max(recorded_at) FROM sensor_data WHERE device = 'device_16';
“2020-02-13 12:49:32.804098-05” “2020-02-25 19:59:57.307063-05”
Then I checked the lowest and highest temperature recorded by device.
SELECT min(reading), max(reading) FROM sensor_data WHERE device = 'device_16' AND measurement = 'temperature';
70.55 lowest and 86.49 highest
I realize that I probably made many inefficient queries, but I am pretty happy that I got to familiarize myself with the UI. I think its visualization helped me better understand the database and queries themselves.