SQL 6일차 #SQL 키트 + GPT 문제

오늘은 프로그래머스의 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