Search

[마케터 1기] SQL 개인 과제

개인 과제 안내
•
제출 마감: 2025. 5. 19. (월) 14:00 까지
•
과제 해설: 2025. 5. 19. (월) 14:00 ~ 15:00
과제 목표 및 진행 안내
과제 목표
1. SQL에 익숙해지기 2. Query 작동 원리를 이해하기
진행 안내
SQL(데이터 속 한 걸음)라이브세션을 학습하신 수강생들이 풀 수 있도록 만들었습니다.
•
문제들은 비전공자 기준 2시간 혹은 그 이상을 고민하도록 구성하였습니다.
◦
충분한 고민이 여러분의 실력 상승에 도움이 됩니다.
•
현업에서의 여러분 코딩 실력을 키우기 위해 ChatGPT 사용을 금지합니다.
◦
당장의 문제는 해결 할 수 있지만 실력 향상에는 독입니다.
◦
추가로 튜터님들에게 직접적으로 문제 해결을 바라는 질문도 지양해주세요.
•
과제 기간에 질문하는 방법
1.
현재 풀고 있는 문제
2.
전체 코드
3.
에러 코드의 Detail (Ex Division by Zero)
4.
에러 코드의 추가 설명
5.
해결을 위해서 시도해본 것
a.
blog, Youtube 찾아본 경험
b.
혹은 본인의 지식 or 강의 지식으로 생각하는 바
진행 방식
1.
과제 진행은 DBeaver 등등 본인이 편한 환경으로 설정하여 진행하세요.
2.
각 문제에 내용들을 확인하여 코드를 작성하여주세요.
3.
제출 링크에 각 문항에 맞게 코드를 제출해주세요.
4.
필수 문제 / 도전 문제
a.
필수 : 1-3번 문제
b.
도전 : 4-6번 문제
•
제출 쿼리는 실행 가능한 쿼리로 작성해주세요.
◦
실행 에러가 날 경우 0점 처리됩니다.
•
문제 4~6번은 난이도 상 도전 과제 입니다. 그래도.. 멋지게 도전해보세요!
과제 목표 & 진행 방식
브라질의 대표적인 이커머스 플랫폼인 Olist의 주문 데이터를 기반으로 만든 실제 상업 데이터로, 캐글(Kaggle)에서 공개된 Brazilian E-Commerce Public Dataset by Olist에서 다운로드할 수 있습니다.

Olist의 원본 데이터

 실습 데이터 다운로드

