인공재환

문제 1: 월간 코호트 리텐션 (Retention & Cohort)

[난이도: 상]

문제: 사용자의 가입 월(Signup Month)을 기준으로 **'가입 후 1개월 뒤'와 '가입 후 2개월 뒤'의 리텐션(재방문율)**을 계산하세요.

• 리텐션의 정의: 가입 월에 가입한 전체 사용자 중, 해당 개월(M+1, M+2)에 events 테이블에 기록이 있는 사용자의 비율.
• 결과는 signup_month, month_1_retention, month_2_retention 형태로 출력하세요.

핵심 포인트:

• 가입 월별 기준점 설정 (Cohort)
• 사용자별 첫 방문일과 이후 방문일 사이의 간격 계산 (Date Diff)
• 분모(가입자 수)와 분자(재방문자 수)를 결합한 비율 계산

1. users (사용자 기본 정보)

2. events (사용자 활동 로그)

이 테이블은 사용자가 서비스에 방문하거나 특정 행동을 할 때마다 기록됩니다.

출력 예시)

가입한 달에 대비하여, 다음 달(M+1)과 다다음 달(M+2)에 얼마나 돌아왔는지를 백분율로 보여줍니다.

• 힌트: 가입월 유저수($Users_{M+0}$) 대비 재방문 유저수($Users_{M+n}$)의 비율을 구하세요.

내 풀이

/* 사용자의 가입 월을 기준으로, 
   가입 후 1개월 뒤와 가입 후 2개월 뒤의 리텐션(재방문율)을 계산
   재방문율 =  
signup_date가 가입월 +n이  event_date에 존재하는 회원 수/ 가입 월 기준 고유 전체 회원 수
결과는 signup_month, month_1_retention, month_2_retention형태로 출력.*/ 

WITH SINGUP_MONTH_USERS AS (
    SELECT user_id, COUNT(user_id) AS total_users
    FROM users),
    
ONE_MONTH_RETENTION AS (
    SELECT A.user_id AS user_id,
        (COUNT(*)) / (A.total_users) AS ONE_MONTH_RETENTION
    FROM SINGUP_MONTH_USERS A
    LEFT JOIN events B ON A.user_id = B.user_id
    WHERE DATE_DIFF(mm,A.SIGNUP_MONTH,B.event_date) = 1
),

TWO_MONTH_RETENTION AS (
    SELECT A.user_id AS user_id,
        (COUNT(*)) / (A.total_users) AS  TWO_MONTH_RETENTION
    FROM SINGUP_MONTH_USERS A
    LEFT JOIN events B ON A.user_id = B.user_id
    WHERE DATE_DIFF(mm,A.SIGNUP_MONTH,B.event_date) = 2
)
SELECT A.total_users AS signup_month,
       B.ONE_MONTH_RETENTION AS month_1_retention,
       C.TWO_MONTH_RETENTION AS month_2_retention
FROM SINGUP_MONTH_USERS A
JOIN ONE_MONTH_RETENTION B ON A.user_id = B.user_id
JOIN TWO_MONTH_RETENTION C ON B.user_id = C.user_id;

문제1 - 월 별로 집계해야하는데 그렇지 않음.

최종 정답

WITH user_cohort AS (
    -- 1. 각 유저의 가입월을 구하고, 가입월별 전체 유저 수(분모)를 미리 계산합니다.
    SELECT 
        user_id,
        DATE_FORMAT(signup_date, '%Y-%m-01') AS cohort_month
    FROM users
),
cohort_size AS (
    -- 2. 각 코호트(가입월)에 몇 명이 가입했는지 집계합니다.
    SELECT 
        cohort_month,
        COUNT(DISTINCT user_id) AS total_users
    FROM user_cohort
    GROUP BY cohort_month
),
user_activities AS (
    -- 3. TIMESTAMPDIFF를 사용하여 가입월과 이벤트 발생월 사이의 개월 수 차이를 구합니다.
    SELECT 
        u.cohort_month,
        u.user_id,
        TIMESTAMPDIFF(MONTH, u.cohort_month, DATE_FORMAT(e.event_date, '%Y-%m-01')) AS month_diff
    FROM user_cohort u
    JOIN events e ON u.user_id = e.user_id
)

-- 4. 최종 집계: 코호트 사이즈 대비 M+1, M+2 리텐션을 백분율로 산출합니다.
SELECT 
    c.cohort_month AS signup_month,
    c.total_users AS cohort_size,
    -- M+1 리텐션: 1개월 뒤 활동한 유니크 유저 수 / 전체 가입자 수
    ROUND(COUNT(DISTINCT CASE WHEN a.month_diff = 1 THEN a.user_id END) / c.total_users, 4) AS month_1_retention,
    -- M+2 리텐션: 2개월 뒤 활동한 유니크 유저 수 / 전체 가입자 수
    ROUND(COUNT(DISTINCT CASE WHEN a.month_diff = 2 THEN a.user_id END) / c.total_users, 4) AS month_2_retention
FROM cohort_size c
LEFT JOIN user_activities a ON c.cohort_month = a.cohort_month
GROUP BY c.cohort_month, c.total_users
ORDER BY c.cohort_month;

문제 2: A/B 테스트 퍼널 전환율 분석 (Activation & A/B Test)

[난이도: 중상]

문제: 새로운 상세페이지 디자인(B 그룹)이 기존 디자인(A 그룹)보다 **'조회(view)에서 구매(order)로 이어지는 전환율'**이 얼마나 개선되었는지 분석하세요.

1. 각 그룹별(A, B)로 view 이벤트를 발생시킨 고유 유저 수를 구하세요.
2. 그 유저들 중 실제 orders 테이블에 결제 기록이 있는 고유 유저 수를 구하세요.
3. 결과값은 ab_group, viewers, buyers, conversion_rate 순으로 출력하세요.

핵심 포인트:

• LEFT JOIN을 활용한 유저 단위 퍼널 분석
• 그룹별 집계 (GROUP BY)
• 실제 주문 데이터와 매칭 시점 확인

2. events (행동 로그 데이터)

출력 예시)

단순 클릭이 아니라, '상품 조회(view)'를 한 사람 중 실제 '구매(order)'까지 이어진 비율을 비교합니다.

• 힌트: view 이벤트를 기록한 유저 리스트와 orders 테이블의 유저 리스트를 user_id 기준으로 결합하세요.

문제 3: 고가치 유저(Whale) 탐지 및 Revenue 분석 (Revenue)

[난이도: 상]

문제: 전체 매출의 상위 10%를 차지하는 **'핵심 유저군(Whales)'**의 평균 주문 금액(AOV)과 그들이 가장 많이 구매한 날짜를 찾으세요.

1. 유저별 총 구매 금액을 합산하여 순위를 매깁니다.
2. 누적 매출 비율이 상위 10% 이내에 드는 유저들만 필터링하세요.
3. 이 유저들의 전체 평균 주문 금액(AVG(amount))을 구하세요.

핵심 포인트:

• 윈도우 함수(SUM() OVER)를 활용한 누적합 계산
• 퍼센트 계산을 위한 서브쿼리 활용

3. orders (결제 데이터)

출력 예시)

누적 매출 기여도가 높은 상위 10% 유저들의 구매 행태를 분석합니다.

• 힌트: 윈도우 함수 PERCENT_RANK()나 SUM(amount) OVER()를 사용하여 상위 10% 지점을 찾아보세요.

🚀 문제 4: 리텐션의 심화 - "첫 구매 후 재구매까지의 소요 기간(TT2P)"

[AARRR: Retention & Revenue]

단순한 접속 리텐션이 아니라, '첫 구매(First Order)'를 한 유저가 '두 번째 구매(Second Order)'를 하기까지 평균적으로 며칠이 걸리는지를 구하고자 합니다.

• 요구사항: 1. 유저별로 첫 번째 주문일과 두 번째 주문일을 각각 구하세요. (세 번째 이후 주문은 무시) 2. 첫 구매 후 30일 이내에 재구매한 유저의 비율(%)을 구하세요. 3. 재구매 유저들의 평균 재구매 소요 기간(Day)을 출력하세요.
• 활용 팁: ROW_NUMBER(), LEAD() 또는 LAG(), DATEDIFF()
• 출력 예시: | total_first_buyers | repurchase_rate_30d | avg_days_to_repurchase | | :--- | :--- | :--- | | 5000 | 0.28 (28%) | 14.5 |

테이블 - users (유저)

🚀 문제 5: A/B 테스트의 심화 - "누적 매출 기여도(LTV 7D) 비교"

[AARRR: Activation & Revenue]

가입 후 첫 7일간의 누적 매출액을 통해 A/B 테스트 그룹 간의 질적 성과를 비교해야 합니다. 단순히 '누가 더 많이 샀나'가 아니라 **'누가 더 빨리, 꾸준히 샀나'**가 핵심입니다.

