https://teamsparta.notion.site/1-SQL-1f02dc3ef51480918faaf992f940cdd9
문제1. 배송 지연 고객 분석
배송 지연은 고객 만족도에 큰 영향을 미칠 수 있습니다. 특히, 여러 번 주문을 한 고객들이 예상 배송일보다 실제 배송이 늦어진 경우, 이들은 브랜드에 대한 충성도가 높을 수 있으므로 특별한 주의가 필요합니다.
문제
예상 배송일보다 실제 배송을 늦게 받은 고객들 중에서, 가장 많은 주문을 한 고객의 ID와 총 주문 수를 조회하세요.
- 결과 컬럼: customer_id, total_orders
구해야 할 것 !
1. 실제 배송일 > 예상 배송일인 고객, (WHERE절 조건 부여)
2. 해당 조건을 충족하는 고객별 주문 수 (COUNT 함수 이용 및 GROUP BY)
3. 가장 많은 주문을 한 고객의 ID 및 총 주문수 (ORDER BY절 이용,내림차순 desc, 1명만 추출)
풀이
SELECT customer_id,
count(1) AS total_orders --------------고객의 ID와 총 주문 수
FROM orders
WHERE order_delivered_customer_date > order_estimated_delivery_date ---- 실제 배송일이 늦은 고객만 추출
GROUP BY customer_id ---------------------고객별로 묶음 해주기
ORDER BY total_orders desc limit 1 ------주문수 내림차순으로 1명만 뽑기
결과
문제2. 결제 방식별 통계 분석
온라인 쇼핑몰이나 서비스 플랫폼에서 결제 방식은 고객의 구매 경험에 중요한 영향을 미칩니다. 다양한 결제 방식이 제공됨에 따라, 각 결제 방식별로 얼마나 많은 금액이 결제되었는지를 파악하는 것은 매우 중요합니다. 이를 통해 결제 방식의 선호도와 효율성을 분석할 수 있으며, 결제 수단에 따른 사용자 행동 분석을 통해 마케팅 전략을 조정하거나 시스템 최적화를 할 수 있습니다.
문제
payments 테이블에서 각 결제 방식(payment_type)별 결제 금액의 합계와 해당 결제 방식이 전체 결제 금액에서 차지하는 비율을 계산하세요.
- 결과 컬럼: payment_type, total_payment_value, payment_percentage
구해야 할 것 !
1. 결제 방식 별 결제 금액의 합 (GROUP BY)
2. 비율 구하기???
1차 풀이
SELECT payment_type,
SUM(payment_value) as '합계 금액' ---- 결제 타입과 합계 금액 구하기
FROM payments
GROUP BY payment_type ----- 결제 타입 별로 묶음 해주기
1차 결과
❗ 비율 구하는 공식 = A 합계/전체 합계
1차로 SUM(payment_value)를 구한 후 합계 금액을 나누기?
2차 풀이
SELECT payment_type,
SUM(payment_value) as '합계 금액',
SUM(payment_value)/145589.82 '비율' ---- + 전체 금액 구한 후 나누기
FROM payments
GROUP BY payment_type
2차 결과
❓ 2차 풀이의 경우 효율성 ▼, 더 효율적인 방법이 있을지? + 비율이 퍼센트가 아닌 소수점으로 나타나는 점.
전체 중 비율 구하는 함수식 찾아보기.
문제3. 유저별 결제 및 주문 분석
고객의 주문 및 결제 데이터를 분석하는 것은 비즈니스 전략에 중요한 인사이트를 제공합니다. 특히, 배송된 주문에 대한 분석을 통해 고객들의 구매 패턴을 파악하고, 이들을 대상으로 더 나은 서비스나 마케팅 전략을 수립할 수 있습니다. 이번 과제는 배송이 완료된 주문을 기준으로 고유 고객 수, 총 주문 수, 총 결제 금액, 그리고 고객 1명당 평균 결제액을 계산하는 것입니다.
문제
배송된(delivered) 주문을 기준으로 고유 고객 수, 총 주문 수, 총 결제 금액, 그리고 고객 1명당 평균 결제액을 계산하세요.
- 결과 컬럼: cnt_users, cnt_orders, sum_payment, arppu
구해야 할 것 !
1. 배송된 주문만 보기 (WHERE절 조건 삽입)
2. 고유 고객수. (중복 X. DISTINCT)
3. 총 주문 수 (중복 포함)
4. 총 결제 금액 (합계 구하기)
5. 1명당 평균 결제액 (총 결제 금액/고객수)
풀이
SELECT COUNT(distinct customer_id) AS 'cnt_users', ---중복 제외한 고객 아이디
COUNT(*) AS 'cnt_orders', ---- 총 주문수 확인
SUM(payment_value) AS 'sum_payment', ---- 총 주문 금액
SUM(payment_value)/COUNT(distinct customer_id) 'arppu' ---총주문금액/고객 수
FROM orders o INNER JOIN payments p ON o.order_id=p.order_id ----주문과 결제 내역 조인, 데이터가 둘다 존재해야하기에 INNER JOIN 사용
WHERE order_status='delivered' ----- 주문 상태가 배달 완료만 보기
결과
✔ 문제 풀이할 때 주어진 조건에 맞게 단계식으로 구성하기 !
✔ 꼭 정해진 공식이 아니더라도 가진 지식으로 결과 도출하기 !
