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

1.문제 정의!

2.문제 풀이

-- 1. 2021년에 가입한 전체 회원과 총 회원 수 컬럼으로 구하기
/*
SELECT DISTINCT USER_ID , (SELECT COUNT(DISTINCT USER_ID) FROM USER_INFO WHERE JOINED LIKE '%2021-%') AS SUM_USER
FROM USER_INFO
WHERE JOINED LIKE '%2021-%'
*/

-- 2. JOIN

SELECT YEAR(SALES_DATE) AS YEAR ,MONTH(SALES_DATE)  AS MONTH,
COUNT(DISTINCT A.USER_ID) AS PURCHASED_USERS,
ROUND(COUNT(DISTINCT A.USER_ID) /SUM_USER,1) AS PUCHASED_RATIO


FROM (SELECT DISTINCT USER_ID , (SELECT COUNT(DISTINCT USER_ID) AS SUM_USER FROM USER_INFO WHERE JOINED LIKE '%2021-%') AS SUM_USER
FROM USER_INFO
WHERE JOINED LIKE '%2021-%'
) A JOIN ONLINE_SALE B
ON A.USER_ID= B.USER_ID

GROUP BY  YEAR(SALES_DATE),MONTH(SALES_DATE) 

ORDER BY 1 ASC, 2 ASC;

주의할 점

• 상품 구매한 사람, 중복 처리 없이 출력되어야하므로 distinct 붙이기

멸종위기의 대장균 찾기

1.문제 정의

2.문제 풀이

-- 자식이 없는 경우? PARENT_ID에 값이 없는 경우
-- 1. JOIN을 활용해 ID 별 자식 개수를 확인하기

/*
SELECT B.ID,COUNT(A.ID) AS 'COUNT'
FROM
(SELECT ID, PARENT_ID
FROM ECOLI_DATA) A RIGHT  JOIN (SELECT ID, PARENT_ID
FROM ECOLI_DATA) B
ON A.PARENT_ID = B.ID
GROUP BY B.ID
*/
-- 2. 재귀 CTE을 활용해 세대 수 구하기
/*
WITH RECURSIVE CTE AS (
SELECT ID ,PARENT_ID ,1 AS LEVEL
FROM ECOLI_DATA
WHERE PARENT_ID IS NULL -- 시작점

UNION ALL

SELECT A.ID , A.PARENT_ID, B.LEVEL+1 AS LEVEL
FROM ECOLI_DATA A
INNER JOIN CTE B ON A.PARENT_ID = B.ID
)

SELECT ID,LEVEL FROM CTE
*/

-- 3.JOIN

WITH RECURSIVE CTE AS (
SELECT ID ,PARENT_ID ,1 AS LEVEL
FROM ECOLI_DATA
WHERE PARENT_ID IS NULL -- 시작점

UNION ALL

SELECT A.ID , A.PARENT_ID, B.LEVEL+1 AS LEVEL
FROM ECOLI_DATA A
INNER JOIN CTE B ON A.PARENT_ID = B.ID
)



SELECT COUNT(*) AS 'COUNT' ,LEVEL AS GENERATION
FROM (SELECT B.ID,COUNT(A.ID) AS 'COUNT'
FROM
(SELECT ID, PARENT_ID
FROM ECOLI_DATA) A RIGHT  JOIN (SELECT ID, PARENT_ID
FROM ECOLI_DATA) B
ON A.PARENT_ID = B.ID
GROUP BY B.ID) T1 JOIN (SELECT ID,LEVEL FROM CTE) T2
ON T1.ID= T2.ID
WHERE COUNT <1
GROUP BY LEVEL

주의할 점

• 재귀함수 이용해서 답안 작성해야한다. recursive cte 로 출력하기!
• (1)자식이 없는 경우 parent_id의 값을 구하고 (2) 동시에 개체 계층값을 출력해야한다.
• (1) join 필요 (2) recursive cte 필요