• 요구사항:
  1. 가입일로부터 7일 이내에 발생한 매출만 집계하세요.
  2. A그룹과 B그룹 각각에 대해, 가입자 1인당 평균 누적 매출(ARPU)을 일자별(D+0, D+1 ... D+7)로 구하세요.
  3. 윈도우 함수를 사용하여 일자별로 매출이 쌓이는 구조를 만드세요.
• 활용 팁: SUM() OVER(), DATE_ADD(), LEFT JOIN (가입했으나 구매하지 않은 유저도 포함해야 하므로 필수!)
• 출력 예시: | ab_group | day_since_signup | cumulative_revenue_per_user | | :--- | :--- | :--- | | A | 0 | 1200 | | A | 1 | 1500 | | ... | ... | ... | | B | 7 | 4500 |

테이블 -  events (유저 행동 로그)

🚀 문제 6: 퍼널 분석의 심화 - "이탈 전 마지막 행동(Last Touch) 분석"

[AARRR: Activation]

장바구니(cart)에 상품을 담았지만, 최종 결제(order)를 하지 않고 이탈한 유저들을 찾고, 그들이 장바구니 담기 직전에 수행한 행동이 무엇인지 파악하려고 합니다.

• 요구사항:
  1. 최근 30일 이내에 cart 이벤트는 발생시켰으나, order 기록은 없는 유저를 추출하세요.
  2. 해당 유저들이 cart 이벤트를 발생시키기 직전 1개의 event_type이 무엇이었는지 찾으세요.
  3. 직전 행동별로 이탈 유저 수를 집계하여 내림차순 정렬하세요.
• 활용 팁: LAG() OVER(), CTE, NOT EXISTS 또는 EXCEPT
• 출력 예시: | last_event_before_cart | abandoned_user_count | | :--- | :--- | | search | 850 | | promotion_click | 420 | | direct_view | 150 |

테이블 -  orders (주문/결제 정보)

6장은 정렬이다. 가장 기본적인 알고리즘이므로, 복습 차원에서 핵심만 되짚고 가보자.

많은 정렬이 있지만, 선택/삽입/퀵장은 정렬이다. 가장 기본적인 알고리즘이므로, 복습 차원에서 핵심만 되짚고 가보자.

많은 정렬이 있지만, 선택/삽입/퀵/계수 정렬 정도만 알아보자고 한다.

선택 정렬(Selection Sort) ★

- 모든 수를 탐색하여 가장 작은 수를 맨 앞으로 보내는 것 -> n-1의 수를 탐색..반복

(보낸다는 것은 맨 앞의 수와 바꾼다는 것을 의미)

한 번 코드를 짜보자.

# 선택 정렬 (Selection Sort)
A = [1,5,2,4,7,9]
def selection_sort(lst):
  for i in range(len(lst)): # 원소 개수만큼 반복
    smallest_idx = i    # 인덱스
    for j in range(i+1, len(lst)):
      if A[j] < A[smallest_idx]:
        smallest_idx = j
    A[i],A[smallest_idx] = A[smallest_idx],A[i] # 스왑왑
  return lst
  return A

print(selection_sort(A))

삽입 정렬(Insertion Sort)

- 데이터를 하나씩 확인하며, 각 데이터를 적절한 위치에 "삽입"해보자.

선택정렬과 마찬가지로 2중 for문 -> O(N^2)이지만 조건이 걸려 있기 때문에 최선은 O(N)이 나온다.

구현해보자,

def insertion_sort(lst):
  for i in range(1, len(lst)):
    for j in range(i, 0, -1):
     if lst[j] < lst[j-1]:
       lst[j],lst[j-1] = lst[j-1],lst[j]
  return lst

퀵 정렬 (Quick Sort)

- 기준 값 (pivot)를 설정하고, 그 기준보다 크고 작은 값들의 위치를 바꾸자는 개념 (list 양쪽에서 탐색하면서)

pivot은 lst[0]로 설정하자 (호어 분할 방식)

구현-

def QUICK_SORT(lst, start, end): # start = 0 / end = len(lst)-1
  if start >= end:
    return 
  pivot = start
  left = start + 1 
  right = end
  
  while left <= right: 
    while left <= end and lst[left] <= lst[pivot]:
      left += 1
    while right > start and lst[right] >= lst[pivot]:
      right -= 1
    if left > right:
      lst[right],lst[pivot] = lst[pivot],lst[right]
    else:
      lst[left],lst[right] = lst[right],lst[left]
  QUICK_SORT(lst, start, right -1)
  QUICK_SORT(lst, right+1, end)

재귀함수로 푸는 것이 뽀인뚜

더 파이썬 스럽게 구현해보면 아래와 같다!!

def quick_sort(lst):
  if len(lst) <= 1:
    return
  pivot = lst[0]
  tail = lst[1:] # 피벗을 제외한 리스트
  
  left = [x for x in tail if x <= pivot] # 피벗보다 작은애들
  right = [ x for x in tail if x >= pivot] # 피벗보다 큰 애들
  
  return quick_sort(left) + pivot + quick_sort(right)

계수 정렬 (Counting Sort)

값을 비교하지 않고 개수를 세서” 정렬하는 알고리즘

- 특정 조건(값 범위가 작고, 정수 데이터)에 맞을 때만 사용가능하며, 최고의 경우 퀵 소트보다 빠를 수 있다.

- 최고/최악 모두 O(n+k)임.

- K가 크면 메모리 비용 高

구현

lst = list(int(input('정수 배열을 입력하십시오. : ').spilit(',')))

def counting_sort(arr):
   	arr2 = [0] * (max(arr) + 1)  # 카운팅 할 빈 배열
    ans = []
	for i in arr:
    	arr2[i] += 1         # arr2 카운팅 됨
	
    for i in range(len(arr2)):    # 인덱스를 순회하면서
    	for j in range(arr2[i]):  # 인덱스 값의 수 만큼 반복해서 삽입 = 정렬
    		ans.append(i)
	return ans
    
print(counting_sort(lst))

---------------------------------

책과 별도로, 버블 정렬(bubble sort) 과 병합 정렬(merge sort)도 리뷰하고 가보자.

버블 정렬 (bubble sort)

• 리스트의 첫 원소부터 시작해 인접한 원소를 비교
• 앞의 원소가 뒤보다 크면 서로 교환
• 한 바퀴 돌면 가장 큰 값이 맨 뒤로 이동
• 범위를 하나씩 줄이며 반복

# 리스트의 첫 원소부터 시작해 인접한 원소를 비교
# 앞의 원소가 뒤보다 크면 서로 교환
# 한 바퀴 돌면 가장 큰 값이 맨 뒤로 이동
# 범위를 하나씩 줄이며 반복

lst = list(map(int,input('입력: ').split(',')))

def bubble(arr):
	
    for i in range(len(arr)):
        for j in range(0, len(arr)-i-1):
        	if arr[j] > arr[j+1]:
            	arr[j],arr[j+1] = arr[j+1],arr[j]
    return arr
    
print(bubble(lst))

병합 정렬 (merge sort)

• Divide (분할): 배열을 반으로 나눠서 더 이상 나눌 수 없을 때까지(길이 1) 분할
• Conquer (정복): 분할된 조각들을 정렬된 상태로 병합(merge)하면서 하나로 합침
• 이 과정을 재귀적으로 반복

lst = list(map(int,input('입력 : ').split(',')))

def merge_sort(arr):
	if len(arr) <= 1:
    	return arr
    
    # 분할
    n = len(arr) // 2
    left = merge_sort(arr[:n])
    right = merge_sort(arr[n:])
    
    
    # 병합
    result = []
    i = j = 0 
    while i < len(left) and j < len(right)
    	if left[i] <= right[j]:
        	result.append(left[i])
            i += 1
        else:
        	result.append(right[j])
            j+=1
	result += left[i:]
    result += right[j:]
    return result

이제 마지막으로 책의 예제 문제 세 가지를 풀어보자! (매우 쉬움)

오늘은 5장인 DFS/BFS + 프로그래머스 한 문제를 풀어보자.

01. 프로그래머스의 "가장 큰 수"

보고 든 생각은 자릿수 비교였다.

각 원소의 가장 앞자리수가 큰 수가 앞에 옴 -> 같을 경우 두 번째 자릿수 비교 -> 반복...

하지만 예시처럼 3과 30/ 3과 34의 비교는 어떻게 될까? 

간단하게 3,30,34만 주어졌다고 가정하면 답은 34330이다.

예시와 같이, 원소가 한자리 수 일경우 두 번째 자릿수는 33,44,55와 같이 한 자리수와 같은것으로 간주해도 될 것 같다.

-> 구현하기에 너무 빡세서 그냥 경우의 수로 비교

이제 이를 코드로 짜보았다.

1. 일단 무식하게 버블 정렬 형태로 각 원소의 가장 큰 자릿수를 비교  # str(numbers[j][0] 형태로

2. 만약 numbers[0]이 서로 같다면 ->  numbers[j] + numbers[j+1]  <  numbers[j+1] + numbers[j] 이런 형태로 비교

