240816_TIL

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

SQL 코드카타

문제 링크

1. total_transaction CTE를 만들어서 customer_id별로 총 거래횟수를 구한다.

2. calculate CTE를 만들어서 customer_id별로 며칠이나 연속해서 구매했는지 계산하기 위한 cnt 컬럼을 구한다. date_sub안에 window 함수를 집어넣는 건 며칠 전 코드카타에서도 했던 방법이고, 만약에 이게 생각이 안 날 경우에는 CTE를 한 번 더 만들어도 무방할 듯. 이 calculate CTE의 용도는 오직 계산용 cnt를 뽑아내는 것이다.

3. 이제 calculate 테이블로부터 연속해서 구매한 일수(consecutive)를 구하는 result CTE를 만든다. 예시에서 customer 102번의 경우 1, 2 이렇게 나올 것이다. (총 구매횟수는 3번이지만, 중간에 연속구매가 끊겼으므로)

4. 마지막으로 제일 처음 구한 total_transaction과 마지막으로 만든 result를 join하고, 이 때 consecutive가 가장 큰 경우를 갖고와야 하므로 where절에 들어갈 값은 result 테이블에서 서브쿼리로 뽑아온다. (이 작업을 안 하면 최대 연속구매횟수가 1인 경우를 갖고 올 수 없음)

5. 본 쿼리에서는 customer_id만 불러온 뒤 조건에 맞게 order by를 적용하면 끝.

WITH total_transaction
AS
  (
           SELECT   customer_id,
                    count(transaction_id) AS "total"
           FROM     transactions
           GROUP BY 1),
  calculate
AS
  (
           SELECT   customer_id,
                    date_sub(transaction_date, INTERVAL rank() over(partition BY customer_id ORDER BY transaction_date) day) AS "cnt",
                    transaction_date
           FROM     transactions),
  result
AS
  (
           SELECT   customer_id,
                    count(transaction_date) AS "consecutive"
           FROM     calculate
           GROUP BY customer_id,
                    cnt)
  SELECT    t.customer_id
  FROM      total_transaction t
  LEFT JOIN result r
  ON        t.customer_id = r.customer_id
  WHERE     consecutive =
            (
                   SELECT max(consecutive)
                   FROM   result)
  ORDER BY  1;