why my inner join doesn't work on comparing columns?

user_15387628 0
Viewed 757

Write a SQL query to find all dates' id with higher temperature compared to its previous dates (yesterday).

Reference: https://leetcode.com/problems/rising-temperature/

Here is my code:

SELECT L.id AS id
FROM weather AS p 
INNER JOIN weather AS L ON p.recordDate = L.recorddate - 1
WHERE p.temperature < l.temperature

I don't know what's wrong with my answer, can someone help?

sql
4 Answers

Using Lag()

select id from (
    select id, temperature, Lag(temperature,1) over (order by recorddate) lastTemp
    from weather
)w
where temperature>LastTemp
user_153326500

LAG Function do the trick for You:

 WITH a AS(
    SELECT *, LAG(temperature) OVER(ORDER BY recordDate) PrevTmp FROM weather 
    )
    SELECT * FROM a
    WHERE a.PrevTmp<temperature
user_54808870

Look after dateadd function of sql. if L.recorddate is a proper date or datetime you need to use DateAdd function of sql when you compare instead of L.recorddate - 1 Like p.recordDate=dateadd(day, -1, l.recorddate)

user_87052280

MySQL typecasts your date to an integer when you subtract an integer from the date. This is not the case with PostgreSQL so while your solution would work for PostgreSQL, it wouldn't for MySQL.

For example:

With MySQL -

recordDate  recordDate-1
1998-11-01  19981100
1998-10-31  19981030

With PostgresSQL -

recordDate  recordDate-1
1998-11-01  1998-10-31
1998-10-31  1998-10-30

Possible solutions for MySQL:

  1. Use DATE_SUB() - eg, SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY) AS one_day_before_given_date

  2. Use LAG() window function - eg, SELECT LAG(temperature) OVER(ORDER BY date_) AS previous_temperature

user_77720290
Related