def solution(numbers):
    n = len(numbers)
    for i in range(n):
        for j in range(0, n-1-i):
            if str(numbers[j])[0] < str(numbers[j+1])[0]:
                numbers[j], numbers[j+1] = numbers[j+1], numbers[j]
    # 만약, 일의 자리수가 같다면-> 다음 자리수를 비교해야함
            elif str(numbers[j])[0] == str(numbers[j+1])[0]:
                if str(numbers[j])+str(numbers[j+1]) < str(numbers[j+1])+str(numbers[j]):
                    numbers[j], numbers[j+1] = numbers[j+1], numbers[j]
    ans = ''.join(map(str, numbers))
    return ans

테스트케이스는 통과했지만, O(N^2)이라서 (input은 100,000까지) 틀렸다ㅠ

이중 for문 쓰지말고 어떻게 푸냐..

내가 생각한 자릿수 비교

 1. 3vs30 -> 3이 커야함.

 2. 878vs87 -> 878이 커야함

 3. 454vs45 -> 45가 커야함

-----------------------------------------

-> 문자열 자릿수 비교를 이용 -> 문제: 3vs30을 30이 크다고 인식함 -> 해결: str(list)*4 해버리자

[ 3, 30 ] *4 -> 3333vs30303030 -> 3이 큼! 

# 예외처리 -> 모든 숫자 0일경우 -> '000000'이 아닌 '0'만 출력해야함.

def solution(numbers):
    numbers = list(map(str, numbers))
    def my_key(x):
        return x * 4
    numbers.sort(key = my_key, reverse=True)
    numbers= ''.join(numbers)
    
    # 예외 처리 -> 모든 숫자 0일 경우?
    if numbers[0] == '0':
        return '0'
    return numbers

이코테 Ch.5

대표적인 탐색 알고리즘으로 BFS/DFS가 있고, 자주 나오므로 꼭 알아두자.

그전에!!!!!!

가장 기본적인 스택/큐를 복습하고 가보자.

스택

- 박스 쌓기를 생각 (후입선출)

- append()와 pop()으로 별도 라이브러리 없이 구현가능하다

pop() -> 가장 뒤쪽의 데이터를 꺼내오는 함수

큐

- 입장 대기 줄 (선입선출)

- deque.append()와 deque.popleft()으로 deque import해야함

from collections import deque

queue = deque()

queue.popleft()

popleft() -> 가장 앞쪽의 데이터를 꺼내오는 함수

재귀 함수

DFS/BFS를 구현하려면 재귀함수도 이해하고 있어야한다.

피보나치/팩토리얼 같이 자신을 다시 호출하는 함수이다.

-> 종료 조건을 까먹지말자.

 DFS (Depth First Search)

깊이 우선 탐색이라고 하며, 그래프에서 깊은 부분을 우선적으로 탐색하는 알고리즘이다.

그래프는 노드(Node / vertex)와 간선(Edge)로 표현되며, 

그래프 탐색이란 하나의 노드를 시작으로 다수의 노드를 방문하는 것을 말한다.

두 노드가 간선으로 연결되어 있는 경우는 '인접하다(Adjacent)'라고 한다.

이 인접하다는 개념을 이용해서, 인접 행렬/ 인접 리스트에 대해서도 알아보자.

인접 행렬: 2차원 배열로 그래프의 연결관계를 표현하는 것

인접 리스트: 리스트로 그래프의 연결 관계를 표현하는 방식

(=> 연결 리스트로 구현하는데, Python의 경우 C++과 다르게 별도 라이브러리 필요가 없다.)

인접 행렬 vs 인접 리스트

-> 메모리 측면: 인접 행렬은 모든 관계를 저장하므로 메모리 측면에서 비효율

                        인접 리스트는 연결된 정보만을 저장하므로 효율적

하지만 이 때문에 인접 리스트는 특정 두 노드의 연결 여부 정보를 얻는 속도가 느림 (하나하나 확인해야해서)

((((만약 노드 1과 2과 연결 되어 있는 지가 궁금하다면

인접 행렬의 경우 graph[1][2]만 하면 바로 나오지만,

인접 리스트의 경우 앞에서부터 차례대로 확인해야 함!))))

다시 앞으로가서 DFS는 깊이 우선 탐색 알고리즘이라고 했다.

DFS는 특정 경로로 탐색하다가 특정한 상황에서 최대한 깊숙이 노드를 방문 한 후 다시 돌아가 다른 경로를 탐색하는 알고리즘이다.

구체적인 동작 과정은 아래와 같다.

1. 탐색 시작 노드를 스택에 삽입하고 방문 처리를 한다.
2. 스택의 최상단 노드에 방문하지 않은 인접 노드가 있다면 그 인접노드를 스택에 넣고 방문 처리를 한다.
   방문하지 않은 인접 노드가 없으면 스택에서 최상단 노드를 꺼낸다.
3. 2번의 과정을 더이상 수행할 수 없을 때까지 반복한다. 

이 그림을 예시로 들어보면, DFS 과정은 아래와 같다,

[노드 방문 순서]

1 -> 2 -> 7 -> 6 -> 8 -> 3 -> 4 -> 5

스택의 흐름:

1: 1
2: 1, 2
3: 1,2,7
4: 1,2,7,6
5: 1,2,7 (6pop)
6: 1,2,7,8
7: 1,2,7 (8pop)
8: 1,2 (7pop)
9: 1 (2pop)
10: 1,3
11: 1,3,4
12: 1,3,4,5

이제 DFS를 코드로 구현해보자.

(힌트 : 스택/재귀함수를 활용해서)

graph = [[], [2,3,8], [1,7], [1,4,5], [3,5], [3,4], [7], [2,6,8], [1,7]]
for i in range(len(graph)):
    graph[i].sort()
def DFS(v, visited):        # graph;연결리스트,
                            # v; 시작 노드(주어짐)
                            # visited;방문한 노드들을 표시하는 리스트 
  # visited = [False,False,False,...False]
  graph = [
    [],        # 편한 인덱싱을 위해서 0은 비워둠
    [2,3,8],   # 1번 노드 = 2,3,8과 연결
    [1,7],     # 2번 노드 = 1,7과 연결
    [1,4,5],   # .
    [3,5],     # .
    [3,4],     # .
    [7],       # .
    [2,6,8],   # 7번 노드 = 2,6,8과 연결
    [1,7]      # 8번 노드 = 1,7과 연결
    ]
  
  visited[v] = True         # 현재 노드를 방문처리
  print(v, end='')
    
  for i in graph[v]:
    if not visited[i]:
      DFS(i ,visited)
      
visited = [False] * 9
DFS(1, visited)

재귀함수 없이, 스택으로 구현할 수도 있다!

1. "더이상 방문할 노드가 없으면" -> for문에서 종료

2. "상위 노드로 타고 올라가서 반복해" -> 마지막 노드(스택에 저장된 값)로 이동, 1을 반복

BFS (Breadth - First - search)

너비 우선 탐색, 즉 "가까운 노드"부터 탐색한다고 생각하면 된다.

"스택"이 아니라 "큐" 자료구조를 이용한다고 생각하면 쉽다!

정확한 동작 방식은 아래와 같다.

1. 탐색 시작 노드를 큐에 삽입 (+방문 처리)
2. 큐에서 노드를 꺼내 그 인접한 노드 중에서 방문하지않은 모든 노드를 큐에 삽입 (+방문 처리)
3. 2번의 과정을 더 이상 수행할 수 없을 때 까지 반복

이제 DFS와 마찬가지로 예제를 통해서 생각해보자.

[노드 방문 순서]

1 -> 2 -> 3 -> 8 -> 7 -> 4 -> 5 -> 6

큐의 흐름:

1: 1
2: 1 꺼내고, 1과 인접한 노방문 노드 : 2,3,8 -> 큐: 2,3,8 (누적:1,2,3,8)  
3: 2 꺼내고, 2와 인접한 노방문 노드: 7 -> 큐: 3,8,7 (누적:1,2,3,8,7)
4: 3꺼내고 3과 인접한 노방문 노드: 4,5 -> 큐: 8,7,4,5(누적:1,2,3,8,7,4,5)
5: 8꺼내고 8과 인접한 노방문 노드: 없음 -> 큐: 7,4,5

6: 7꺼내고 7과 인접한 노방문 노드: 6추가 -> 큐: 4,5,6

덱 라이브러리를 활용해서 구현해보자!

from collections import deque
graph = [
[], [2,3,8], [1,7], [1,4,5], [3,5], [3,4], [7], [2,6,8], [1,7]
]
visited = [False] * 9
ct = 0

def BFS(graph, start, visited):
  queue = deque([start])
  visited[start] = True
  
  while queue: # queue가 비어있으면 종료
    v = queue.popleft() # 먼저 들어온 노드 부터 탐색합시다
    print(v, end= '')
    
    for i in graph[v]:
      if not visited[i]:
        visited[i] = True
        queue.append(i)

