197. Rising Temperature

---

Link

Write an SQL query to find all dates' Id
with higher temperatures compared to its previous dates (yesterday).

Return the result table in any order.

The query result format is in the following example.

Example

풀이

---

📌 Self Join 문제가 헷갈릴 때는 각 테이블과 컬럼에 대해서 alias를 명확히 붙여주자

1. SELF JOIN 으로 전 날(yesterday) 테이블 생성
   혼동 방지를 위해서 각 컬럼의 alias를 명확히 붙여주고 테이블을 확인하자.

SELECT today.id AS today_id
     , today.recordDate AS today_recordDate
     , today.temperature AS today_temperature
     , yesterday.id AS yesterday_id
     , yesterday.recordDate AS yesterday_recordDate
     , yesterday.temperature AS yesterday_temperature
FROM weather AS today
    INNER JOIN weather AS yesterday ON DATE_ADD(yesterday.recordDate, INTERVAL 1 DAY) = today.recordDate

{"headers": ["today_id", "today_recordDate", "today_temperature", "yesterday_id", "yesterday_recordDate", "yesterday_temperature"]
,"values": [[2, "2015-01-02", 25, 1, "2015-01-01", 10], [3, "2015-01-03", 20, 2, "2015-01-02", 25], [4, "2015-01-04", 30, 3, "2015-01-03", 20]]}

2. 오늘 기온이 전날의 기온보다 높은 경우만 출력하는 조건 생성

WHERE today.temperature > yesterday.temperature

3. 최종 SELECT 쿼리 조건 정리

SELECT today.id AS ID
FROM weather AS today
    INNER JOIN weather AS yesterday ON DATE_ADD(yesterday.recordDate, INTERVAL 1 DAY) = today.recordDate
WHERE today.temperature > yesterday.temperature

My Answer

---

SELECT today.id AS ID
FROM weather AS today
    INNER JOIN weather AS yesterday ON DATE_ADD(yesterday.recordDate, INTERVAL 1 DAY) = today.recordDate
WHERE today.temperature > yesterday.temperature