2025.03.18 본_캠프 20일차

97번 문제 풀이

문제 요구사항

각 고객의 첫 번째 주문(최초 주문) 중에서 즉시 주문(주문 날짜와 고객 선호 배송 날짜가 같은 경우)의 비율을 계산해야 한다. 결과는 소수점 두 번째 자리까지 반올림해야 한다.

---

SELECT ROUND(SUM(CASE WHEN a.cnt = 'immediate' THEN 1 ELSE 0 END) / COUNT(1)*100,2) immediate_percentage 
FROM 
(
SELECT CASE WHEN order_date = customer_pref_delivery_date THEN 'immediate'
       ELSE 'scheduled' END cnt
FROM Delivery
WHERE order_date in(SELECT min(order_date)
                    FROM Delivery
                    GROUP BY customer_id )
)a

WHERE order_date IN()의 IN은 여러 개의 결과를 고려하지만, 고객 별 첫 주문을 정확히 선택하지 않은 문제가 발생.
처음에는 min 함수를 걸어줬기 때문에 가능할거라는 생각을 했었음. 하지만 min함수에서 걸린 수치로 다른 사람의 첫 주문이 다른 주문으로 나올 수 있는 가능성이 발생하였다.....

---

내 정답 쿼리

#1
SELECT ROUND(SUM(CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END) / COUNT(*) * 100, 2)  immediate_percentage
FROM (
    SELECT customer_id, order_date, customer_pref_delivery_date
    FROM Delivery
    WHERE (customer_id, order_date) IN (SELECT customer_id, MIN(order_date) FROM Delivery GROUP BY customer_id)
) first_orders;

#2
SELECT ROUND(SUM(CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END)/COUNT(1)*100,2) immediate_percentage
FROM (
    SELECT *,MIN(order_date) OVER(PARTITION BY customer_id) first_order
    FROM delivery
)a
WHERE order_date = first_order 

---

쿼리 설명

1. 각 고객의 첫 주문을 찾는다.

   • MIN(order_date)를 사용하여 고객별 최초 주문 날짜를 찾음.
   • WHERE (customer_id, order_date) IN (...) 서브쿼리를 사용하여 해당 주문을 필터링.

2. 즉시 주문을 판별하여 개수 계산.

   • CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END 를 사용하여 즉시 주문(=당일 배송)을 판별.
   • 즉시 주문의 개수를 SUM()으로 집계.

3. 즉시 주문 비율을 계산.

   • 즉시 주문 수를 전체 고객 수로 나눈 후 ROUND( , 2)를 사용하여 반올림.

• 윈도우 함수를 사용하기 위해 한번더 풀어봄

  	- MIN(order_date) OVER(PARTITION BY customer_id) AS first_order
  	고객별 최소 주문일을 각 행에 추가.

---

98번 문제 정리

문제 정리

• 각 플레이어의 첫 로그인 날짜(first login date) 를 구해야 함.
• 첫 로그인 다음 날(= 첫 로그인일 + 1)에 다시 로그인한 플레이어 수 / 전체 플레이어 수 를 계산해야 함.
• 결과는 소수점 둘째 자리까지 반올림.

---

내 정답 쿼리

SELECT ROUND(SUM(CASE WHEN event_date = DATE_ADD(first_day, INTERVAL 1 DAY) THEN 1 ELSE 0 END) 
             / COUNT(DISTINCT player_id), 2) AS fraction  
FROM (
    SELECT player_id, event_date,
           MIN(event_date) OVER (PARTITION BY player_id) AS first_day
    FROM Activity
) a;

---

쿼리 분석

1. 각 플레이어의 첫 로그인 날짜 구하기

   • MIN(event_date) OVER (PARTITION BY player_id) AS first_day
   • 윈도우 함수를 사용하여 각 플레이어별 최초 로그인 날짜를 가져옴.
   • event_date와 함께 가져와 첫 로그인 후 활동을 확인할 수 있도록 함.

2. 첫 로그인 다음 날 재로그인한 플레이어 수 계산

   • DATE_ADD(first_day, INTERVAL 1 DAY) = event_date
   • 첫 로그인 다음 날(event_date = first_day + 1일) 방문한 플레이어만 카운트.
   • SUM(CASE WHEN ... THEN 1 ELSE 0 END) 를 사용하여 재로그인한 플레이어 수를 계산.

3. 전체 플레이어 대비 비율 계산

   • COUNT(DISTINCT player_id) → 전체 플레이어 수.
   • 재로그인한 플레이어 수 / 전체 플레이어 수를 구하고 소수점 두 자리까지 반올림.

---

99번 문제 정리

문제 정리

• teacher_id별로 서로 다른 과목(subject_id) 개수를 구해야 함.
• 같은 subject_id를 다른 dept_id에서 가르치더라도 1개로 카운트해야 함.

---

내 정답 쿼리

SELECT teacher_id, COUNT(DISTINCT subject_id) cnt  
FROM Teacher  
GROUP BY teacher_id;

---

쿼리 분석

1. GROUP BY teacher_id

   • teacher_id 기준으로 데이터를 그룹화하여 각 선생님별로 과목 수를 계산.

2. COUNT(DISTINCT subject_id)

   • 같은 teacher_id가 여러 dept_id에서 같은 subject_id를 가르칠 수 있음.
   • 중복을 제거하고 고유한 과목 수만 카운트.

---

100번 문제 정리

문제 요약

• Activity 테이블에서 최근 30일(2019-06-28 ~ 2019-07-27) 동안 활동한 사용자 수를 일별로 구하기.
• 특정 날짜에 한 번이라도 활동한 사용자 수를 active_users로 출력해야 함.
• 결과는 activity_date 기준으로 정렬.

---

내 정답 쿼리

SELECT activity_date day,
       COUNT(DISTINCT user_id) active_users 
FROM Activity
WHERE activity_date BETWEEN DATE_SUB('2019-07-27', INTERVAL 29 DAY) AND '2019-07-27'
GROUP BY activity_date;

---

쿼리 분석

1. DATE_SUB('2019-07-27', INTERVAL 29 DAY)를 사용하여 30일 전(2019-06-28)부터의 데이터 조회.
2. COUNT(DISTINCT user_id)를 사용하여 각 날짜별 고유한 사용자 수를 집계.
3. GROUP BY activity_date를 사용하여 날짜별 사용자 수를 정리.