인공재환

오늘은 어려운 응용 문제 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

프로그래머스 SQL문제를 다 풀었으니 이제 리트코드에서 풀어보자!

난이도는 MED으로 설정하고,

확실히 문제가 프로그래머스와 확연히 다르다.

SQL은 뇌빼고 풀어도 다 풀 수 있는데 여기 문제는 코드짜기 전 어떻게 풀어야하는지 깊게 생각해야하는것같다. (나만 그럴수도)

복습할 문제

1. 3번 연속하는 등장하는 숫자 찾기 (180번)

Find all numbers that appear at least three times consecutively.

Return the result table in any order.

The result format is in the following example.

-- num이 같은 숫자로 연속 3번이상 등장할 경우 COUNT
-- n번째 id의 num과, n+1.., n+2의..num이 모두 같을 경우 COUNT -> WHERE?

-- 힌트: 조인 사용

SELECT DISTINCT A.num AS ConsecutiveNums
FROM Logs A
JOIN Logs B
  ON B.id = A.id + 1
JOIN Logs C
  ON C.id = A.id + 2
WHERE A.num = B.num and B.num = C.num

# 어떠한 숫자가 3번 이상 등장하는지 -> JOIN 2번, DISTINCT 사용

2. 각 부서 별로 최고의 연봉 갖는 사람의 이름 추출 (184번)

오답)

    -- 부서별 MAX연봉 직원 찾기 ,any order

    SELECT B.name AS Department, A.name AS Employee, MAX(A.salary) AS Salary

    FROM Employee A
    JOIN Department B
    ON A.departmentId  = B.id -- 직원이름이 어떠한 부서 아래 있는지 참조

    GROUP BY B.name

문제 : 추출되는 A.name이 최고 연봉을 가지는 사람이 아님. (랜덤임) 

만약 문제가 각 부서 별 최고 연봉금액이면 맞지만, 연봉이 얼마인지 + 그사람의 이름을 가져오려면 서브쿼리 써야함.

    # 부서별 MAX연봉 직원 찾기 ,any order
    
    SELECT B.name AS Department, 
           A.name AS Employee, 
           MAX(A.salary) AS Salary
    
    FROM Employee A
    JOIN Department B
      ON A.DepartmentId = B.id
      
   WHERE (A.departmentId, A.salary) IN -- 두개 이상을 비교하는 것이기 때문에 괄호써줘야함.
   	 	 (
         SELECT departmentId, MAX(salary) A.departmentId아님 -> 서브쿼리라서 테이블 내 컬럼명 그대로
         FROM Employee
		 GROUP BY departmentId
         )

서브쿼리를 쓴 이유 - 부서별로 가장 높은 salary를 미리 계산해서 메인쿼리와 비교해야 하기 때문입니다.

IN을 쓴 이유 -  (departmentId, max salary) 조합 중 하나에 해당하는 직원들을 찾기 때문입니다.

정확히 숙지하고 가자!

오늘은 프로그래머스 내 SQL 남은 문제 모두를 풀어보도록 하자!

약 60문제 정도 남은것같다ㅠ

새로운 개념

1. 문자열 이어 붙이기 -> CONCAT () -> 주의: ROUND/AVG 등 함수 내부가 아닌 제일 밖에 사용!

예시)

SELECT ROUTE, 
       CONCAT(ROUND(SUM(D_BETWEEN_DIST),2), 'km') AS TOTAL_DISTANCE,
       CONCAT(ROUND(AVG(D_BETWEEN_DIST),3), 'km') AS AVERAGE_DISTANCE
FROM SUBWAY_DISTANCE

문제 풀이 인증

이제 프로그래머스는 다 풀었으니

리트코드로 가보자

오늘은 50문제정도 풀어보자. 정답률 가장 높은 순대로!!

작성 순서때문에 틀린 것도 많아서 정리해놔야겠다ㅠ

문제 풀면서 처음 보는 개념/문법들 정리

1. LIMIT -> df.head()와 같음!