DFS보다 구현은 쉽다. "다시 올라가"는 개념이 필요없으니까 더욱 직관적이라서

이제 BFS/DFS개념을 참고해서 문제를 풀어보자!

1. DFS (재귀)로 풀어보자.

graph = []
for i in range(N):
  graph.append(list(map(int, input('띄어쓰지말고 0/1를 입력하십시오 : '))))

visited = [[False] * M for _ in range(N)]

def dfs(x,y):
  if x < 0 or y <0 or x>= N or y >= M:
    return
  if graph[x][y] == 1 or visited[x][y]:
    return
  visited[x][y] = True
  dfs(x-1,y)
  dfs(x+1,y)
  dfs(x,y+1)
  dfs(x,y-1)
  
ct = 0
for i in range(N):
    for j in range(M):
      if graph[i][j] == 0 and not visited[i][j]:
        dfs(i,j)
        ct += 1
print(ct)

2. BFS로도 풀어보자.

from collections import deque
queue = deque([])
N,M = map(int,input('N/M을 입력해').split(','))
graph = []
ans = 0 

for i in range(N):
  graph.append(list(map(int, input())))
  # graph 만들어짐
      
dx = [-1,1,0,0]
dy = [0,0,1,-1]

# BFS로 구현 -> queue.popleft / while 활용
def BFS(x,y):
  queue.append(x,y)
  graph[x][y] = 1
  
  while queue:
    cx,xy = queue.popleft()
    for i in range(4):
      nx = cx + dx
      ny = dx + dy
      if nx >= N and ny >= M and nx >=0 and ny >= 0 and graph[nx][ny] == 0:
        graph[nx][ny] = 1
        queue.append((nx,ny))
ans = 0
for i in range(N):
  for j in range(M):
    if graph[i][j] == 0:
      BFS(i,j)
      ans += 1
print(ans)

다음 문제!

# 미로 탈출
# 행렬이 주어지고, 현재 위치는 항상 1,1 이며 탈출구는 NxM이다. 1로 되어 있는 길을 찾아서 이동해야하고,
# 이동할때마다 +1, 출력하면댐
from collections import deque
graph =[]
N,M = map(int,input('두 정수를 입력하십시오. :').split(','))
for i in range(N):
  graph.append(list(map(int, input().strip())))
dx = [0,0,1,-1]
dy = [1,-1,0,0]

def BFS(x,y):
  queue = deque()
  queue.append((x,y))
  
  while queue:
    x,y = queue.popleft()
    for i in range(4):
      nx = x + dx[i]
      ny = y + dy[i]
      # 범위 벗어나면 무시
      if nx < 0 or nx >= N or ny < 0 or ny >= M:
        continue
      # 벽(0)이면 무시
      if graph[nx][ny] == 0:
        continue
      # 처음 방문하는 길(1)이면, 이전 칸 + 1로 거리 기록
      if graph[nx][ny] == 1:
        graph[nx][ny] = graph[x][y] + 1
        queue.append((nx,ny))
  return graph[N-1][M-1] # 이이 최단거리
    
print(BFS(0,0))

엄격히 말하면 알고리즘의 범주는 아니지만, 많은 기업에서 구현 문제도 코테에 포함되므로, 필수적으로 공부해야한다!

책에서는 구현 능력을 피지컬이라고 표현한다. ( <-> 뇌지컬)

바로 실전 문제를 풀어보자.

A = int(input('N을 입력하시오 : '))
B = input('계획서 내용을 입력하시오. :').split(' ')

x,y = 1,1
hang = [0,0,-1,1]
yeol = [-1,1,0,0]
lst = ['L','R','U','D']
for i in range(len(B)):
  if B[i] in lst:
    idx = lst.index(B[i])
    x += hang[idx]
    y += yeol[idx]  
    if x == 0 or x > A or y == 0 or y > A:  # 만약 벗어났다면
      x -= hang[idx] # 원래대로
      y -= yeol[idx]
print(x,y)

아이디어1: 행/열을 두 개로 쌍을 지어서 리스트만들어놓기

아이디어2: 조건에 벗어날 경우 원래대로

혹은

아이디어2를 수정 -> 깔끔한 코드

A = int(input('N을 입력하시오 : '))
B = input('계획서 내용을 입력하시오. :').split(' ')

x,y = 1,1
hang = [0,0,-1,1]
yeol = [-1,1,0,0]
lst = ['L','R','U','D']
for i in range(len(B)):
  if B[i] in lst:
    idx = lst.index(B[i])
    dx = x + hang[idx]
    dy = y + yeol[idx]  
    if dx == 0 or dx > A or dy == 0 or dy > A:  # 만약 벗어났다면
      continue
    x,y = dx,dy
print(x,y)

다음 문제로 가보자.

정답

ct = 0
A = int(input('0~23 사이 정수 입력하시오 : '))

for i in range(A+1):
       for j in range(60): # 분
              for k in range(60):  # 초
                 if '3' in str(i) or '3' in str(j) or '3' in str(k):
                     ct += 1
print(ct)

꼭 다시풀어보자 ★ ★ ★ ★ ★ ★

다음 문제

왕실의 나이트

★★ 상하좌우 풀었던 코드를 떠올리면 쉽게 풀 수 있다 ★★

A = input('현재 좌표를 입력하시오 : ')
steps1= [2,-2,2,2,1,-1,1,-1]
steps2= [-1,1,1,-1,2,2,-2,-2]

yeol = ord(A[0]) - 96 # x좌표
hang = int(A[1])      # y좌표
ct = 0
tmp = (hang,yeol)
for i in range(len(steps1)):
       dx = yeol + steps1[i]
       dy = hang + steps2[i]
       if dx > 0 and dy > 0 and dx < 9 and dy < 9:  # 만약 8x8좌표내부에 있을 경우,
              ct += 1                               # 해당 경우의 수 count + 1
       else:
              continue
print(ct)

#1일차

그리디 알고리즘 

-> "현재 상황에서" 최고의 이득/점수를 가져올 수 있는 알고리즘,

즉, 지금 당장 좋은 것만 고르는 방법임.

다시말하면 최적의 선택은 아닐 수 있다!

코테의 관점에서 바라보면, 그리디알고리즘은 그 범위가 매우 광범위하므로, 많은 유형을 풀어보면서 감을 익혀여한다.

(다익스트라도 그리디로 분류되기 때문에, 특정 유형은 암기가 필요함)

EX) 거스름돈

카운터에는 거스름돈으로 사용할 500원,100원,50원,10원 동전이 무한하다고 가정해보자

손님에게 거슬러 줘야할 돈이 N원일 때 거슬러줘야 할 동전의 최소 개소를 구하시오!

단, 거슬러 줘야 할 돈은 N은 항상 10의 배수이다.

-> 킥: 가장 적은 동전을 줘야하므로, 500,100,50,10원 순으로 MAX를 사용해야한다.

