1번 문제(15분)

주차별 각 카테고리별 평균 할인율이 가장 높았던 기간과 할인율을 구하는 쿼리를 작성해주세요

단, 날짜 데이터를 YYYY-MM-DD 23:59:39 이런 형태로 변경해주세요

WITH transaction_data AS (
  SELECT 111 AS user_id, 1001 AS item_id, 719200 AS actual_price, '01/08/2024 12:00:00' AS transaction_date UNION ALL
  SELECT 111, 2002, 89000, '01/10/2024 12:00:00' UNION ALL
  SELECT 189, 2002, 89000, '01/12/2024 12:00:00' UNION ALL
  SELECT 156, 3002, 459000, '01/15/2024 12:00:00' UNION ALL
  SELECT 121, 1001, 719200, '01/18/2024 12:00:00' UNION ALL
  SELECT 156, 2001, 90300, '01/25/2024 12:00:00' UNION ALL 
  SELECT 145, 3001, 399000, '01/26/2024 12:00:00' UNION ALL
  SELECT 189, 1002, 607200, '01/28/2024 12:00:00' UNION ALL
  SELECT 111, 3001, 399000, '02/05/2024 12:00:00' UNION ALL
  SELECT 178, 1002, 759000, '02/07/2024 12:00:00' UNION ALL
  SELECT 121, 2002, 62300, '02/08/2024 12:00:00' UNION ALL 
  SELECT 156, 1001, 899000, '02/10/2024 12:00:00' UNION ALL
  SELECT 190, 2001, 90300, '02/11/2024 12:00:00' UNION ALL 
  SELECT 189, 2001, 90300, '02/14/2024 12:00:00' UNION ALL 
  SELECT 111, 1002, 759000, '02/15/2024 12:00:00' UNION ALL
  SELECT 156, 3001, 299250, '02/20/2024 12:00:00' UNION ALL
  SELECT 189, 3002, 344250, '02/25/2024 12:00:00' UNION ALL
  SELECT 111, 2001, 90300, '02/28/2024 12:00:00'  
), user_info AS (
  SELECT 111 AS user_id, 'Seoul' AS city, 28 AS age, 'Female' AS gender UNION ALL
  SELECT 121, 'Busan', 35, 'Male' UNION ALL
  SELECT 145, 'Incheon', 42, 'Female' UNION ALL
  SELECT 156, 'Seoul', 31, 'Male' UNION ALL
  SELECT 178, 'Daegu', 25, 'Female' UNION ALL
  SELECT 189, 'Seoul', 39, 'Male' UNION ALL
  SELECT 190, 'Busan', 29, 'Female'
), item_info AS (
  SELECT 1001 AS item_id, 'Electronics' AS category, 'Smartphone' AS item_name, 899000 AS list_price UNION ALL
  SELECT 1002 AS item_id, 'Electronics' AS category, 'Tablet' AS item_name, 759000 AS list_price UNION ALL
  SELECT 2001 AS item_id, 'Fashion' AS category, 'Sneakers' AS item_name, 129000 AS list_price UNION ALL
  SELECT 2002 AS item_id, 'Fashion' AS category, 'Backpack' AS item_name, 89000 AS list_price UNION ALL
  SELECT 3001 AS item_id, 'Home' AS category, 'Coffee Machine' AS item_name, 399000 AS list_price UNION ALL
  SELECT 3002 AS item_id, 'Home' AS category, 'Air Purifier' AS item_name, 459000 AS list_price
),
base AS (
  SELECT
    user_id,
    item_id,
    actual_price,
    --FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', PARSE_DATETIME('%m/%d/%Y %H:%M:%S', transaction_date)) AS transaction_datetime
    PARSE_DATETIME('%m/%d/%Y %H:%M:%S', transaction_date) AS transaction_datetime
  FROM transaction_data
),
discount_tb AS (
  SELECT
    base.user_id,
    base.item_id,
    100 - (base.actual_price / item_info.list_price) * 100 AS discount_ratio,
    base.transaction_datetime,
    item_info.category,
    date_trunc(base.transaction_datetime, WEEK) AS transaction_week
  FROM
    base
  LEFT JOIN
    item_info
  ON
    base.item_id = item_info.item_id
),
weekly_ratio AS (
  SELECT
    FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', transaction_week) AS transaction_week,
    category,
    AVG(discount_ratio) as avg_discount_ration
  FROM
    discount_tb
  GROUP BY
    transaction_week, category
),
rank_tb AS (
  SELECT
   transaction_week,
   category,
   discount_ratio,
   rank() OVER(ORDER BY discount_ratio DESC) AS rk
  FROM
    discount_tb
)
SELECT
  transaction_week,
  category,
  discount_ratio
