코딩테스트 연습JOIN상품을 구매한 회원 비율 구하기(level 5)

고군분투의sql기록.txt

0.01MB

가장 어려웠던 문제!!!!!

풀면 풀수록 헷갈렸던 문제!!!!

 

무조건 문제를 잘 읽자!!!!!

문제

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

 

 

 

핵심 헷갈리는 사항

 

1. left join 을 써야하나 inner join 을 써야하나

   - 정답은 inner join 

why :  2021년에 가입한 전체 회원들 중 상품을 구매한 회원수

즉, 2021년에 가입한 전체 회원을 모수로, 그들의 구매내역 테이블을 조회하는 것이기 때문에 inner join 으로 해야함!!!!

 

2. 날짜 함수로 년,월을 표현하는 문제는 자주 나오므로 기억하자!!!

   month(joined) as mon,

   date_format(joined, '%m') as mon

 

* joined 가 timestamp 형식이라도 date_format함수, month 함수는 모두 통함!!!

 

둘다 동일한 표현임

 

3. 비율 구할시 분모인 "전체 가입자 수" 는 고정 상수로 구하는 select 문으로 출력할것!!

단, where 조건에 반드시 '2021'년만 한정할것!!, 분모인 전체 select 문은 반드시 괄호로 묶어줄것!!!

 

 

문제를 잘 읽자!!!

2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요.

 

round(count(distinct c2.USER_ID ) /(select count(USER_ID) from USER_INFO) , 1) as 구매비율

-- 주의!!! 반드시 뒤에 select문은 괄호로 묶어줘야한다!!

 

4. 년도,월은 가입년월이 아닌 세일즈 데이터의 세일즈 년월임을 헷갈리지 말자!!

가입한회원모수 대상으로 그 고객의 세일즈 년월별로 집계 하는것임

문제가 좀더 명확했으면 좋겠다!!! 가입년월인지 세일즈 년월인지!!!!!

 

USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요

 

5. 당연히 online_sale 내역에 sale_amount가 0보다 큰 사용건인 회원만 있을 것이라 생각하지만 혹시 아닐수 있어

case when으로 따져볼 수 있다!!
결국은 동일함이 밝혀짐!!

요 세가지 수치는 모두 동일하다!!!!

 

    COUNT(DISTINCT  c2.USER_ID ) as 구매회원수,
    COUNT(DISTINCT CASE WHEN  c2.USER_ID IS NOT NULL THEN c2.USER_ID END) AS 구매회원수1,
    COUNT(DISTINCT CASE WHEN c2.SALES_AMOUNT>0  THEN c2.USER_ID END) AS 구매회원수2,

 

6. 구매 비율 구할시 백분율로 표기하라고 한적 없다!!!! 반드시 주의하자

-- 비율 % 로 구하는게 아니라 그냥 소수점이다!! 즉 * 100을 안해도됨!! 문제를 잘 읽자!!

 round(count(distinct c2.USER_ID )/(select count(USER_ID) from USER_INFO where year(JOINED)='2021') , 1) as 구매비율

 

* 정답!!!

yearmon구매회원수구매비율

2022	01	47	0.3
2022	02	40	0.3
2022	03	6	0.0

===> 이렇게 하면 틀림!!!

 

 round(count(distinct c2.USER_ID )*100 /(select count(USER_ID) from USER_INFO where year(JOINED)='2021') , 1) as 구매비율

* 오답!!!

year mon 구매비율
2022 01 29.7
2022 02 25.3
2022 03 3.8

 

7. round함수 표현시 소수점두번째 자리에서 반올림하라는 것은 결국 1번째까지 표현하라고 한것이므로

round(results, 1) 이 맞다!!!
       상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고

---

나의 풀이과정!! 험란했다!!!!

 

 

 

---

select date_format(c2.SALES_DATE, '%Y') as year, 
       date_format(c2.SALES_DATE, '%m') as mon , 
       count(distinct c2.USER_ID ) as 구매회원수,
       round(count(distinct c2.USER_ID )/(select count(USER_ID) from USER_INFO where year(JOINED)='2021') , 1) as 구매비율
       from USER_INFO as c1 inner join ONLINE_SALE as c2 on c1.USER_ID=c2.USER_ID
where  date_format(c1.JOINED, '%Y')='2021'
group by year , mon
order by year , mon

-- 주의!!! 반드시 뒤에 select문은 괄호로 묶어줘야한다!!
-- 주의!!! 분모인 전체 가입회원수는 특정년월인 2021만 가능하다!!! 또한 고정상수로 되어있다!!!
-- 비율 % 로 구하는게 아니라 그냥 소수점이다!! 즉 * 100을 안해도됨!! 문제를 잘 읽자!!
-- round 함수를 쓸때 소수점 두번째자리에서 반올림하라고 했으니,1 번째자리까지만 표현하라는 뜻!!!
       
-- 정답
/*
year mon 구매회원수 구매비율
2022 01 47 0.3
2022 02 40 0.3
2022 03 6 0.0
*/