lst = [500,100,50,10]
N= int(input('10의 배수 숫자를 입력하시오. : '))
tmp = 0
for i in lst:
  tmp += (N // i)
  N = N % i          # 다음과 같이 바꾸자. N %= i
print(tmp)

위 코드 시간복잡도는 O(N)이다. 화폐의 종류가 K개라면 O(K)인 것.

큰 수의 법칙

★인덱스 다르면 다른 수로 간주 ★

lst = [6,3,6,4] 이고, m=8, k=3 라면, 6+6+6 + 6+6+6 + 6+6이 가능

# 배열의 크기 N, 더하기 개수 M, 연속 사용 횟수 K가 주어질 때 합을 출력
N = int(input('리스트 길이를 입력하시오 : '))
m= int(input('m을 입력하시오 : '))
k = int(input('k를 입력하시오 : '))

x = list(map(int, input().split(' ')))
x.sort(reverse = True)

tmp = 0
for i in lst:
  if m > k:
    tmp += i * k
    m -= k
  elif m == k:
    tmp += m * i
    break
  else:
    tmp += i * k
    break

print(tmp)

처음 풀어본 코드 -> 능지이슈..

위 문제는 전형적인 그리디 알고리즘 문제라고한다.!

해설

: 입력값 중에서 가장 큰 수와 두 번째로 큰 수만 저장하면된다..

만약 두 개가 같은 경우는 그 수에 곱하기 k가 정답이고,

아니라면 m이 0될 때 까지  큰 수 세번 더하고 두 번째로 큰 수 한번 더하고 반복

# 배열의 크기 N, 더하기 개수 M, 연속 사용 횟수 K가 주어질 때 합을 출력
n,m,k = map(int, input().split(' '))
x = list(map(int, input().split(' ')))
x.sort(reverse = True)

result = 0
a,b = x[0],x[1]

if x[0] == x[1]:
  result += m * x[0]
else:
  while True:
    for i in range(k):
      if m == 0:
        break
      else:
        result += a
        m -= 1
    if m == 0:
      break
    else:
      result += b
      m -=1
print(result)
    else:
      result += b
      m -=1
print(result)

★★ for문에서, if문을 걸어서 하나씩 더할 때 마다 m의 0에 안걸리는 조건문 거는 것!

이건 무식하게 푼 것이고, 수열의 개념에서 접근하자면 아래와 같다.

가장 큰 수와 두번째로 큰 수가 같다면 답은 바로 가장 큰 수 * m.

else: (가장 큰 수 x k + 두 번째로 큰 수) + (가장 큰 수 x k + 두 번째로 큰 수).. 반복

즉, k+1이 반복된다. 

따라서 이를 아래와 같이 구현할 수 있다.

# 배열의 크기 N, 더하기 개수 M, 연속 사용 횟수 K가 주어질 때 합을 출력
n,m,k = map(int, input().split(' '))
x = list(map(int, input().split(' ')))
x.sort(reverse = True)

result = 0
a,b = x[0],x[1]
pair = (a*k + b)
# k+1 // m == 0이면 result = m*a, 나머지가 있을경우
#: 나머지 x * a

# pair가 몇 번 도는지 계산
count =  (m // (k+1))
rest_count =  (m % (k+1))
result += count * pair
result += rest_count * a

print(result)

이렇게 for문없는 시간복잡도 낮은 코드를 칠 수 있다! 복습 必

실전 문제#3 

입/출력 양식

 Input

1. N , M (공백으로 구분하여 입력받음)

2. 각 row의 원소의 숫자들 (공백으로 구분하여 입력받음)

생각

1. 각 행의 원소들 중에서 가장 큰 낮은 값들을 새로운 리스트에 저장(순서 지켜서 나중에 인덱스 호출)

2. 그 리스트 중에서 가장 큰 값 찾기

파이썬 min() / max()활용?

N, M = map(int, input(' N과 M을 공백으로 구분하여 입력하시오.').split(' '))
lst = []
for i in range(N):
  x = list(map(int, input(' ').split(' ')))
  lst.append(x)
new_lst = [] # 각 행의 원소들 중 가장 작은 값은 원소들의 집합

for i in lst:
  new_lst.append(min(i))
print(max(new_lst))

EZ

N, K = map(int, input(' ').split(' '))
ct = 0

while N != 1:
  if N % K == 0:
    N = N // K
    ct += 1
  else:
    N -= 1
    ct += 1
print(ct)

EZ

오늘은 어려운 응용 문제 3가지를 풀어봅시다.

✅ 문제 1: 가입 → 첫 주문까지 30일 이내인 유저의 평균 지연일 & 상품군 분석

테이블

• users(user_id, signup_date)
• orders(order_id, user_id, order_date)
• order_items(order_id, item_id)
• items(item_id, category)

요구사항

• 각 카테고리(category) 별로,
  • 가입 후 30일 이내 첫 구매한 유저만 집계
  • 해당 유저들의 가입 → 첫 주문까지 평균 일수
• 카테고리, 가입월(signup_date) 기준으로 정리

첫 풀이)

WITH first_buyers AS (
    SELECT user_id, MIN(order_date) AS order_date
    FROM orders
    GROUP BY user_id),
    
    30_buyers AS(
    SELECT A.user_id AS user_id, A.signup_date AS signup_date, B.order_date AS order_               date 
        CASE WHEN DATEDIFF(A.signup_date,B.order_date) <= 30 THEN 1
        ELSE 0 
        END AS good_users
    FROM users A
    JOIN first_buyers B ON A.user_id = B.user_id
    GROUP BY user_id)
    
SELECT A.user_id AS user_id, 
       AVG(DATEDIFF(A.signup_date,B.order_date)) AS avg_order,
       C.category AS category

FROM 30_buyers A
JOIN order_items B ON A.order_id = B. order_id
JOIN items C ON B.item_id = C.item_id

WHERE A.good_users = 1 

GROUP BY category
ORDER BY category, signup_date

 틀린점 : 

1. 30_buyers CTE절에서 user_id 쓰면 X

2. 문제에서 카테고리와 가입월 기준으로 정리하라했음 -> GROUP BY 에 추가

WITH first_buyers AS (
    SELECT user_id, MIN(order_date) AS order_date
    FROM orders
    GROUP BY user_id),
    
    30_buyers AS(
    SELECT A.user_id AS user_id,
        B.order_id AS order_id,
        A.signup_date AS signup_date,
        B.order_date AS order_date,
        CASE WHEN DATEDIFF(A.signup_date,B.order_date) <= 30 THEN 1
        ELSE 0 
        END AS good_users
    FROM users A
    JOIN first_buyers B ON A.user_id = B.user_id
    GROUP BY user_id)
    
SELECT AVG(DATEDIFF(A.signup_date,A.order_date)) AS avg_order,
       C.category AS category,
       A.signup_date AS signup_date

FROM 30_buyers A
JOIN order_items B ON A.order_id = B.order_id
JOIN items C ON B.item_id = C.item_id

WHERE A.good_users = 1 

GROUP BY category, signup_date
ORDER BY category, signup_date

🔹 문제 2: 장바구니 → 구매 퍼널 + 장바구니 미구매 이탈율 분석

테이블

• users(user_id, signup_date)
• events(user_id, event_type, event_date)
  • event_type: 'add_to_cart', 'purchase'
• order_items(order_id, item_id, user_id, event_date)
• items(item_id, category)

요구사항

1. 가입 후 30일 이내에 장바구니 추가한 유저 중,
2. 장바구니 추가 후 14일 이내에 구매한 유저는 전환, 그렇지 않으면 이탈
3. 각 상품 카테고리(category) 기준으로,
   • 전환율(전환 유저 수 / 전체 장바구니 유저 수)
   • 이탈율(이탈 유저 수 / 전체 장바구니 유저 수)
   • 전체 유저 수
4. 가입월 기준으로 정리

첫 풀이

WITH users AS (
    SELECT DISTINCT A.user_id AS cartman, 
    MIN(A.signup_date) AS signup_date,
    MIN(C.event_date) AS event_date
    
    FROM users A
    JOIN events B ON A.user_id = B.user_id
    JOIN order_items C ON B.user_id = C.user_id
    
    WHERE B.event_type = 'add_to_cart'),
    
    30_buyers AS (
    SELECT cartman, event_date, signup_date
           CASE WHEN DATEDIFF(signup_date, event_date) <= 30 THEN 1
           ELSE 0
           END AS 30_buyers 
    FROM users),
    
    convertion_users(
    SELECT A.cartman AS cartman,
           A.signup_date AS signup_date
           ,CASE WHEN DATEDIFF(A.event_date,B.event_date) <= 14 THEN 'convertion'
           ELSE 'non_convertion'
           END AS CONV
    FROM 30_buyers A
    JOIN events B ON A.user_id = B.user_id    
    WHERE B.event_type ='purchase')
    
SELECT (SELECT COUNT(CONV) FROM convertion_users WHERE CONV = 'convertion')/COUNT(cartman) AS convertion_rate,
    (SELECT COUNT(CONV) FROM convertion_users WHERE CONV = 'non_convertion')/COUNT(cartman) AS non_convertion_rate,
    MONTH(signup_date) AS signup_date,
    (SELECT COUNT(DISTINCT user_id) FROM users) AS ALL_users
FROM convertion_users
GROUP BY signup_date

문제:

맨처음 users-> C 조인할 필요없음 + event_date를 C가 아니라 B로 바꾸기

맨 마지막 -> users에서 cartman을 불러와야함 user_id가 아니라!

나머진 굿

🔹 문제 3 (난이도 ↑↑↑)

📌 테이블

• users(user_id, signup_date)
• logins(user_id, login_date)
• orders(order_id, user_id, order_date)
• order_items(order_id, item_id)
• items(item_id, category)

📌 요구사항

1. 가입 월(Cohort) 기준 그룹화
2. 각 유저가 가입 후 7일 이내에 재방문(로그인)했는지 여부 계산
3. 7일 이내 재방문자 중 7일 이내 구매까지 한 유저 비율 계산
4. 주문 시 포함된 상품의 카테고리 기준으로 결과 분리
5. 최종 출력 컬럼
   • signup_month
   • category
   • retention_rate (7일 이내 재방문자 / 전체 가입자)
   • conversion_rate (7일 이내 재구매자 / 전체 가입자)

첫 풀이

WITH first_login AS (
    SELECT A.user_id AS user_id,
           A.signup_date AS signup_date,
           MIN(B.login_date) AS login_date
    FROM users A
    JOIN logins B ON A.user_id = B.user_id
    GROUP BY user_id),
    
    7_days_visitors AS (
    SELECT DISTINCT user_id AS user_id
        ,signup_date,
        login_date,
        CASE WHEN DATEDIFF(signup_date,login_date) <= 7 THEN 1 ELSE 0
        END AS good_users
    FROM first_login
        ),
    
    order_users AS(
    SELECT COUNT(A.user_id) AS total_signup,
        MONTH(A.signup_date) AS signup_month,
        SUM(A.good_users) AS 7_visitors,
        CASE WHEN DATEDIFF(A.login_date, B.order_date) <= 7 THEN 1 ELSE 0 
        END AS 7_buyers
    FROM 7_days_visitors A
    JOIN orders B ON A.user_id = B.user_id)
    
SELECT A.signup_month AS signup_month,
       C.category AS category,
       (A.7_visitors / A.total_signup) AS retention_rate,
       (SUM(A.7_buyers) / A.total_signup) AS conversion_rate
       
FROM order_users A
JOIN order_items B ON A.order_id = B.order_id
JOIN items C ON B.item_id = C.item_id

GROUP BY signup_month

틀린 점 ->

1. 마지막 GROUP BY -> SELECT한 카테고리도 포함해야함!

• MySQL은 기본적으로 SELECT 절에 있는 컬럼은 모두 GROUP BY에 있어야 함.
• 지금은 SELECT signup_month, category인데, category를 GROUP BY하지 않으면 에러 또는 비정의 집계 발생 가능.

2. 마지막 A.B 조인할 때 A에는 order_id없음 

3. order_users에서 CASE WHEN 틀림 -> GROUP BY user_id로 묶어줘야함 

 -> GROUP BY user_id 아니면 SUM(CASE WHEN ...)

🔹 다음 문제: 카테고리별 Top 2 구매 유저 찾기

테이블

• orders(order_id, user_id, order_date)
• order_items(order_id, item_id)
• items(item_id, category)

요구사항

• 각 카테고리별로 가장 많이 구매한 유저 TOP 2를 구하세요
• 동점 시 모두 포함 (RANK 사용)
• 최종 출력: category, user_id, purchase_count, rank

🔹 문제: 카테고리별 Top 2 구매 유저 찾기 (RANK)

테이블

• orders(order_id, user_id, order_date)
• order_items(order_id, item_id)
• items(item_id, category)

요구사항

• 각 카테고리별로 가장 많이 구매한 유저 TOP 2를 구하세요
• 동점 시 모두 포함 (RANK 사용)
• 최종 출력: category, user_id, purchase_count, rank

WITH top2 AS (
    SELECT A.user_id,
           COUNT(*) AS purchase_count
           ,DENSE_RANK() OVER(PARRITION BY C.category ORDER BY COUNT(order_id) DESC) rk
    FROM orders A
    JOIN order_items B ON A.order_id = B.order_id
    JOIN items C ON B.item_id = C.item_id
    GROUP BY A.user_id, C.category
    )

SELECT 
      B.category AS category, 
      A.user_id AS user_id,
      A.purchase_count AS purchase_count,
      A.rk AS rk
FROM top2 A
JOIN items B ON A.item_id = B.item_id
WHERE rank = 2
GROUP BY category

PARTITION BY를 까먹었더니 아래처럼 답변해준다,,,,

친절한 설명 고마워요

GROUP BY를 자꾸 틀려서 아래처럼 혼나버렸어요. 

기본중에 기본인데 정신 똑바로 차리겠습니다

"GROUP BY는 줄 세우는 거고,
SELECT에 있는 애들은 줄 기준이거나, 줄에서 집계된 것만 써야 한다!"

그 외의 애가 SELECT에 들어오면 👉 "너 뭐냐?" 하면서 SQL이 화냅니다. 😡

이제 마지막 실전문제를 내달라고해봤다.

여러분들도 한 번 풀어보세요!

✴️ 실무형 A/B 테스트 + 리텐션 전환 분석 문제

💡 시나리오

당신은 마케팅팀 데이터 분석가입니다.
신규 유저를 대상으로 A/B 쿠폰 실험을 진행했고, 이후 가입 후 7일 이내에 접속한 유저를 리텐션 유저로 간주합니다.

실험 종료 후, 유저들이 어떤 상품 카테고리를 가장 많이 구매했는지,
그리고 A/B 그룹별로 리텐션된 유저 중 전환율이 높은 유저 상위 3명을 식별하는 것이 목표입니다.

📦 테이블 구조

📌 요구사항

다음 조건을 만족하는 SQL을 작성하시오:

1. 가입 후 7일 이내 최소 1회 로그인한 유저를 리텐션 유저로 분류
2. 리텐션 유저 중 상품을 1번이라도 구매한 유저만 대상
3. 이들이 **어떤 카테고리(category)**를 가장 많이 구매했는지 확인 (카테고리별 구매 건수 집계)
4. 그룹별(ab_group)로 전환율 상위 3명 유저 추출 (전환율 = 총 주문수 / 총 로그인수)
5. RANK를 써서 A/B 그룹 내 전환율 Top 3 유저만 필터링

✅ 출력 예시

[1단계] 카테고리별 구매 건수 (리텐션 유저 한정)

[2단계] 전환율 상위 3명 유저 (그룹별)

단, 동점이면 같은 순위(DENSE_RANK), 최대 3명 이상 나올 수 있음

1단계)

