스파르타코딩 데이터분석 04-24

[2기] 데이터와 친해지는 SQL 3회차 과제

 

문제1*

case when 구문과 join 함수를 사용하여, users 테이블을 기준으로, 결제를 한 유저와 결제를 하지 않은 유저를 추출해주세요.결과값은 아래와 같아야 합니다.

select case when p.game_account_id is null then '결제안함'
			else '결제함'
			end gb,
			count(distinct u.game_account_id) usercnt 
from users u
left join payment P on u.game_account_id = p.game_account_id
group by 1

 

CASE WHEN구문으로 NULL값인지 아닌지를 판단하여 결제여부 확인

한 명의 유저가 결제를 여러번 할 수도 있기 때문에 전체 유저 데이터가 들어있는 USER 테이블에서 중복처리한 유저 수 계산

첫번째 컬럼을 기준으로 GROUP BY

---

• **문제2**
  • 서버번호가 2 이상인 데이터와 결제방식이 CARD 인 경우를 join해 주시고
  • game_account_id 를 기준으로 game_actor_id 갯수를 중복값없이 세어주시고, actorcnt 으로 컬럼명을 명시해주세요.
  • pay_amount 값을 더해주시고, sumamount 으로 컬럼명을 명시해주세요.
  • having 을 사용하지 않고, subquery 사용으로 game_actor_id 갯수가 2 이상인 경우만 추출해주세요. 결과값은 아래와 같아야 합니다. (전체결과 중 일부입니다.)

select *
from (select u.game_account_id, 
	   		 count(distinct u.game_actor_id) actorcnt,
	   		 sum(distinct p.pay_amount) sumamount 
	  from (select *
	  		from users
	  		where serverno  >= 2
	  		) u
	  join (select *
	  		from payment
	  		where pay_type = 'CARD'
	  		) p
	  on u.game_account_id = p.game_account_id
	  group by u.game_account_id) c
where c.actorcnt >= 2

각각의 테이블에 조건문을 붙이고 서로 조인

계정 아이디 별로 게임 캐릭터 아이디 수와 결제금액 합산 출력

로그에 따라 기록된 테이블이 때문에 하나의 캐릭터가 중복되어 세질 수 있다. 따라서 distinct 처리

결제 금액을 합산할 때는 각각의 game_account_id에 해당하는 결제 금액을 중복으로 더하지 않기 위해 중복을 제거

---

**문제3**

• user 테이블에서 game_account_id, date, serverno 를 추출한 데이터와 매출 테이블에서 game_account_id 별 가장 마지막 결제일자를 찾고 join 을 진행해주세요.
• 그 다음, datediff 함수를 사용해 결제일자-접속일자를 구해주세요. 그리고 컬럼이름을 diffdate로 설정해주세요. 두 날짜의 형식은 같아야 합니다.
• 마지막으로, 인라인 뷰 subquery 를 이용하여 서버별 평균 diffdate를 구해주세요. 다만, 평균 datediff 컬럼은 정수 형태로 출력되어야 합니다. 또한, 조건절에 diffdate 값이 10일 이상인 경우를 필터링해주세요. 그리고 서버번호를 기준으로 내림차순 정렬해주세요. (전체결과 중 일부입니다.)

select c.serverno, round(avg(c.datediff))  
from(select p.payday, u.date, datediff(p.payday, u.date) datediff, u.serverno
	 from (select game_account_id , `date` , serverno 
		   from users
		   ) u 
join(select game_account_id, substr(max(approved_at),1,10) payday
 	 from payment
 	 group by game_account_id
 ) p
on u.game_account_id = p.game_account_id
) c
where c.datediff >= 10
group by c.serverno
order by c.serverno desc

가장 최근의 결제일자를 찾기 위해선 max사용

서버번호를 출력하기 위해 인라인 서브쿼리의 select문에 serverno 출력