2. LIKE -> 문자열 부분일치 (WHERE뒤에 붙으면서, % / _ 등 기호와 함께사용)

3. 결측치 처리 ->  COALESCE(column, 'NA') , INFULL(column, 'NA') NULL이라면 'NA'로 채움 -> IFNULL도 가능!

4. LEFT(XXX, 2) -> 문자열 왼쪽에서 2글자 추출

5. DATE_FORMAT(DATE,%Y,-%m-%d)

6. 중복 제거 : DISTINCT ------------> 집계 함수 쓴다면 COUNT(DISTINCT NAME) 요렇게 가능

7. DATEDIFF(A.DATE, B.DATE)

서브쿼리

SELECT * FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT);

오늘푼 문제 인증

40문제?? 푼것같다.. 50문제는 너무 많아...

질답은 아래와 같다.

Q: 자 CTE 들어가기전, 나는 정말 자주 나오고, 리트코드 + 회사 입사 코딩테스트 (SQL 데이터분석)에 맞게 문제를 풀어보고 싶어. 도와줄래?

A:

문제 1: 부서별 평균보다 높은 연봉 가진 직원 찾기  (난이도:⭐)

각 부서에서, 해당 부서의 평균 연봉보다 높은 연봉을 받는 직원의 이름과 연봉을 출력하라.

• 테이블: employees(department, name, salary)

# 각 부서에서, 해당 부서의 평균 연봉보다 높은 연봉을 받는 직원의 이름과 연봉을 출력하라.
# 테이블: employees(department, name, salary)

SELECT name,salary,department FROM employees e
WHERE salary > 
	(SELECT AVG(salary)
    FROM employees
	WHERE department = e.department);


# 핵심 -> 부서 평균 연봉을 구하고, 그걸 직원 연봉과 비교해야함
# (상관)서브쿼리 써야함

🔥 문제 2: 매니저가 3명 이상 직원 관리하는 경우만 출력 (난이도:⭐⭐)

직원 수가 3명 이상인 매니저의 이름과 직원 수를 출력하라.

• 테이블: employees(employeeId, name, managerId)

# 문제 2: 매니저가 3명 이상 직원 관리하는 경우만 출력 (난이도:⭐⭐)
# 직원 수가 3명 이상인 매니저의 이름과 직원 수를 출력하라.
# 테이블: employees(employeeId, name, managerId)

SELECT m.name AS manger_name, COUNT(e.employeeId) AS ct
FROM employees e
INNER JOIN employees m
		ON e.managerId = m.employeeId
GROUP BY m.name
HAVING COUNT(e.employeeId) >= 3;

# 1.JOIN + CASE WHEN 해서 직원 담당 매니저 변수만들기
# 2.COUNT활용해서 COUNT > 3인 매니저 이름과 직원 수 출력하기 -> GROUPBY + HAVING으로!

처음엔 CASE WHEN을 썼더니 반응이,..

그냥 욕을 해.

이걸 알아야 풀 수 있다고.!

🔥 문제 3: 이전 연도보다 연봉이 오른 직원만 출력 (난이도:⭐⭐⭐)

같은 직원이 여러 해에 걸쳐 기록된 salaries 테이블에서,
이전 연도보다 연봉이 증가한 경우만 employeeId, year, salary를 출력하라.

• 테이블: salaries(employeeId, year, salary)

(Hint: self join, 또는 나중에 윈도우 함수로 리팩터링 가능)

🔥 문제 4: 부서별 최고 연봉자

(난이도:⭐⭐)

각 부서마다 최고 연봉을 받는 직원의 이름과 연봉을 출력하라.

• 테이블: employees(department, name, salary)

🔥 문제 5: 상사보다 연봉 높은 직원 찾기

(난이도:⭐⭐⭐)

자신보다 연봉이 낮은 상사를 둔 직원을 출력하라.

• 테이블: employees(employeeId, name, salary, managerId)

들어가기전, 먼데이 단장님께서 오프닝 멘트 시작하시겠다. 다들 집중