WITH login AS (
    SELECT A.user_id AS user_id,
           A.signup_date AS signup_date,
           MIN(B.login_date) AS login_date
    FROM users A
    JOIN logins B ON A.user_id = B.user_id
    GROUP BY A.user_id, signup_date)

WITH retention AS (
    SELECT C.category AS category,
           COUNT(*) AS order_nums
    FROM login A
    JOIN orders B ON A.user_id = B.user_id
    JOIN order_items OI ON B.order_id = OI.order_id
    JOIN items C ON OI.item_id = C.item_id
    WHERE DATEDIFF(A.signup_date,B.login_date) <= 7
    GROUP BY  C.category)

2단계)

WITH login AS (
    SELECT A.user_id AS user_id,
           A.signup_date AS signup_date,
           A.ab_group AS ab_group,
           MIN(B.login_date) AS login_date,
           COUNT(B.login_date) AS total_login
    FROM users A
    JOIN logins B ON A.user_id = B.user_id
    GROUP BY A.user_id, A.signup_date, A.ab_group),
    
    retention AS (
    SELECT A.ab_group AS ab_group,
           A.user_id AS user_id,
           COUNT(B.order_id) AS total_order,
           A.total_login AS total_login
    JOIN orders B ON A.user_id = B.user_id
    JOIN order_items OI ON B.order_id = OI.order_id
    JOIN items C ON OI.item_id = C.item_id
    WHERE DATEDIFF(A.signup_date,B.login_date) <= 7
    GROUP BY A.ab_group , A.user_id),
    convertion AS(
    SELECT user_id,
           ab_group,
           total_login,
           total_order,
           (total_login/total_order) AS convertion_rate,
           DENSE_RANK() OVER(PARTITION BY ab_group ORDER BY (total_order/total_login) DESC) rank
    FROM retention
    GROUP BY ab_group, user_id)
    
SELECT *
FROM convertion
WHERE rank = 3

🟦 문제 D: 구독 전환율 분석 ★★★★★★ (3번 틀림)

✅ 테이블

subscriptions(user_id, event_type, event_time)

• event_type은 'free_trial_start', 'paid_subscribe' 둘 중 하나

✅ 요구사항

• 무료 체험 시작 후 14일 이내에 유료 전환한 유저를 전환 유저로 간주
• 월별(event_time) 기준으로:
  • 무료 체험 시작 유저 수 (trial_users)
  • 그 중 14일 이내에 유료 전환한 유저 수 (converted_users)
  • 전환율 (converted_users / trial_users), 소수점 둘째 자리까지

✅ 출력 예시

WITH free_trial AS (
    SELECT user_id, MIN(event_time) AS MONTH
    FROM subscriptions
    WHERE event_type = 'free_trial_start'
    GROUP BY user_id),
    
    conversion_users AS(
    SELECT user_id, MIN(event_time) AS MONTH
    FROM subscriptions
    WHERE event_type = 'paid_subscribe'
    GROUP BY user_id),
    
    converted_users AS (
    SELECT A.user_id AS trial_users,
           CASE WHEN DATEDIFF(B.MONTH, A.MONTH) <= 14 THEN 1
           ELSE 0
           END AS binary,
           DATE_FORMAT(A.MONTH, '%Y-%m') AS event_month
    FROM free_trial A
    LEFT JOIN conversion_users B ON A.user_id = B.user_id
    )
                      
SELECT  COUNT(DISTINCT trial_users) AS trial_users
       ,SUM(binary) AS converted_users
       ,ROUND(SUM(binary) / COUNT(DISTINCT trial_users),2) AS converted_rate
FROM converted_users
GROUP BY DATE_FORMAT(event_month, '%Y-%m')

꼭 다시 풀자!!!!!!!!!!!!!!!

🟦 문제 E: 장바구니 담고 구매하지 않은 유저 분석

✅ 테이블

user_events(user_id, event_type, event_time)

• event_type: 'browse', 'add_to_cart', 'purchase' 중 하나

✅ 문제 설명

