[SQL코드카타] 83~85번 (LeetCode)

(83번) product-sales-analysis

--MySQL / ORACLE
SELECT B.PRODUCT_NAME, A.YEAR, A.PRICE
FROM SALES A, PRODUCT B
WHERE A.PRODUCT_ID = B.PRODUCT_ID

 

(84번) customer-who-visited-but-did-not-make-any-transactions/

-- MySQL / ORACLE
SELECT A.CUSTOMER_ID, COUNT(*) AS COUNT_NO_TRANS
FROM VISITS A LEFT JOIN TRANSACTIONS B
     ON A.VISIT_ID = B.VISIT_ID
WHERE 1=1
      AND B.TRANSACTION_ID IS NULL
GROUP BY A.CUSTOMER_ID

 

(85번) rising-temperature

> 날짜 간 차이구하기 & 날짜 더하기 빼기 : here

-- MySQL
SELECT ID
FROM Weather A
WHERE 1=1
      AND A.temperature > (select temperature
                         from weather
                         where recordDate = DATE_SUB(A.recordDate,INTERVAL 1 DAY))
         -- 셀프조인할 때 비교대상을 명확하게 해주기 위해 A.recordDate

-- MySQL (2)
SELECT a.id
FROM  weather a,weather b
WHERE 1=1
      and a.temperature > b.temperature 
      and DATEDIFF(a.recordDate,b.recordDate)=1
-- ORACLE
-- Oracle은 날짜 차이를 구하는 함수가 별도로 없다고 한다.
SELECT a.id
FROM  weather a,weather b
WHERE 1=1
      and a.temperature > b.temperature 
      and a.recordDate - b.recordDate = 1

 

Q. 해결해볼 부분..

> 이렇게 보면 

SELECT TEMPERATURE - PRE_TEMP
FROM (SELECT id,
       recordDate,
       temperature,
       lag(temperature) over(order by recordDate) as pre_temp
        FROM Weather
)

> 값이 아래처럼 나와서

> WHERE절에 조건을 넣었는데 아래 결과가 나오는 이유를 모르겠다 ...

SELECT ID
FROM   (SELECT id,
       recordDate,
       temperature,
       lag(temperature) over(order by recordDate) as pre_temp
        FROM Weather
) A
WHERE TEMPERATURE- PRE_TEMP > 0