과제
문제1. 배송 지연 고객 분석
배송 지연은 고객 만족도에 큰 영향을 미칠 수 있습니다. 특히, 여러 번 주문을 한 고객들이 예상 배송일보다 실제 배송이 늦어진 경우, 이들은 브랜드에 대한 충성도가 높을 수 있으므로 특별한 주의가 필요합니다. 이번 과제는 예상 배송일보다 실제 배송을 늦게 받은 고객들 중에서, 가장 많은 주문을 한 고객을 찾아내는 것입니다. 이 고객들은 배송 서비스 개선이나 VIP 혜택을 제공하기 위한 중요한 대상이 될 수 있습니다.
문제
예상 배송일보다 실제 배송을 늦게 받은 고객들 중에서, 가장 많은 주문을 한 고객의 ID와 총 주문 수를 조회하세요.
•
결과 컬럼: customer_id, total_orders
쿼리 예시 이미지
문제2. 결제 방식별 통계 분석
온라인 쇼핑몰이나 서비스 플랫폼에서 결제 방식은 고객의 구매 경험에 중요한 영향을 미칩니다. 다양한 결제 방식이 제공됨에 따라, 각 결제 방식별로 얼마나 많은 금액이 결제되었는지를 파악하는 것은 매우 중요합니다. 이를 통해 결제 방식의 선호도와 효율성을 분석할 수 있으며, 결제 수단에 따른 사용자 행동 분석을 통해 마케팅 전략을 조정하거나 시스템 최적화를 할 수 있습니다. 이번 과제는 payments 테이블에서 각 결제 방식(payment_type)별 결제 금액의 합계와 해당 결제 방식이 전체 결제 금액에서 차지하는 비율을 계산하는 것입니다. 이를 통해 각 결제 방식의 기여도를 분석하고, 플랫폼의 결제 방식에 대한 인사이트를 도출할 수 있습니다.
문제
marketer_payments 테이블에서 각 결제 방식(payment_type)별 결제 금액의 합계와 해당 결제 방식이 전체 결제 금액에서 차지하는 비율을 계산하세요.
•
결과 컬럼: payment_type, total_payment_value, payment_percentage
쿼리 예시 이미지
문제3. 유저별 결제 및 주문 분석
고객의 주문 및 결제 데이터를 분석하는 것은 비즈니스 전략에 중요한 인사이트를 제공합니다. 특히, 배송된 주문에 대한 분석을 통해 고객들의 구매 패턴을 파악하고, 이들을 대상으로 더 나은 서비스나 마케팅 전략을 수립할 수 있습니다. 이번 과제는 배송이 완료된 주문을 기준으로 고유 고객 수, 총 주문 수, 총 결제 금액, 그리고 고객 1명당 평균 결제액을 계산하는 것입니다.
이 분석을 통해, 배송이 완료된 고객들의 행동 패턴을 파악하고, 특정 고객군에 대한 맞춤형 마케팅을 고려할 수 있는 기초 데이터를 제공할 수 있습니다.
문제
배송된(delivered) 주문을 기준으로 고유 고객 수, 총 주문 수, 총 결제 금액, 그리고 고객 1명당 평균 결제액을 계산하세요.
•
결과 컬럼: cnt_users, cnt_orders, sum_payment, arppu
쿼리 예시 이미지
문제4. 고과금 결제 비율 분석
결제 수단별 분석은 고객이 어떤 결제 방식을 선호하는지, 그리고 그 결제 방식에서 발생하는 금액 분포를 파악하는 데 유용합니다. 특히, 결제 수단 내에서 고액 결제를 분석함으로써, 고액 결제 고객을 식별하고, 이를 통해 더 나은 마케팅 전략을 수립할 수 있습니다.
이번 과제는 동일한 결제수단(payment_type)에서 이루어진 다른 결제들의 평균 금액보다 높은 결제들 중, 해당 결제가 결제 수단의 총 결제 금액 대비 20% 이상을 차지하는 주문을 찾아내는 것입니다. 이를 통해 특정 결제 수단에서 큰 비중을 차지하는 고액 결제 고객을 식별할 수 있습니다. payment_ratio는 결제 금액이 해당 결제 수단의 총 결제 금액에서 차지하는 비율을 소수점 둘째 자리까지 계산합니다.
문제
동일한 결제수단(payment_type)에서 이루어진 다른 결제의 평균 금액보다 높은 결제들 중에서, 해당 결제가 해당 결제수단의 총 결제 금액 대비 20% 이상을 차지하는 주문을 조회하세요. payment_ratio는 결제 금액이 총 결제 금액에서 차지하는 비율을 소수점 둘째 자리까지 계산해주세요.
•
결과 컬럼: order_id, payment_type, payment_value, payment_ratio
쿼리 예시 이미지
문제5. 월별 주문 추세 분석
월별 주문 건수를 분석하는 것은 사업의 성장 추이를 파악하고, 특정 월에 발생한 주문 변화를 이해하는 데 중요한 역할을 합니다. 특히, 주문이 없는 달도 0건으로 포함하여 전반적인 패턴을 정확히 이해하는 것이 필요합니다. 또한, 월별 주문 건수의 증감률을 계산함으로써, 이전 달과 비교한 성장률을 파악하고, 비즈니스 전략을 개선하는 데 필요한 인사이트를 얻을 수 있습니다.
이번 과제는 orders 테이블에서 월별(년-월) 주문 건수를 계산하고, 주문이 없는 달도 0건으로 포함하며, 지난달 대비 주문 건수 증감율(growth_rate)을 계산하는 것입니다. 결과는 년-월(month) 순서대로 정렬되며, 증감율은 소수점 둘째 자리까지 반올림하여 계산합니다.
문제
marketer_orders 테이블에서 월별(년-월) 주문 건수를 계산하되, 주문이 없는 달도 0건으로 포함하고, 지난달 대비 주문 건수 증감율(growth_rate)을 계산하세요. 결과는 년-월(month) 순서대로 정렬하며, 증감율은 소수점 둘째 자리까지 반올림 해주세요.
•
결과 컬럼: month, cnt_orders, growth_rate
쿼리 예시 이미지(결과 전체)
문제6. 결제 이상치 탐지
결제 금액의 이상치는 고객의 구매 행동을 분석하거나 결제 시스템의 오류를 파악하는 데 중요한 정보를 제공합니다. 결제 금액이 평균을 기준으로 너무 크거나 작은 경우, 이는 비정상적인 결제 패턴을 나타낼 수 있습니다. 이러한 이상치를 탐지하고, 이를 'Yes'/'No'로 표시함으로써, 정상적인 결제 흐름과 비정상적인 흐름을 구별할 수 있습니다.
이번 과제는 각 결제 방식(payment_type)별로 결제 금액의 평균 ± 3 표준편차(standard deviation)를 기준으로 이상치를 탐지하는 것입니다. 결제 금액이 이 범위를 벗어나면 해당 결제는 이상치로 간주되며, 그 여부를 'Yes' 또는 'No'로 표시합니다. 또한, 결제 금액이 큰 순으로 정렬해야 합니다.
문제
각 결제 방식(payment_type)별 결제 금액의 평균 ± 3 표준편차(standard deviation)를 기준으로 이상치를 ‘Yes’/’No’로 탐지하세요. 결제 금액이 이 범위를 벗어나면 이상치로 간주합니다. 결제 금액이 큰 순으로 정렬해주세요.
•
결과 컬럼: order_id, payment_type, payment_value, is_outlier
쿼리 예시 이미지(상위 일부)
개인 과제 제출 안내
•
제출 마감: 2025. 5. 19. (월) 14:00 까지
•
과제 해설: 2025. 5. 19. (월) 14:00 ~ 15:00
Copyright ⓒ 2024 TeamSparta All rights reserved.