프로그래머스 SQL 5단계 문제풀이

문제 : 상품을 구매한 회원 비율 구하기

 

문제 핵심

문제 잘 이해하기..

 

조건

USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

 

풀이흐름

1. CTE로 2021년에 가입한 USER_ID의 합 계산
CTE로 먼저 2021년에 가입한 USER_ID의 합을 계산해둔다
나중에 행 마다 연산하지 않기 위해서이다

2. 메인쿼리의 FROM
만들어둔 CTE와 ONLINE SALES 데이터를 불러온다

3. 메인쿼리의 WHERE
2021년에 가입한 USER_ID만 조회되도록 서브쿼리로 제한한다

4. 메인쿼리의 GROUP BY
ONLINE SALES 의 연, 월별로 구매한 USER_ID 의 수를 계산해야되기 때문에
SALES DATE 의 연,월을 기준으로 그룹화한다

5. 메인쿼리의 SELECT
그룹 기준 열인 연도를 기록한 열
그룹 기준 열인 월을 기록한 열
연,월 별 구매한 USER_ID 의 수를 기록한 열 (DISTINCT 를 사용해서 같은 USER ID가 중복집계 되지 않도록 한다)
연,월 별 구매한 USER ID의 수와 2021년에 가입한 USER ID의 합의 비율을 기록한 열 (아까 CTE 에서 계산해 둔 USER ID 합을 사용한다)

6. 메인쿼리의 ORDER BY
문제의 조건대로 연, 월 오름차순 정렬

WITH CTE AS (
    SELECT COUNT(*) AS COUNT_2021_ID
    FROM USER_INFO
    WHERE JOINED lIKE '2021%')

SELECT
    YEAR(SALES_DATE),
    MONTH(SALES_DATE),
    COUNT(DISTINCT USER_ID) AS PURCHASED_USERS,
    ROUND((COUNT(DISTINCT USER_ID) / COUNT_2021_ID), 1) AS Ratio
FROM
    ONLINE_SALE, CTE
WHERE USER_ID IN (
    SELECT USER_ID
    FROM USER_INFO
    WHERE JOINED lIKE '2021%')
GROUP BY
    YEAR(SALES_DATE), MONTH(SALES_DATE)
ORDER BY
    YEAR(SALES_DATE) ASC, MONTH(SALES_DATE) ASC;

 

 

 

문제 : 멸종 위기의 대장균 찾기

 

문제 핵심 :

JOIN에 대한 높은 이해 (가 필요할 것으로 보임)

 

조건

각 세대별 자식이 없는 개체의 수(COUNT)와

세대(GENERATION)를 출력하는 SQL문을 작성해주세요.

이때 결과는 세대에 대해 오름차순 정렬해주세요.

단, 모든 세대에는 자식이 없는 개체가 적어도 1개체는 존재합니다.

 

 

풀이 흐름

 

# 1.ID 별 세대를 기록한 CTE 생성
PARENT_ID가 NULL 인
즉, 초기 세대의 PARENT_ID, ID, 세대 열을 생성한 뒤에
재귀 CTE 기능을 사용해서 순차적으로 모든 ID 별 세대를 기록한
테이블을 생성한다

# 2.자녀가 있는 ID 구하기
문제에서는 세대 별 자녀가 없는 ID의 수를 구해야 하지만
자녀가 있는 ID를 구하여 추후 자녀 유무를 기록한 열을 구한 뒤에
세대 별 자녀가 없는 개체의 수를 구할 것이다
그래서 여기서는 앞서만든 재귀 CTE를 셀프 조인한 CTE2를 생성한다
ID와 PARENT_ID를 INNER JOIN 으로 연결하면
자녀가 있는 ID만 남길 수 있다

# 3.자녀 유무 열을 생성
CTE와 CTE2 를 기반으로 한 CTE3를 생성하고
CASE 문을 이용해 CTE2에 존재하는 ID 면 'WITH_CHILD'
이외엔 'NONE_CHILD' 로 범주화한 열을 생성한다

# 4.메인 쿼리에서 최종 집계
CTE3 에는 [PARENT_ID, ID, 세대, 자녀유무] 열을 준비해놓았기 때문에
여기서부터는 쉽게 문제를 해결할 수 있다
NONE_CHIID 인 데이터만 필터링해서 GENERATION 별 COUNT를 조회하면
세대별로 자식이 없는 대장균의 수가 조회된다

WITH RECURSIVE CTE AS (
    SELECT DISTINCT
        a.PARENT_ID, a.ID, 1 AS GENERATION
    FROM
        ECOLI_DATA a LEFT JOIN ECOLI_DATA b
        ON a.ID = b.PARENT_ID
    WHERE
        a.PARENT_ID IS NULL  
    
    UNION ALL
    
    SELECT
        c.ID AS PARENT_ID,
        d.ID AS ID,
        GENERATION + 1
    FROM
        CTE c 
        INNER JOIN ECOLI_DATA d
        ON c.ID = d.PARENT_ID),
        
        CTE2 AS (
        SELECT DISTINCT
            c1.ID AS WITH_CHILD_ID
        FROM
            CTE c1 INNER JOIN CTE c2 ON c1.ID = c2.PARENT_ID),
            
        CTE3 AS (
        SELECT 
            PARENT_ID, ID, GENERATION,
            CASE
                WHEN ID IN (SELECT WITH_CHILD_ID FROM CTE2) THEN 'WITH_CHILD'
                ELSE 'NONE_CHILD'
            END AS CHILD
        FROM CTE)
    
SELECT  COUNT(*) AS COUNT, GENERATION
FROM CTE3
WHERE CHILD = 'NONE_CHILD'
GROUP BY GENERATION, CHILD