내일배움캠프 mysql 5회차 숙제 복습!

#5회차 숙제에서 마냥 어렵다고 생각했던 마지막 문제를 오래걸렸지만 한번에 풀었다!

 

 

문제1 JOIN 활용

조건1) 알맞은 join 방식을 사용하여 users 테이블을 기준으로, payment 테이블을 조인해주세요.

조건2) case when 구문을 사용하여 결제를 한 유저와 결제를 하지 않은 게임계정을 구분해주시고, 컬럼이름을 gb로 지정해주세요. 조건3) gb를 기준으로 게임계정수를 추출해주세요. 컬럼 이름은 usercnt로 지정해주시고, 결과값은 아래와 같아야 합니다.

 

정답 쿼리!

select case when p.pay_amount is null then '결제안함'

           else '결제함' end gb,

           count(distinct u.game_account_id) usercnt

from basic.users u left join basic.payment p on u.game_account_id=p.game_account_id

group by 1

order by 1

 

 

#처음에 헷갈렸던 부분

게임 계정수를 중복값 없지 추출해서 숫자가 훨씬 많았음.

결제를 한 유저와 아닌 유저를 구분하는것이기 때문에 계정수를 전체 다 세는것이 아닌 중복값은 제외해야함!

---

 

 

문제2 JOIN 응용

조건1) users 테이블에서 서버번호가 2 이상인 데이터와 payment 테이블에서 결제방식이 CARD 모두를 만족하는 경우를 알맞은 방식으로 join 해 주세요.

조건2) 조인한 결과를 바탕으로 users 테이블의 game_account_id 를 기준으로 game_actor_id수를 중복값없이 세고 컬럼 이름을 actor_cnt로 지정해주세요. 또한 pay_amount 값을 더해주시고, 컬럼 이름을 sumamount로 지정해주세요.

조건3) having 을 사용하지 않고, 인라인 뷰 subquery 사용으로 actor_cnt수가 2 이상인 경우만 추출해주세요. 그리고 sumamount를 기준으로 내림차순 정렬해주세요. 결과값은 아래와 같아야 합니다. 전체결과 중 일부입니다.

 

정답 쿼리!

select *

from

(

  select u.game_account_id,

            count(distinct game_actor_id) actor_cnt,

            sum(p.pay_amount) sumamount

  from basic.users u inner join basic.payment p on u.game_account_id=p.game_account_id

  where u.serverno >= 2 and

             p.pay_type = 'CARD'

  group by 1

) a

where actor_cnt >= 2

order by sumamount desc

 

 

#처음에 ,, 그냥 길어서 조금 겁 먹었던것 같지만 차근차근 풀어보니 답이 나왔다!

 

---

 

 

문제3 JOIN 응용2

조건1) user 테이블에서 game_account_id, first_login_date, serverno 를 추출한 결과와

조건2) payment 테이블에서 game_account_id 별 가장 마지막 결제일자를 찾고 그 컬럼이름을 date2로 지정해주세요. 그 다음 inner join 을 진행해주세요. 다만, 첫 접속일자보다 마지막 결제일자가 큰 경우만 추출해주세요.

조건3) 조인 결과를 바탕으로 마지막 결제일자-첫 접속일자 를 구해주세요. 그리고 컬럼이름을 diffdate로 설정해주세요. 두 날짜의 형식은 같아야 합니다.

조건4) 인라인 뷰 subquery 를 이용하여 서버별 평균 diffdate를 구해주시고, 컬럼이름을avgdiffdate로 설정해주세요. 해당컬럼은 정수 형태로 출력되어야 합니다.

조건5) 조건절에 diffdate 값이 10일 이상인 경우를 필터링해주세요. 그리고 서버번호를 기준으로 내림차순 정렬해주세요. 결과값은 아래와 같아야 합니다. 전체결과 중 일부입니다.

 

정답 쿼리!

select serverno, round(avg(diffdate),0) avgdiffdate

from

(

  select u.game_account_id, u.first_login_date, u.serverno, datediff(date2,u.first_login_date) diffdate

  from

  (

    select game_account_id, first_login_date, serverno

    from basic.users

   ) u

    inner join

  (

    select game_account_id, max(date_format(approved_at,'%Y-%m-%d')) date2

    from basic.payment

    group by 1

  ) p

    on u.game_account_id = p.game_account_id

    where first_login_date < date2

) a

where diffdate >=10

group by 1

order by 1 desc

 

#다시 복습하며 풀어보니 한번에 성공했다!

정답은 문제에 있다고, 문제를 차근차근 읽어보면서 진행하니 시간은 오래걸렸지만 깔끔하게 한번에 답을 맞출 수 있었다.

사실 첫번째 프로젝트를 끝내고 주말에 너무 쉬고싶어서 숙제에 집중하지 못했고, 이 문제는 빈칸으로 제출했다.. 반성합니다 ㅜㅜ