240819_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

SQL 코드카타

문제 링크
window 함수 몇 개면 간단히 해결되는 문제.

공연히 쿼리 최적화에 욕심내느라 시간쓰고 에너지 쓰지 말자.
무조건 빠르고 정확하게 답을 내는 게 1순위.

WITH result
     AS (SELECT user_id,
                spend,
                transaction_date,
                Rank()
                  OVER(
                    partition BY user_id
                    ORDER BY transaction_date) AS "ranking_date",
                Lag(spend)
                  OVER(
                    partition BY user_id
                    ORDER BY transaction_date) AS "preceding1",
                Lag(spend, 2)
                  OVER(
                    partition BY user_id
                    ORDER BY transaction_date) AS "preceding2"
         FROM   transactions)
SELECT user_id,
       spend            AS "third_transaction_spend",
       transaction_date AS "third_transaction_date"
FROM   result
WHERE  ranking_date = 3
       AND spend > preceding1
       AND spend > preceding2
ORDER  BY 1;