1. 유저가 장바구니에 상품을 담은 시점 (add_to_cart) 이후
2. 24시간 내에 구매 (purchase)가 없으면 → 이탈로 간주
3. 월별(event_time) 기준으로 다음을 구하시오:

• 장바구니 유저 수
• 이탈 유저 수
• 이탈율 = 이탈 유저 수 / 장바구니 유저 수 (소수점 둘째 자리까지)

WITH cart AS (
    SELECT user_id, MIN(event_time) AS time
    FROM user_events
    WHERE event_type = 'add_to_cart'
    GROUP BY user_id),
    
    purchase AS (
    SELECT user_id, MIN(event_time) AS time
    FROM user_events
    WHERE event_type = 'purchase'
    GROUP BY user_id),
    
    out_users AS (
    SELECT DISTINCT A.user_id AS users, CASE 
        WHEN (TIMESTAMPDIFF(HOUR, A.time, B.time) <= 24) THEN 0 -- 전환
        ELSE 1 -- 이탈
        END AS converted,
        DATE_FORMAT(A.time,"%Y-%m") AS TIME
    FROM cart A LEFT JOIN purchase B ON A.user_id = B.user_id)

SELECT COUNT(users) AS cart_users,
       SUM(converted) AS exit_users,
       ROUND(SUM(converted)/COUNT(users),2)
FROM out_users
GROUP BY TIME

핵심 1. out_users CTE절에서 DATE를 CART인지, PURCHASE 인지 확실하게 알기

핵심 2. 이탈 유저 수 -> 전환자의 여집합으로 풀이 -> why? 이탈 유저 수의 조건이 한 개가 아님. (24시간 이후 구매 및 장바구니만 담은 고객 (NULL) + ..) 따라서 전환자 1 ELSE 0 후 SUM으로 집계

+ 프로그래머스 문제)

https://school.programmers.co.kr/learn/courses/30/lessons/131534

첫 풀이

WITH BUYERS_2021 AS (
    SELECT DISTINCT  A.USER_ID AS BUYERS
                    ,B.SALES_DATE AS SALES_DATE
    FROM USER_INFO A
    INNER JOIN ONLINE_SALE B ON A.USER_ID = B.USER_ID
    WHERE YEAR(A.JOINED) = 2021),
 
    BUYERS_ALL AS (
    SELECT DISTINCT USER_ID AS ALL_USERS, JOINED
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021)
    
SELECT   YEAR(A.SALES_DATE) AS YEAR
        ,MONTH(A.SALES_DATE) AS MONTH
        ,COUNT(A.BUYERS) AS PURCHASED_USERS
        ,ROUND( (COUNT(A.BUYERS) / COUNT(B.ALL_USERS)) , 2) AS PUCHASED_RATIO
        
FROM BUYERS_2021 A
JOIN BUYERS_ALL B ON A.BUYERS = B.ALL_USERS

GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH

문제점 -> 분모 (2021년 가입한 전체 유저가 JOIN이후 바뀌어버림!)

해결 -> 서브쿼리로 고정 + 쓸데없이 마지막 SELECT 문에서 CTE 두개 합치지 않기

WITH BUYERS_2021 AS (
    SELECT DISTINCT  A.USER_ID AS BUYERS
                    ,B.SALES_DATE AS SALES_DATE
    FROM USER_INFO A
    INNER JOIN ONLINE_SALE B ON A.USER_ID = B.USER_ID
    WHERE YEAR(A.JOINED) = 2021),
 
    BUYERS_ALL AS (
    SELECT DISTINCT USER_ID AS ALL_USERS, JOINED
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021)
    
SELECT   YEAR(A.SALES_DATE) AS YEAR
        ,MONTH(A.SALES_DATE) AS MONTH
        ,COUNT(A.BUYERS) AS PURCHASED_USERS
        ,ROUND( (COUNT(A.BUYERS) / (SELECT COUNT(*) FROM BUYERS_ALL)) , 2) AS PUCHASED_RATIO
        
FROM BUYERS_2021 A

GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH

오늘은 GPT한테 문제를 받아서 몇 가지 풀어보자!

GPT한테 데이터분석가 실무에 가까운 SQL문제를 내달라고 했더니, 이렇게 체계적으로 뽑아주네요!!

Day7이야 이자식아

📌 문제 1: 리텐션 분석 (설계만 먼저)

문제:
회원 가입한 날로부터 7일 이내에 다시 로그인한 유저 수를 구하라.
비율도 함께 구하라 (재로그인한 유저 수 / 전체 가입자 수)

테이블:

• users(user_id, signup_date)
• logins(user_id, login_date)

조건 요약:

• 첫 가입일 기준으로 7일 이내 login_date가 존재해야 함
• 로그인은 중복 허용. 재로그인 여부만 체크

WITH first_signup AS (
    SELECT user_id, signup_date AS dt
    FROM users
    WHERE signup_date IS NOT NULL),
    
    first_login AS (
    SELECT user_id, MIN(login_date) AS dt
    FROM logins
    WHERE login_date IS NOT NULL)
    
SELECT ROUND((COUNT(*) / COUNT(DISTINCT A.user_id)),4) AS retention
FROM  first_signup A
LEFT JOIN first_login B ON A.user_id = B.user_id
WHERE (B.dt - A.dt) >= 7

ADHD 이슈로 인해 갑자기 프로그래머스 문제도 풀어보고 싶었다.

조회수가 가장 많은 중고거래~~ 문제를 풀어봅시다.

내 풀이:

-- 첨부 파일 경로를 조회하는 SQL
-- /home/grep/src/ + 게시글 ID + (파일 ID+파일네임+파일EXT)
-- 조건: 조회수 가장 높은 게시물
WITH MOST_VIEWS AS (
SELECT BOARD_ID, VIEWS
FROM USED_GOODS_BOARD
ORDER BY VIEWS DESC
LIMIT 1
)
SELECT CONCAT('/home/grep/src/', A.BOARD_ID,'/', B.FILE_ID,B.FILE_NAME,B.FILE_EXT) AS FILE_PATH
FROM MOST_VIEWS A
JOIN USED_GOODS_FILE B ON A.BOARD_ID = B.BOARD_ID
ORDER BY B.FILE_ID DESC

핵심: CONCAT() ★

다시 GPT 선생님의 문제를 풀어보자.

📌 문제 2: 퍼널 이탈 분석 ★★★★★ 

유저의 행동 흐름은 아래와 같이 구성됩니다.
각 단계에서 이탈이 발생한 유저 수와 전환율을 계산하세요.

퍼널 단계:

1단계: 'visit'
2단계: 'add_to_cart'
3단계: 'purchase'

🧾 테이블 구조

• 각 유저는 여러 이벤트를 가질 수 있음
• 동일 유저가 여러 번 purchase 해도 전환 여부만 판단
• 이벤트는 하루에 여러 번 있을 수도 있음

내 풀이:

WITH visit_out AS (
   SELECT DISTINCT user_id
   FROM event_log
   WHERE event_type = 'visit'), -- 1단계 유저
   
   add_to_cart_out AS (
   SELECT DISTINCT user_id
   FROM event_log
   WHERE event_type = 'add_to_cart'), -- 2단계 유저
   
   purchase_out AS (
   SELECT DISTINCT user_id
   FROM event_log
   WHERE event_type = 'purchase') -- 3단계 유저
   
-- 각 단계별 유저 수, 전환자 수, 이탈 수, 전환율 구하기

SELECT COUNT(A.user_id) AS '1->2단계 전체 유저 수',
       COUNT(B.user_id) AS '1->2단계 전환 유저 수',
       COUNT(A.user_id) - COUNT(B.user_id) AS '1->2단계 이탈 유저 수',
       COUNT(B.user_id) / COUNT(A.user_id) AS '1->2단계 전환율',
                
       COUNT(B.user_id) AS '2->3단계 전체 유저 수',
       COUNT(C.user_id) AS '2->3단계 전환자 수',
       COUNT(B.user_id) - COUNT(C.user_id) AS '2->3단계 이탈 유저 수',
       COUNT(C.user_id) / COUNT(B.user_id) AS '2->3단계 전환율',
                                
       COUNT(A.user_id) AS '1->3단계 전체 유저 수',
       COUNT(C.user_id) AS '1->3단계 전환 유저 수',
       COUNT(A.user_id) - COUNT(C.user_id) AS '3단계 이탈 유저 수',
       COUNT(C.user_id) / COUNT(A.user_id) AS '3단계 전환율'
       
FROM visit_out A
LEFT JOIN add_to_cart_out B ON A.user_id = B.user_id
LEFT JOIN purchase_out C ON B.user_id = C.user_id

꼭 다시 풀어보자!

이제 GPT한테 난이도를 올려달라고해봤슴다

친구들과 GPT를 쓰다보니 이런 부작용이 있네요.. 풀어봅시당

WITH first_login AS(
     SELECT DISTINCT user_id, signup_date
     FROM users
     ),
     
     re_login AS(
     SELECT user_id, MIN(login_date)
     FROM logins
     GROUP BY user_id
     )