FROM
  rank_tb
WHERE
  rk = 1

2번 문제(10분)

2024년 1월에 가장 많은 매출을 기록한 카테고리를 구하는 쿼리를 작성해주세요

WITH transaction_data AS (
  SELECT 111 AS user_id, 1001 AS item_id, 719200 AS actual_price, '01/08/2024 12:00:00' AS transaction_date UNION ALL
  SELECT 111, 2002, 89000, '01/10/2024 12:00:00' UNION ALL
  SELECT 189, 2002, 89000, '01/12/2024 12:00:00' UNION ALL
  SELECT 156, 3002, 459000, '01/15/2024 12:00:00' UNION ALL
  SELECT 121, 1001, 719200, '01/18/2024 12:00:00' UNION ALL
  SELECT 156, 2001, 90300, '01/25/2024 12:00:00' UNION ALL 
  SELECT 145, 3001, 399000, '01/26/2024 12:00:00' UNION ALL
  SELECT 189, 1002, 607200, '01/28/2024 12:00:00' UNION ALL
  SELECT 111, 3001, 399000, '02/05/2024 12:00:00' UNION ALL
  SELECT 178, 1002, 759000, '02/07/2024 12:00:00' UNION ALL
  SELECT 121, 2002, 62300, '02/08/2024 12:00:00' UNION ALL 
  SELECT 156, 1001, 899000, '02/10/2024 12:00:00' UNION ALL
  SELECT 190, 2001, 90300, '02/11/2024 12:00:00' UNION ALL 
  SELECT 189, 2001, 90300, '02/14/2024 12:00:00' UNION ALL 
  SELECT 111, 1002, 759000, '02/15/2024 12:00:00' UNION ALL
  SELECT 156, 3001, 299250, '02/20/2024 12:00:00' UNION ALL
  SELECT 189, 3002, 344250, '02/25/2024 12:00:00' UNION ALL
  SELECT 111, 2001, 90300, '02/28/2024 12:00:00'  
), user_info AS (
  SELECT 111 AS user_id, 'Seoul' AS city, 28 AS age, 'Female' AS gender UNION ALL
  SELECT 121, 'Busan', 35, 'Male' UNION ALL
  SELECT 145, 'Incheon', 42, 'Female' UNION ALL
  SELECT 156, 'Seoul', 31, 'Male' UNION ALL
  SELECT 178, 'Daegu', 25, 'Female' UNION ALL
  SELECT 189, 'Seoul', 39, 'Male' UNION ALL
  SELECT 190, 'Busan', 29, 'Female'
), item_info AS (
  SELECT 1001 AS item_id, 'Electronics' AS category, 'Smartphone' AS item_name, 899000 AS list_price UNION ALL
  SELECT 1002 AS item_id, 'Electronics' AS category, 'Tablet' AS item_name, 759000 AS list_price UNION ALL
  SELECT 2001 AS item_id, 'Fashion' AS category, 'Sneakers' AS item_name, 129000 AS list_price UNION ALL
  SELECT 2002 AS item_id, 'Fashion' AS category, 'Backpack' AS item_name, 89000 AS list_price UNION ALL
  SELECT 3001 AS item_id, 'Home' AS category, 'Coffee Machine' AS item_name, 399000 AS list_price UNION ALL
  SELECT 3002 AS item_id, 'Home' AS category, 'Air Purifier' AS item_name, 459000 AS list_price
),
base AS (
  SELECT
    transaction_data.actual_price,
    item_info.category,
  FROM
    transaction_data
  LEFT JOIN
    item_info
  ON
    transaction_data.item_id = item_info.item_id
  WHERE
    transaction_date >= '01/01/2024' AND
    transaction_date < '02/01/2024'
)
SELECT
  category,
  total_price
FROM
  (
  SELECT 
    category,
    SUM(actual_price) as total_price,
    rank() OVER(ORDER BY SUM(actual_price) desc) AS rk
  FROM
    base
  GROUP BY
    category
  )
WHERE
  rk = 1

3번 문제(10분)

유저별 총 구매 금액이 200만원 이상인 유저들이 가장 많이 구매한 카테고리를 찾는 쿼리를 작성해주세요