드가자!!!!!!!!!

 오늘은 Self Join과 CASE WHEN,  EXISTS를 배워보도록 하자.

예시

SELF JOIN부터 알아보자. JOIN을 알다시피 테이블을 하나로 합치는 것

SELF JOIN이란 자기 자신을 자기에게 붙이는것..! 

ex) customer 테이블의 데이터를 다시 customer 테이블에 붙인다! 

WHY??? 이게 왜 필요한지 물어봤다.

오늘도 창의적으로 포문을 여는 먼선생.!

SELECT 
	e.name AS employee_name, m.name AS manager_name
    FROM employees e
    JOIN employees m
    ON e.mangers_id = m.id;

먼선생의 유머 수준을 60%로 낮춰야겠다.

재밌는 교수는 없다는 소리가 괜히 나오는게 아니다.

바로 실전 문제 가보자!

문제 1.

직원과 매니저 이름을 같이 출력하라. (Self Join 구조 익히기)

Employee(employeeId, name, salary, managerId)
→ managerId가 employeeId를 참조하고 있음

# 직원과 매니저 이름을 같이 출력하라. (Self Join 구조 익히기)

# Employee(employeeId, name, salary, managerId)
# → managerId가 employeeId를 참조하고 있음

SELECT e.name, m.name
	FROM employees e
	JOIN employees m
    ON e.employeeId = m.managerId

괴라는 나물..SQL 어렵다..

e.employeeId = m.mangerId가 아니라 e.managerId = m.employeeId! (직원 -> 매니저 찾아야하므로)

문제 2.

각 직원이 자기 매니저보다 연봉이 높으면 ‘Higher’, 낮으면 ‘Lower’라고 표시하라.

컬럼: employee_name, manager_name, 비교결과

# 각 직원이 자기 매니저보다 연봉이 높으면 ‘Higher’, 낮으면 ‘Lower’라고 표시하라.
# 컬럼: employee_name, manager_name, 비교결과

SELECT e.name AS employee_name, m.name AS manager_name, e.salary,
CASE 
	WHEN e.salary > m.salary THEN 'Higher'
    ELSE 'Lower'
    END AS '비교결과'
FROM employees e
JOIN employees m
ON e.managerId= m.employeeId;

 

문제 3.

매니저가 없는 직원은 'No Manager'로 표기하라.

CASE WHEN을 IS NULL 조건과 함께 써봐

참고) 테이블은 아래와 같음.

# 매니저가 없는 직원은 ‘No Manager’로 표시
# employeeId/managerId/name/salary/department

SELECT e.name AS employee_name,
CASE
	WHEN m.name IS NULL THEN 'No Manager'
    ELSE m.name
    END AS "매니저"
FROM employees e
JOIN employees m
ON e.managerId = m.employeeId;

문제 4.

직원이 자기 매니저보다 1.1배 이상 높은 연봉이면 ‘Fast Climber’,
동일하면 ‘Peer’, 아니면 ‘Underpaid’ 라고 표시

# 직원이 자기 매니저보다 1.1배 이상 높은 연봉이면 ‘Fast Climber’,
# 동일하면 ‘Peer’, 아니면 ‘Underpaid’ 라고 표시
# columns - 'employeeId/name/salary/managerId'

SELECT name,
CASE 
	WHEN e.salary > ( 1.1 * m.salary) THEN "Fast Climber"
    WHEN e.salary = m.salary THEN "Peer"
    ELSE "Underpaid"
	END AS "연봉 상황"
FROM employees e
INNER JOIN employees m
ON e.managerId = m.employeeId;

궁금한 점

어떻게 e.salary m.salary를 각각 직원 연봉과 매니저 연봉으로 구분할 수 있는가?

-> 먼교수님 말씀... 

요새 GPT는 노트북 카메라로 얼굴 보면서 대답하나보다. 괴물처럼 생겨서 화는 나지 않는다.

결국, INNER JOIN , ON -> 부터 관계가 생기면서 (두 테이블 연결하면서) 인지함.