SELECT COUNT(*)
FROM first_login A
JOIN re_login B ON A.user_id = B.user_id
WHERE (B.login_date - A.signup_date) >= 30

풀이:

WITH cart AS (
    SELECT user_id, event_type, MIN(event_time)
    FROM user_events
    WHERE event_type = '장바구니')
    GROUP BY user_id,
    
    purchase AS(
    SELECT user_id, event_type, MIN(event_time)
    FROM user_events
    WHERE event_type = '구매')
    GROUP BY user_id,
    
    user_out AS(
    SELECT DISTINCT user_id
    FROM cart A LEFT JOIN purchase B ON A.user_id = B.user_id
    WHERE TIMESTAMPDIFF(HOUR, B.event_time,A.event_time) > 24)
    
-- 월별(event_time 기준) 장바구니 유저 수, 이탈 유저 수, 이탈율을 구하라.

SELECT DATE_FORMAT(A.event_time,'%Y-%m') AS MONTH,
       COUNT(A.user_id) AS '장바구니 유저 수',
       COUNT(C.user_id) AS '이탈 유저 수',
       COUNT(C.user_id) / COUNT(A.user_id) AS '이탈율'
FROM cart A 
LEFT JOIN purchase B ON A.user_id = B.user_id
LEFT JOIN user_out C ON B.user_id = C.user_id
GROUP BY MONTH

🟦 문제 C: 앱 설치 후 리텐션 추적 ★★★

테이블

• app_installs(user_id, install_time)
• app_open(user_id, open_time)

요구사항

• 설치일 기준: +1일, +3일, +7일에 앱을 오픈한 유저 수 계산하고
• 각 리텐션 구간별 유저 수 출력해라

WITH install_time AS(
    SELECT user_id, MIN(install_time) AS time
    FROM app_installs
    GROUP BY user_id),
    
    open_days AS (
    SELECT user_id,
    	  CASE WHEN DATEDIFF(MIN(A.open_time), B.time) >= 1 THEN '1일 유저'
           WHEN DATEDIFF(MIN(A.time,MIN).B.open_time) >= 3 THEN '3일 유저'
           WHEN DATEDIFF(MIN(A.time,MIN),B.open_time) >= 7 THEN '7일 유저'
           END AS day_group
    FROM app_open A JOIN install_time B ON A.user_id = B.user_id)
    
SELECT B.day_group, COUNT(*) AS user_count
FROM install_time A 
LEFT JOIN open_days B ON A.user_id = B.user_id
GROUP BY A.user_id

오늘은 프로그래머스의 SQL 고득점 키트를 풀어봅시다.

1. JOIN -> 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

조건이 매우 많고 까다로워서 어려운 문제였다

조건 & 풀이:

-- 조건1: CAR_TYPE = SUV랑 세단일것
-- 조건2: 11월이 대여가능한 차 일것 -> 서브쿼리를 통해서 대여가능한 차들만 가져오기
-- 조건2: STARTDATE와 ENDATE + 아예 IS NULL인 경우 두가지 조건임 -> 11월에 시작&종료가 겹치는 CAR들만

               불러오고 (LEFT JOIN 후 ) WHERE에서 IS NULL하자 (여집합으로 접근)
-- 조건3: 할인조건을 따져서, 30일 빌릴때 대여요금을 계산할 것. (CAR_TYPE과 그에 맞는 할인율 적용)

-- 조건4: FEE의 가격이 500,000~2,000,000사이 일것

-- 조건1: CAR_TYPE = SUV랑 세단일것
-- 조건2: 11월이 대여가능한 차 일것 -> 서브쿼리를 통해서 대여가능한 차들만 가져오기
-- 조건2: STARTDATE와 ENDATE + 아예 IS NULL인 경우 두가지 조건임 -> LEFT JOIN 후
-- 조건3: 할인조건을 따져서, 30일 빌릴때 대여요금을 계산할 것. 

SELECT A.CAR_ID, A.CAR_TYPE,
       ROUND(A.DAILY_FEE * 30 * (1 - B.DISCOUNT_RATE / 100), 0) AS FEE
FROM CAR_RENTAL_COMPANY_CAR A
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B 
     ON A.CAR_TYPE = B.CAR_TYPE
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY C 
     ON A.CAR_ID = C.CAR_ID 
    AND C.START_DATE <= '2022-11-30'
    AND C.END_DATE >= '2022-11-01'
WHERE C.CAR_ID IS NULL
  AND A.CAR_TYPE IN ('SUV', '세단')
  AND B.DURATION_TYPE = '30일 이상'
  AND ROUND(A.DAILY_FEE * 30 * (1 - B.DISCOUNT_RATE / 100), 0) >= 500000
  AND ROUND(A.DAILY_FEE * 30 * (1 - B.DISCOUNT_RATE / 100), 0) < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;

꼭꼭 다시 풀자!! 기본기 문제임.

GPT가 내준 문제들 풀어보자.

🔁 문제 1 요약

1. -- “첫 로그인일로부터 7일 안에 재로그인한 유저 수 구하기”

-- user_event(user_id, event_type, event_date)
-- event_type = 'login'만 대상
-- first_login_date부터 7일 안에 **또 다른 'login'**이 있어야 카운트됨


WITH first_login AS(
    SELECT user_id, MIN(event_date) AS first_login_date
    FROM user_event
    WHERE event_type = 'login'
    GROUP BY user_id
    )
    ,
    second_login AS(
    SELECT user_id, event_date,
        RANK() OVER (PARTITION BY user_id ORDER BY event_date) AS rk
    FROM user_event
    WHERE event_type = 'login'
    ) 
SELECT COUNT(*) AS user_count
FROM first_login A
JOIN second_login B ON A.user_id = B.user_id

WHERE B.rk=2 AND (B.event_date - A.first_login_date) >= 7

🔁 문제 2 요약

✅ "첫 구매 후 30일 이내에 두 번째 구매한 유저 비율 구하기"

• 테이블: purchase(user_id, purchase_date)
• 분자: 30일 이내 두 번째 구매한 유저 수
• 분모: 전체 유저 수 (최소 1회 구매한 사람)
• 결과: 비율

WITH First_purchase AS(
    SELECT user_id, MIN(purchase_date) AS first_purchase_date
    FROM purchase
    ),
ranked_purchase_date AS(
    SELECT user_id, purchase_date,
        RANK() OVER (PARTITION BY user_id ORDER BY purchase_date ASC) AS rk
    FROM purchase)
    
SELECT COUNT(
    CASE WHEN (B.purchase_date - A.first_purchase_date) >= 30 THEN 1
    END)
    /
    COUNT(A.user_id)
    AS ratio
FROM First_purchase A
LEFT JOIN ranked_purchase_date B ON A.user_id = B.user_id
WHERE B.rk=2

3번째 문제

🎯 문제 3

"2022년 1월에 가입한 유저 중, 2월에 로그인한 유저의 비율을 구하라."

🔧 테이블 구조

• users(user_id, signup_date)
• logins(user_id, login_date)

✅ 풀이

WITH signup AS(
    SELECT user_id, signup_date,
    FROM users
    WHERE signup_date BETWEEN '2022-01-01' AND '2022-01-31'
    ,
login AS(
    SELECT user_id, login_date
    FROM logins
    WHERE login_date BETWEEN '2022-02-01' AND '2022-02-28'
    GROUP BY user_id)

SELECT ROUND(COUNT(B.user_id)/ COUNT(A.user_id),4)
FROM users A
JOIN logins B ON A.user_id = B.user_id

🔥 문제 4: A/B 테스트 전환율 비교

문제:
A/B 테스트에서 각각 그룹 A와 B에 속한 유저의 전환율을 계산하라.
전환은 'purchase' 이벤트를 수행한 경우로 정의한다.

📦 테이블 구조

• user_event(user_id, ab_group, event_type, event_date)
  • ab_group: 'A' 또는 'B'
  • event_type: 'visit', 'click', 'purchase'

✅ 요구사항

1. 각 그룹(A, B)별 방문자 수
2. 각 그룹(A, B)별 전환자 수 (event_type = 'purchase'인 유저 수)
3. 그룹별 전환율 = 전환자 수 / 방문자 수
4. 소수점 4자리까지 전환율 출력

WITH A_group AS (
    SELECT user_id,
    FROM user_event
    WHERE ab_group = 'A')
,
    B_group AS (
    SELECT user_id,
    FROM user_event
    WHERE ab_group = 'B')
-- A그룹 전환수 / A전체
-- B그룹 전환수 / B전체
SELECT (
    SELECT COUNT(*)
    FROM user_event
    WHERE ab_group ='A' AND event_type ='purchase')/ (SELECT COUNT(*) FROM A_group) AS A_ratio, 
    (
    SELECT COUNT(*)
    FROM user_event
    WHERE ab_group ='B' AND event_type ='purchase')/ (SELECT COUNT(*) FROM B_group) AS B_ratio