SQL | SOLVESQL 코딩테스트 : Lv5. (WINDOWFUNCTION)

Lv5. 카테고리 별 매출 비율

SELECT DISTINCT category
  , sub_category
  , ROUND(SUM(sales) OVER(PARTITION BY sub_category),2) AS sales_sub_category
  , ROUND(SUM(sales) OVER(PARTITION BY category),2) AS sales_category
  , ROUND(SUM(sales) OVER() ,2) AS sales_total
  , ROUND(ROUND(SUM(sales) OVER(PARTITION BY sub_category),2) / ROUND(SUM(sales) OVER(PARTITION BY category),2)*100,2) AS pct_in_category
  , ROUND(ROUND(SUM(sales) OVER(PARTITION BY sub_category),2) / ROUND(SUM(sales) OVER() ,2)*100,2) AS pct_in_total
FROM records

❗윈도우 함수 @@() OVER(PARTITION BY ##) 사용해서 칼럼 생성

 

Lv5. 세션 재정의하기

WITH step1 AS (
  SELECT user_pseudo_id
       , event_timestamp_kst
       , LAG(event_timestamp_kst, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp_kst) AS last_event_timestamp
       , LEAD(event_timestamp_kst, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp_kst) AS next_event_timestamp
       , ROW_NUMBER() OVER () AS id
   FROM ga
  WHERE user_pseudo_id = 'S3WDQCqLpK'
  ORDER BY user_pseudo_id, event_timestamp_kst
), step2 AS (
  SELECT user_pseudo_id
       , event_timestamp_kst
       , TIMESTAMPDIFF(SECOND, last_event_timestamp, event_timestamp_kst) AS last_event
       , TIMESTAMPDIFF(SECOND, event_timestamp_kst, next_event_timestamp) AS next_event
       , id
  FROM step1
), step3 AS (
  SELECT step2.*
       , CASE WHEN last_event >= 3600 THEN id
              WHEN last_event IS NULL THEN id
              ELSE LAG(id, 1) OVER (PARTITION BY user_pseudo_id ORDER BY id) END AS session
  FROM step2
  WHERE last_event >= 3600
  OR next_event >= 3600
  OR last_event IS NULL
  OR next_event IS NULL
), step4 AS (
  SELECT user_pseudo_id
       , session
       , MIN(event_timestamp_kst) AS session_start
       , MAX(event_timestamp_kst) AS session_end
  FROM step3
  GROUP BY user_pseudo_id, session
)

SELECT user_pseudo_id, session_start, session_end
FROM step4
ORDER BY session_start

• 예전에 풀었는데 다시 풀려니까 다시 못풀었다... 
• 그김에 그때 풀었던 코드 리뷰를 하려 한다. 

 

 

+) with 절

step1

❗유저 아이디를 파티션으로 이벤트 시간을 앞 뒤로 하나씩 당긴다
❗ 이전 행 1, 원래 행, 다음 행 1
❗ id 지정해서 구분할 수 있게 한다

step2

❗이전에 만든 3개의 시간의 차이를 구한다

❗원래 행 - 이전 행, 다음 행 - 원래 행

 

step3

❗case when 사용해서 30분 이상인 아이디는 남기고 30 미만일 때는 다음 아이디를 하나 가져온다

❗where 절에 이벤트 시간 기준 걸어서 앞 뒤가 비거나, 시간이 30분 이상인 것만 case when 절에 넣을 수 있도록 한다

 

step4

❗최소 시간과 최대 시간 정의한다

 

 

---

나중에 다시 풀어봐야겠다...ㅠ