오늘은 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를 코드로 구현해보자.

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

 

# 힌트: 재귀 + 스택 활용해서 구현하기기
def dfs(graph, v, visited): # graph;연결리스트, v;노드, visited;방문한 노드들을 표시하는 리스트 
  
  visited[v] = True   # 현재 노드를 방문처리
  
  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 = [False] * 9    # visited = [False,False,False,...False]

'코테 스터디' 카테고리의 다른 글

Ch4. 구현  (1) 2025.07.11
Ch3. 그리디 알고리즘  (0) 2025.06.28

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

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

 

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

 

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)

'코테 스터디' 카테고리의 다른 글

Ch.05 DFS & BFS / 가장 큰 수(프로그래머스)  (0) 2025.07.25
Ch3. 그리디 알고리즘  (0) 2025.06.28

#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

'코테 스터디' 카테고리의 다른 글

Ch.05 DFS & BFS / 가장 큰 수(프로그래머스)  (0) 2025.07.25
Ch4. 구현  (1) 2025.07.11

오늘은 어려운 응용 문제 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명을 식별하는 것이 목표입니다.


📦 테이블 구조

users(user_id, signup_date, ab_group) -- ab_group: 'A' or 'B'
logins(user_id, login_date)
orders(order_id, user_id, order_date)
order_items(order_id, item_id)
items(item_id, category)

📌 요구사항

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

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

✅ 출력 예시

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

categorytotal_orders
의류 512
전자제품 421
식품 212
 

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

ab_group         user_id                                     login_count     order_count    conversion_rate                           rank

 

A u0123 4 3 0.75 1
A u0455 3 2 0.6667 2
A u0212 6 3 0.5 3
B u0345 2 2 1.0 1
B u0444 4 2 0.5 2
B u0111 6 3 0.5 2
 

단, 동점이면 같은 순위(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

'SQLD' 카테고리의 다른 글

SQL 8일차  (0) 2025.05.02
SQL 7일차  (0) 2025.05.01
SQL 6일차 #SQL 키트 + GPT 문제  (1) 2025.05.01
SQL 5일차 #LeetCodE  (0) 2025.04.27
SQL 3일차 #프로그래머스  (0) 2025.04.24

🟦 문제 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), 소수점 둘째 자리까지

✅ 출력 예시

monthtrial_usersconverted_usersconversion_rate
2022-07 123 91 0.74
2022-08 110 66 0.60
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

 

프로그래머스

SW개발자를 위한 평가, 교육, 채용까지 Total Solution을 제공하는 개발자 성장을 위한 베이스캠프

programmers.co.kr

 

첫 풀이

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

 

'SQLD' 카테고리의 다른 글

SQL 9일차 #응용 문제  (0) 2025.05.07
SQL 7일차  (0) 2025.05.01
SQL 6일차 #SQL 키트 + GPT 문제  (1) 2025.05.01
SQL 5일차 #LeetCodE  (0) 2025.04.27
SQL 3일차 #프로그래머스  (0) 2025.04.24

+ Recent posts