WITH transaction_data AS (
  SELECT 111 AS user_id, 1001 AS item_id, 719200 AS actual_price, '01/08/2024 12:00:00' AS transaction_date UNION ALL
  SELECT 111, 2002, 89000, '01/10/2024 12:00:00' UNION ALL
  SELECT 189, 2002, 89000, '01/12/2024 12:00:00' UNION ALL
  SELECT 156, 3002, 459000, '01/15/2024 12:00:00' UNION ALL
  SELECT 121, 1001, 719200, '01/18/2024 12:00:00' UNION ALL
  SELECT 156, 2001, 90300, '01/25/2024 12:00:00' UNION ALL 
  SELECT 145, 3001, 399000, '01/26/2024 12:00:00' UNION ALL
  SELECT 189, 1002, 607200, '01/28/2024 12:00:00' UNION ALL
  SELECT 111, 3001, 399000, '02/05/2024 12:00:00' UNION ALL
  SELECT 178, 1002, 759000, '02/07/2024 12:00:00' UNION ALL
  SELECT 121, 2002, 62300, '02/08/2024 12:00:00' UNION ALL 
  SELECT 156, 1001, 899000, '02/10/2024 12:00:00' UNION ALL
  SELECT 190, 2001, 90300, '02/11/2024 12:00:00' UNION ALL 
  SELECT 189, 2001, 90300, '02/14/2024 12:00:00' UNION ALL 
  SELECT 111, 1002, 759000, '02/15/2024 12:00:00' UNION ALL
  SELECT 156, 3001, 299250, '02/20/2024 12:00:00' UNION ALL
  SELECT 189, 3002, 344250, '02/25/2024 12:00:00' UNION ALL
  SELECT 111, 2001, 90300, '02/28/2024 12:00:00'  
), user_info AS (
  SELECT 111 AS user_id, 'Seoul' AS city, 28 AS age, 'Female' AS gender UNION ALL
  SELECT 121, 'Busan', 35, 'Male' UNION ALL
  SELECT 145, 'Incheon', 42, 'Female' UNION ALL
  SELECT 156, 'Seoul', 31, 'Male' UNION ALL
  SELECT 178, 'Daegu', 25, 'Female' UNION ALL
  SELECT 189, 'Seoul', 39, 'Male' UNION ALL
  SELECT 190, 'Busan', 29, 'Female'
), item_info AS (
  SELECT 1001 AS item_id, 'Electronics' AS category, 'Smartphone' AS item_name, 899000 AS list_price UNION ALL
  SELECT 1002 AS item_id, 'Electronics' AS category, 'Tablet' AS item_name, 759000 AS list_price UNION ALL
  SELECT 2001 AS item_id, 'Fashion' AS category, 'Sneakers' AS item_name, 129000 AS list_price UNION ALL
  SELECT 2002 AS item_id, 'Fashion' AS category, 'Backpack' AS item_name, 89000 AS list_price UNION ALL
  SELECT 3001 AS item_id, 'Home' AS category, 'Coffee Machine' AS item_name, 399000 AS list_price UNION ALL
  SELECT 3002 AS item_id, 'Home' AS category, 'Air Purifier' AS item_name, 459000 AS list_price
),
base AS (
  SELECT
      user_id,
      SUM(actual_price) AS total_price
  FROM
      transaction_data
  GROUP BY
      1
  having
      total_price >= 2000000
),
rank_tb AS (
  SELECT
    item_info.category AS category,
    SUM(transaction_data.actual_price) AS total_actual_price,
    RANK() OVER(ORDER BY SUM(transaction_data.actual_price) DESC) as rk
  FROM
    transaction_data
  LEFT JOIN
    item_info
  ON
    transaction_data.item_id = item_info.item_id
  WHERE
    user_id in (
                  SELECT
                    user_id
                  FROM
                    base
                )
  GROUP BY 1
)
SELECT
  category,
  total_actual_price
FROM
  rank_tb
WHERE
  rk = 1

1. Weekly Retention을 구하는 쿼리를 바닥부터 스스로 작성해요

WITH base AS (
  SELECT
    DISTINCT
      DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week,
      user_pseudo_id
  FROM
    advanced.app_logs
  WHERE
      event_date BETWEEN "2022-08-01" AND "2022-11-03"
),
first_event AS (
  SELECT
    event_week,
    first_value(event_week) OVER(PARTITION BY user_pseudo_id ORDER BY event_week ASC) AS first_event_week,
    user_pseudo_id
  FROM
    base
),
first_week_diff AS (
  SELECT
    date_diff(event_week, first_event_week, WEEK) AS week_diff,
    user_pseudo_id
  FROM
    first_event
)
SELECT
  week_diff,
  COUNT(DISTINCT user_pseudo_id) AS cnt
