LeetCode 197 Rising Temperature.sql

set search_path to data_sci;

DROP TABLE if EXISTS weather;

create table weather (
id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
recordDate date,
temperature INTEGER,
device_id INTEGER
);

INSERT into weather
(recordDate, temperature, device_id)
VALUES
('2015-01-01' , 10,1 ),
('2015-01-02' , 25,2 ),
('2015-01-03' , 30,2 ),
('2015-01-04' , 40,2 ),
('2015-01-05' , 30,1 ),
('2015-01-06' , 20,1 ),
('2015-01-07' , 40,3 )

;
-- Join recordDate with next recordDate.
-- PostgreSQL overloads operators so they work with dates and intervals.
-- You can add 1 to the date to get the next day
select w.recordDate, w.id from weather w
JOIN weather w2 on w.recordDate = (w2.recordDate + 1)
where w.temperature > w2.temperature


-- Find first date of each device
select dev_rank.recordDate, dev_rank.id, dev_rank.device_id,dev_rank.rank
from (select w2.recordDate, w2.id, w2.device_id ,
	dense_rank() over (partition by w2.device_id order by w2.recordDate ASC) as rank
	from weather w2) as dev_rank 
where dev_rank.rank = 1


-- Alternate solution.
-- Ss long as I only need the device_id and min date, then a simple group by will work
select min(w.recordDate), w.device_id
from weather w
group by w.device_id
ORDER by w.device_id ASC