240813_TIL

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

SQL 코드카타

문제 링크
연속된 날짜를 다루는 게
아직도 이렇게 한 번에 기억이 안나서야..

우선 transactions 테이블 2개를 조인하고,
조인의 조건으로 customer_id 일치,
거래일 차이가 하루 날 것,
그리고 amount가 클 것 이렇게 3개를 걸어준다.

중간에 들어간

date_sub(t1.transaction_date, INTERVAL rank() over(partition BY t1.customer_id ORDER BY t1.transaction_date) day) AS "cnt",

이 부분이 이 문제의 킥인데,
cnt는 그루핑을 할 조건을 만들어주기 위해
순전히 연산 목적으로 생성한 컬럼이다.
date_sub의 인자에 저렇게 window 함수를 쓸 수도 있다.

저렇게 cnt를 계산해줘야 td1에서 같은 일수를 빼 줌으로써
동일한 cnt로 묶일 수 있다.

그루핑할 조건까지 생성됐으면 그 이후는 간단하다.
아래는 정답 코드.

WITH result
AS
  (
           SELECT   t1.transaction_id,
                    t1.customer_id,
                    date_sub(t1.transaction_date, INTERVAL rank() over(partition BY t1.customer_id ORDER BY t1.transaction_date) day) AS "cnt",
                    t1.transaction_date                                                                                               AS td1,
                    t2.transaction_date                                                                                               AS td2
           FROM     transactions t1
           JOIN     transactions t2
           ON       t1.customer_id = t2.customer_id
           AND      t1.transaction_date = date_sub(t2.transaction_date, INTERVAL 1 day)
           AND      t1.amount < t2.amount)
  SELECT   customer_id,
           min(td1) AS "consecutive_start",
           max(td2) AS "consecutive_end"
  FROM     result
  GROUP BY 1,
           cnt
  HAVING   count(*) >= 2
  ORDER BY 1;