FROM
  first_week_diff
GROUP BY
  week_diff
ORDER BY
  week_diff ASC

2. Retain user를 New + Current + Resurrected + Dormant user로 나누는 쿼리를 작성해보세요.

유저 구분 행동 : 접속기록

<aside> 💡

New User : 최초 접속한 유저
Active User : 해당 주차 기준 이전 3주안에 접속한 기록이 있는 유저

(24년 40주차 기준 : 24년 37주차 ~ 39주차 안에 접속 기록이 한 번이라도 있는 유저)
Resurrected User: 해당 주차에 접속한 기록은 있으나 해당 주차 기준 이전 3주안에 접속한 기록이 없는 유저 (신규 유저 제외)
Dormant User : 과거 접속한 기록은 있으나 해당 주차 기준 이전 3주안에 접속 기록이 없는 유저 (24년 40주차 기준 : 24년 37주차 ~ 40주차 안에 접속 기록이 없지만 그 이전에는 접속 기록이 있는 유저) </aside>
유저 구분 행동 선정 기준

핵심 행동을 click_payment로 선정하려 했으나 아직 구매자가 적어 수익화가 주 목적으로 선정하지 않음. <구매자들의 Weekly Retention>

(대부분 10명 이하의 고객만이 재 구매를 하는 모습)

재구매까지의 유저 수가 적다는 것은 아직 제품이 PMF을 명확히 찾지 못했기에 고객 획득을 아무리 많이 하더라도 이탈하는 고객이 많을 것이라고 판단

<유저들의 Weekly Retention>

실제로도 위의 weekly 리텐션을 보면 최근 리텐션이 떨어지는 주기가 짧아지고 있음. (주차별 평균 리텐션 비교 기준) (2022년 10월 9일 주차는 2주만에 10.66 → 6.46, 2022년 10월 23일 주차는 1주만에 10.03 → 5.59)

또한, 2022년 10월의 경우 1주차에 리텐션이 10%를 넘기며 그 전 주차들 대비 리텐션이 높았음에도 리텐션이 떨어지는 주기가 짧아지는 모습.
유저 구분 근거

<유저들의 Weekly Retention>

보통 유저들이 활성화되고 3~4주 동안 경험하고 리텐션이 떨어지는 모습 (빨간색 부분) (기준 : 대략 평균치 이상이 시작되는 지점)- 평균의 특성상 큰 값이 나오게 되면 평균에 영향을 미칠것으로 보아 활성화 되는 주차로 판단)

그래서 활성화, 이탈 유저의 기준은 최근 3주로 계산

(최근 리텐션이 줄어드는 기간이 빨라지고 있어 4주 보다는 보수적으로 계산)

또한, 배달앱의 경우 접속 주기가 짧은 편에 속하지만 기준은 3주내 1회로 설정.

하단 사진은 첫 방문에서 다음 방문, 두 번째 방문에서 세 번째 방문, 세 번째 방문에서 네 번째 방문간의 날짜 차이의 통계값이다.
- code
전체적으로 접속 주기가 긴 편(max, avg, median 값 참조)에 속하고 유저별 편차(std : 표준편차)도 큰 편이기에 접속 기준을 3주내 1회로 보수적으로 설정
결과 코드
유저 구분별 유저 수

New User, dormant User가 우상향의 그래프를 보이다가

New User는 2022년 10월 9일 주차부터 감소세로 전환되고 2022년 10월 23일 주차에는 dormant User가 역전하는 모습을 보임.

이 기간에 Resurrected, Active User도 감소로 전환하는 모습을 보임.

<All Active vs Dormant User>

All User(New + Active + Resurrencted)와 Dormant User의 유저수를 비교할때 아직 서로 교차하지는 않았지만 사이가 점점 가까워지고 있음.

Dormant User(이탈 유저)가 증가하는 모습으로 보아 앱의 핵심 가치를 느끼지 못하고 있는 것으로 추측.

또한, New User(신규 유저)도 같이 감소세로 전환되는 것으로 보아 앱에 대한 평판 감소, New User 풀 감소 등의 현상이 있을 것으로 의심.

3. 주어진 데이터에서 어떤 사람들이 리텐션이 그나마 높을까요?