문제 5.

매니저 이름이 ‘Kim’인 사람만 필터해서, 그들과 연봉 비교 결과 출력하기

예: Kim보다 높으면 'Surpassed Kim', 낮으면 'Still Climbing' 등

# 매니저 이름이 ‘Kim’인 사람만 필터해서, 그들과 연봉 비교 결과 출력하기
# 예: Kim보다 높으면 'Surpassed Kim', 낮으면 'Still Climbing' 등
# table = 'employeeId','name','salary','managerId'

SELECT e.name AS employee_name,
	   e.salary AS employee_salary,
       m.name AS manager_name,
       m.salary AS manager_salary,
CASE
	WHEN e.salary > m.salary THEN "Surpassed Kim"
    ELSE 'Still Climbing'
	END AS "비교 결과"
FROM employees e
INNER JOIN employees m
ON e.managerId = m.employeeId
WHERE m.name = "Kim";

문제 6.

부서가 같은 직원들 중 매니저보다 연봉이 낮은 사람만 출력,
그리고 'Raise Suggested' 라는 컬럼 추가

# 부서가 같은 직원들 중 매니저보다 연봉이 낮은 사람만 출력하고 그 사람들에게 "Raise Suggested"라는 데이터 채우기, column명은 "평과 결과"

SELECT e.name AS employee_name,
	   e.salary AS employee_salary,
       m.name AS manager_name,
       m.salary AS manager_salary,
       CASE
       WHEN e.salary < m.salary THEN "Raise Suggested"
       END AS "평가 결과"
FROM employees e
INNER JOIN employees m
ON e.managerId = m.employeeId
WHERE (e.salary < m.salary) and (e.department = m.department);

문제 7.

각 매니저별로, 자기보다 연봉 높은 직원을 몇 명이나 두고 있는지 출력하라

Self Join + GROUP BY + COUNT + CASE WHEN 조합해야 함

# 각 매니저별로, 자기보다 연봉 높은 직원을 몇 명이나 두고 있는지 출력하라
# Self Join + GROUP BY + COUNT + CASE WHEN 조합해야 함

SELECT m.name AS manager_name,
	COUNT(CASE WHEN m.salary < e.salary THEN 1 END) AS "better than me"
FROM employees e
INNER JOIN employees m
		ON e.managerId = m.employeeId
GROUP BY m.name;

문제 8.

직원이 속한 부서에 ‘김 매니저’가 있으면 ‘Under Kim’,
없으면 ‘Other Manager’ 라고 표시하라.

Self Join + CASE WHEN + 부서 조건 조합

# 직원이 속한 부서에 ‘김 매니저’가 있으면 ‘Under Kim’, 없으면 ‘Other Manager’ 라고 표시하라.
# table = 'employeeId','name','salary','managerId','department'

SELECT department, e.name AS employee_name,
	CASE
    WHEN m.name = "Kim" THEN "Under Kim"
	ELSE "Other Manager"
    END AS "김 매니저 유무"
FROM employees e
INNER JOIN employees m
	    ON e.managerId = m.employeeId
WHERE e.department = m.department

문제 9.

각 직원이 '매니저와 연봉 차이'가 20% 이상이면 Alert,
아니면 Normal, 매니저 없으면 Unknown 출력

# 각 직원이 '매니저와 연봉 차이'가 20% 이상이면 Alert,
# 아니면 Normal, 매니저 없으면 Unknown 출력

SELECT e.name AS employee_name,
	   e.salary AS employee_salary,
       m.name AS manager_name,
	   m.salary AS manager_salary,
CASE
	WHEN e.managerId IS NULL THEN "Unknown"
    WHEN ABS(e.salary - m.salary) > (e.salary) * 0.8  THEN "Alert"
    ELSE "Normal"
    END AS "매니저 연봉 차이"
FROM employees e
LEFT JOIN employees m
		ON e.managerId = m.employeeId;

개빡세다 1일차는 여기까지...