[빠짝스터디 3주차 과제] 리텐션 연습문제 및 추가 코딩테스트 문제풀이
0
1) 3주차 추가 코딩테스트 대비 문제풀이
1번문제 (제한시간: 15분 >> 소요시간 23분)
문제: 주차별 각 카테고리별 평균 할인율이 가장 높았던 기간과 할인율을 구하는 쿼리를 작성해주세요
단, 날짜 데이터를 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'
), 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
)
SELECT
*,
RANK() OVER (ORDER BY avg_sales_ratio DESC) as rnk
FROM (
SELECT
weekly,
category,
AVG(sales_ratio) AS avg_sales_ratio
FROM (
SELECT
DATE_TRUNC(DATETIME(CONCAT(SPLIT(SPLIT(a.transaction_date, '/')[OFFSET(2)], ' ')[OFFSET(0)],'-',SPLIT(a.transaction_date, '/')[OFFSET(0)] , '-',SPLIT(a.transaction_date, '/')[OFFSET(1)])), WEEK(MONDAY)) AS weekly,
a.item_id,
b.category,
b.list_price,
a.actual_price,
ROUND(1- ROUND(SAFE_DIVIDE(actual_price, list_price),4),4) AS sales_ratio
FROM transaction_data a
LEFT JOIN item_info b ON a.item_id = b.item_id
)
GROUP BY ALL
)
QUALIFY rnk = 1
ORDER BY 1
;2번문제 (제한시간: 10분 >> 소요시간 7분)
문제: 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'
), 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
)
SELECT
category
FROM (
SELECT
transaction_month,
category,
SUM(actual_price) AS category_sum_price
FROM (
SELECT
DATE_TRUNC(DATETIME(CONCAT(SPLIT(SPLIT(a.transaction_date, '/')[OFFSET(2)], ' ')[OFFSET(0)],'-',SPLIT(a.transaction_date, '/')[OFFSET(0)] , '-',SPLIT(a.transaction_date, '/')[OFFSET(1)])), MONTH) AS transaction_month,
a.item_id,
b.category,
a.actual_price
FROM transaction_data a
LEFT JOIN item_info b ON a.item_id = b.item_id
)
WHERE transaction_month = "2024-01-01"
GROUP BY ALL
)
ORDER BY category_sum_price DESC
LIMIT 1
;3번문제 (제한시간: 10분 >> 소요시간 6분 40초)
문제: 유저별 총 구매 금액이 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'
), 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
)
SELECT
category
FROM (
SELECT
category,
SUM(actual_price) AS sum_purchase_price
FROM (
SELECT
a.user_id,
a.item_id,
b.category,
a.actual_price
FROM transaction_data a
LEFT JOIN item_info b ON a.item_id = b.item_id
WHERE a.user_id IN (
SELECT
user_id
FROM (
SELECT
user_id,
SUM(actual_price) AS sum_purchase_price
FROM transaction_data a
GROUP BY ALL
HAVING SUM(actual_price) >= 2000000
)
)
)
GROUP BY ALL
)
ORDER BY sum_purchase_price DESC
LIMIT 12) 리텐션 연습문제 풀이
해당 연습문제는 전부 해결하지 못해 최대한 기한 내에 풀이를 완료한 쿼리를 작성하게 되었습니다. 완료하지 못한 과제는 추후에라도 풀이 완료하여 수정 업로드 하겠습니다.
문제 1) Weekly Retention을 구하는 쿼리를 바닥부터 스스로 작성해보세요.
WITH base AS ( # event데이터의 raw데이터를 추출
SELECT
DISTINCT
user_id,
user_pseudo_id,
DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date,
event_name
FROM advanced.app_logs
WHERE event_date BETWEEN "2022-08-01" AND "2022-12-31"
), first_week_diff_event_week AS ( # 유저별 첫번쨰 week, 이벤트 발생 week, 주차별 차이 데이터 추출
SELECT
DISTINCT
user_pseudo_id,
first_week,
event_week,
DATE_DIFF(event_week, first_week, WEEK) AS weeks_after_first_week
FROM (
SELECT
user_pseudo_id,
event_name,
DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week,
DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date), WEEK(MONDAY)) AS first_week
FROM base
)
), user_counts AS ( # 첫번째 이벤트 발생주차 | 이벤트 Retain 발생차이 | 유저 수 | 코호트 유저 수
SELECT
*,
FIRST_VALUE(user_cnt) OVER (ORDER BY weeks_after_first_week) AS cohort_user
FROM (
SELECT
weeks_after_first_week,
COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM first_week_diff_event_week
GROUP BY ALL
)
) # Retention Rate 추출
SELECT
*,
ROUND(SAFE_DIVIDE(user_cnt, cohort_user), 4) AS retention_rate
FROM user_counts
ORDER BY 1,2
;답변 1
0
안녕하세요, 인프런 AI 인턴입니다. DA유치원님의 질문에 대해 도움을 드리기 위해 기존에 제공된 답변들을 확인해 보았습니다. SQL 연습 문제와 관련하여 리텐션 분석을 하시는 것 같아, 유사한 문제에 대한 해설과 접근방법이 담긴 기존 답변을 제공합니다.
리텐션 분석 관련 기존 질문 및 답변
리텐션 분석 코호트 및 SQL 쿼리 작성 방법에서는 리텐션 쿼리 작성 시 주의할 점과 효율적인 코딩 구조에 대한 조언을 제공합니다. 특히, 주차별 사용자를 분석하고 코호트 유저를 어떻게 나눌지에 대한 SQL 작성 방법이 안내되어 있습니다.
추가적인 코딩테스트 문제 관련 도움말
문제 해설 및 해결을 위해 SQL 쿼리를 통해 리텐션, 카테고리 분석 등 다양한 조건을 간단하고 효율적으로 처리하는 방법에 대해 질문을 주셨습니다. 각각의 문제에 대해 세부적인 조언을 드리자면:
주차별 평균 할인율 계산:
- 할인율을 단일 쿼리 내에서 계산하고 각 카테고리별로 주차를 기준으로 그룹핑하여 평균을 구합니다. DISTINCT 사용 시엔 데이터 중복을 고려해야 합니다.
- 할인율을 단일 쿼리 내에서 계산하고 각 카테고리별로 주차를 기준으로 그룹핑하여 평균을 구합니다. DISTINCT 사용 시엔 데이터 중복을 고려해야 합니다.
가장 많은 매출 기록 카테고리:
- 매출 총액을 기준으로 정렬하고, 가장 큰 값을 ORDER BY와 LIMIT 조합을 통해 추출합니다.
- 매출 총액을 기준으로 정렬하고, 가장 큰 값을 ORDER BY와 LIMIT 조합을 통해 추출합니다.
총 구매 금액이 200만원 이상인 유저 분석:
- 유저 별로 구매 금액을 SUM 함수를 사용하여 계산하고, 조건문을 통해 필터링하여 가장 많이 구매한 카테고리를 추출합니다.
- 유저 별로 구매 금액을 SUM 함수를 사용하여 계산하고, 조건문을 통해 필터링하여 가장 많이 구매한 카테고리를 추출합니다.
이 모든 문제의 핵심은 SQL 쿼리를 통해 데이터를 효율적으로 집계하고, 필요한 정보를 정확하게 추출하는 것입니다.
위 링크된 자료를 참고하시면, DA유치원님께서 고민하신 부분들을 좀 더 명확하게 이해하고 풀어나가시는 데 도움이 될 것 같습니다. 추가적인 궁금증이 있으시면 언제든지 질문해 주시기 바라며, 해답을 찾은 이후 피드백도 주시면 감사하겠습니다.
저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다.
현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.
최종 과제 제출
0
82
3
BigQuery 활용편 18강 질문있습니다!
0
84
1
리텐션 공부하다가 궁금한게 생겨 질문드립니다
0
83
2
안녕하세요 강사님 코호트 쿼리 공부하다가 의문점이 생겨서 문의드립니다
0
75
2
biquery 테이블 생성 오류 이슈
0
55
2
동일하게 쿼리를 작성했는데 화면과 다른 값이 나옵니다
0
73
2
[과제] 퍼널 PIVOT 테이블 작성하기
0
67
2
array 등
0
63
2
N day 리텐션 쿼리 관련 질문
0
63
2
이동평균 계산 시 order by 기본값은 뭔가요?
0
69
2
윈도우 연습문제 1번 질문
0
65
1
user_id에 NULL이 나오는데 정상인가요?
0
72
2
3-13 리텐션 과제 제출
0
94
2
최종 과제 제출
0
130
3
weekly retention 구하기 과제
0
90
2
1-9. 피벗 쿼리 작성
0
81
2
app_logs 테이블 생성 문제
0
73
2
Weekly Retention 구하기 완성하였습니다.
0
77
2
[과제] 퍼널 쿼리(피벗테이블 적용) 작성 완료
0
106
2
3-7 Weekly, Monthly Retention 쿼리 작성
0
92
2
정성 데이터 분석 방법 문의
0
165
1
최종 과제 제출
0
108
3
1-6 예시 문제 풀이
0
69
2
최종과제 제출
0
145
2