[인프런 빅쿼리 빠짝스터디 3주차] 리텐션 분석

1) 리텐션 연습 문제

https://torch-cart-08d.notion.site/3-139701964d8c805d9e49f3c68ab9b0b5

2) 3주차 추가 문제

1번 문제(15분)

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

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

SELECT
  *,
  RANK() OVER(PARTITION BY category ORDER BY rate DESC) AS category_rank
FROM (
SELECT
    category,
    FORMAT_DATETIME('%Y-%m-%d 23:59:39', PARSE_DATETIME('%m/%d/%Y %H:%M:%S', transaction_date)) AS transaction_date,
    actual_price,
    list_price,
    ((list_price - actual_price) / list_price) * 100 AS rate
  FROM transaction_data AS td
  LEFT JOIN item_info ii
  ON td.item_id = ii.item_id
)
QUALIFY category_rank = 1
ORDER BY category, transaction_date

2번 문제(10분)

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

SELECT
  category,
  SUM(actual_price) AS total_price
FROM (
  SELECT
    category,
    FORMAT_DATETIME('%Y-%m-%d 23:59:39', PARSE_DATETIME('%m/%d/%Y %H:%M:%S', transaction_date)) AS transaction_date,
    actual_price,
  FROM transaction_data AS td
  LEFT JOIN item_info ii
  ON td.item_id = ii.item_id
  WHERE EXTRACT(MONTH FROM PARSE_DATETIME('%m/%d/%Y %H:%M:%S', transaction_date)) = 1
)
GROUP BY ALL
ORDER BY total_price DESC
LIMIT 1

3번 문제(10분)

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

over_200 AS (
  SELECT
    td.user_id,
    SUM(actual_price) AS user_price
  FROM transaction_data AS td
  LEFT JOIN user_info AS ui
  ON td.user_id = ui.user_id
  GROUP BY ALL
  HAVING user_price >= 2000000
)

SELECT
  category,
  COUNT(*) AS cnt
FROM over_200 AS O2
CROSS JOIN transaction_data AS td
LEFT JOIN item_info AS ii
ON td.item_id = ii.item_id
GROUP BY ALL
ORDER BY cnt DESC
LIMIT 1

• 약 20분/5분/8분 소요

• 1번 문제에서 날짜 데이터를 변환하는 방법이 떠오르지 않아 시간이 많이 소요됐습니다.