Today’s leetcode challenge was with regards to handling dates in database.

Given the table ‘Weather’, I had to find out the dates that had higher temeperature than the previous dates.

Table: Weather

id	recordDate	temperature
1	2015-01-01	10
2	2015-01-02	25
3	2015-01-03	20
4	2015-01-04	30

Expected Output:

id
2
4

I tried using the function ‘LAG()’ to get temperature of the previous dates. However, this failed in one of the test cases. My solution failed to take account of the fact that the dates may not be consecutive and there could be missing temperature information for one or two dates.

 SELECT id FROM(
             SELECT id, recordDate, Temperature, 
             lag(Temperature) OVER (ORDER BY id) 
             AS prev FROM Weather)
 WHERE temperature > prev; 

Submitted Solution Taken From Forums:

Solution 1

SELECT W1.Id 
FROM Weather W1 
WHERE W1.RecordDate - 1 IN 
     (SELECT W2.RecordDate 
     FROM Weather W2 
     WHERE W1.Temperature > W2.Temperature)

It has been quite taxing trying to understand this query. I asked help from colleague to understand this query. My explanation may not be accurate. If so let me know then.

Query 1

Just like a normal ‘SELECT’ query, the processing sequence follows from the ‘SELECT’ to ‘WHERE’ and then ‘IN’.

SELECT W1.Id 
FROM Weather W1 
WHERE W1.RecordDate - 1

W1.id	W1.recordDate	w1.Temperature
1	2015-01-02	25
2	2015-01-03	20
3	2015-01-04	30

Query 2

The ‘WHERE’ clause in the subquery then tests the W1.Temperature parameter from the ‘W1’ against the W2.Temperature and only return those records that have higher temperature than ‘W2.recordDate’. The below table is the W1 and W2 tables queried from.

IN (SELECT W2.RecordDate 
     FROM Weather W2 
     WHERE W1.Temperature > W2.Temperature)

W1.id	W1.recordDate	W1.Temperatue	W2.id	W2.recordDate	W2.Temperature
1	2015-01-02	25	1	2015-01-01	10
2	2015-01-03	20	2	2015-01-02	25
3	2015-01-04	30	3	2015-01-03	20
4			4	2015-01-04	30

When both the query 1 and query 2 are combined, it returns the ‘id’ that fulfils the condition of W1.Temperature > W2.Temperature.

Solution 2

SELECT DISTINCT a.Id
FROM Weather a,Weather b
WHERE a.Temperature > b.Temperature
AND (a.Recorddate - b.Recorddate) = 1;
 Runtime = 27.43% 

Query 1

The first of the query with the ‘SELECT’ clause is basically a self cross join.

 SELECT *
 FROM Weather a,Weather b

a.id	a.recordDate	a.Temperatre	b.id	b.recordDate	b.Temperature
4	2015-01-04	30	4	2015-01-04	30
4	2015-01-04	30	3	2015-01-03	20
4	2015-01-04	30	2	2015-01-02	25
4	2015-01-04	30	1	2015-01-01	10
3	2015-01-03	20	4	2015-01-04	30
3	2015-01-03	20	3	2015-01-03	20
3	2015-01-03	20	2	2015-01-02	25
3	2015-01-03	20	1	2015-01-01	10
2	2015-01-02	25	4	2015-01-04	30
2	2015-01-02	25	3	2015-01-03	20
2	2015-01-02	25	2	2015-01-02	25
2	2015-01-02	25	1	2015-01-01	10
1	2015-01-01	10	4	2015-01-04	30
1	2015-01-01	10	3	2015-01-03	20
1	2015-01-01	10	2	2015-01-02	25
1	2015-01-01	10	1	2015-01-01	10

Query 2

The second part of the query is where each records are tested based on the conditions specified.

 WHERE a.Temperature > b.Temperature
 AND (a.Recorddate - b.Recorddate) = 1;

Solution 3

WITH tmp AS (
SELECT id, RecordDate, Temperature,
LAG (Temperature,1) OVER (ORDER BY RecordDate) AS prev_Temperature,
LAG (RecordDate,1) OVER (ORDER BY RecordDate) AS prev_RecordDate
FROM Weather)

SELECT id FROM tmp WHERE Temperature > prev_Temperature AND
(RecordDate-prev_RecordDate)=1  

This query is very similar to my submission except that inorder to accurately test the temperature of the date vs previous date, there’s an ‘AND
(RecordDate-prev_RecordDate)=1′ condition.

• Facebook
• Instagram
• LinkedIn
• GitHub