[MySQL] SQL 라이브 섹션 과제 서브쿼리와 JOIN 마무리

정말 긴 여정이였다.

오늘은 SQL 라이브 섹션 마지막 문제, 서브쿼리를 이용한 JOIN의 꽃!

 

진짜..여기 아래 나열된 문제들을 푸는데 걸린시간은 다 해서 4시간 정도 걸린거 같다.

 

일단 문제와 답안 쿼리를 나열하겠다.. 쿼리만 봐도 데이터가 눈에 훤하다.

 

[데이터 타입]

### 문제 1

#조건1) 서버별, 월별 게임계정id 수를 중복값 없이 추출해주세요. 월은 첫 접속일자를 기준으로 계산해주세요. 월은 yyyy-mm의 형태로 추출해주세요.

#힌트: 월을 추출하는 방법→날짜는 string(문자열) 형식으로 저장되어 있으므로, 문자열을 자르는 함수를 사용해주시면 좋겠죠?

SELECT serverno, substr(first_login_date, 1, 7) AS m, count(DISTINCT(game_account_id)) AS user_cnt
FROM users
GROUP BY serverno, m
ORDER BY serverno, m;

 

### 문제 2

#조건1) group by 를 활용하여 첫 접속일자별 게임캐릭터수를 중복값 없이 구하고,

#조건2) having 절을 사용하여 그 값이 10개를 초과하는 경우의 첫 접속일자 및 게임캐릭터id 개수를 추출해주세요.

SELECT first_login_date, count(DISTINCT(game_actor_id)) AS actor_cnt
FROM users
GROUP BY 1
HAVING actor_cnt > 10;

 

### 문제 3

#조건1) group by 절을 사용하여 서버별, 유저구분(기존/신규) 게임캐릭터id수를 구해주세요. 중복값을 허용하지 않는 고유한 갯수로 추출해주세요.

#조건2) 기존/신규 기준→ 첫 접속일자가 2024-01-01 보다 작으면(미만) 기존유저, 그렇지 않은 경우 신규유저

#조건3) 또한, 서버별 평균레벨을 함께 추출해주세요.

SELECT serverno,
	CASE WHEN first_login_date < '2024-01-01' THEN 'OLDUSER'
	ELSE 'NEWBEGINER' END AS gb,
	count(DISTINCT(game_actor_id)) AS actor_cnt,
	avg(`level`) AS avg_level
FROM users
GROUP BY serverno, gb;

 

### 문제 4

#조건1) 문제2번을 having 이 아닌 인라인 뷰 서브쿼리를 사용하여 추출해주세요.

#힌트: 인라인 뷰 서브쿼리는 from 절 뒤에 위치하여, 마치 하나의 테이블 같은 역할을 했었습니다!

SELECT *
FROM (SELECT first_login_date, count(DISTINCT(u.game_actor_id)) AS actor_cnt
	FROM users u
	GROUP BY first_login_date
	) AS a
WHERE actor_cnt > 10
GROUP BY 1;

 

### 문제 5

#조건1) 레벨이 30 이상인 캐릭터를 기준으로, 게임계정 별 캐릭터 수를 중복값 없이 추출해주세요.

#조건2) having 구문을 사용하여 캐릭터 수가 2 이상인 게임계정만 추출해주세요.

#조건3) 인라인 뷰 서브쿼리를 활용하여 캐릭터 수 별 게임계정 개수를 중복값 없이 추출해주세요.

SELECT actor_cnt, count(*) AS accnt
FROM (SELECT game_account_id, count(DISTINCT(game_actor_id)) AS actor_cnt
	FROM users
	WHERE `level` >= 30
	GROUP BY game_account_id
	HAVING actor_cnt >= 2) AS a
GROUP BY actor_cnt
ORDER BY actor_cnt;

 

## 문제 6

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

#조건2) case when 구문을 사용하여 결제를 한 유저와 결제를 하지 않은 게임계정을 구분해주시고, 컬럼이름을 gb로 지정해주세요.

#조건3) gb를 기준으로 게임계정수를 추출해주세요. 컬럼 이름은 usercnt로 지정해주시고, 결과값은 아래와 같아야 합니다.

#힌트: 기준이 되는 테이블의 데이터는 그대로 두어야겠죠?

# 질문 : 인라인뷰에서 u.game_account_id말고 p.game_account_id로 할때 왜 결제안함이 출력이 안되는지

[내 답안]

SELECT CASE WHEN joined.pay_amount IS NULL THEN '결제안함'
	ELSE '결제함' END AS gb,
	count(DISTINCT(joined.game_account_id)) AS usercnt
FROM (SELECT p.pay_amount, u.game_account_id
	FROM users u LEFT JOIN payment p
	ON u.game_account_id = p.game_account_id) AS joined
GROUP BY gb;

[모범답안]

SELECT CASE WHEN joined.pay_amount IS NULL THEN '결제안함'
	ELSE '결제함' END AS gb,
	count(DISTINCT(joined.game_account_id)) AS usercnt
FROM (SELECT p.pay_amount, u.game_account_id
		FROM users
     ) AS u
LEFT JOIN
	 (SELECT game_account_id
     	FROM payment
     ) AS p
	ON u.game_account_id = p.game_account_id
GROUP BY gb;

 

## 문제 7-1

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

# payment 테이블의 매출 금액이 중복되는 것을 방지하기 위해 모든 값을 고유하게 추출해야 합니다.

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

# 또한 pay_amount 값을 더해주시고, 컬럼 이름을 sumamount로 지정해주세요.

#조건3) having 을 사용하지 않고, 인라인 뷰 subquery 사용으로 actor_cnt수가 2 이상인 경우만 추출해주세요.

# 그리고 sumamount를 기준으로 내림차순 정렬해주세요.

# 결과값은 아래와 같아야 합니다. 전체결과 중 일부입니다.

SELECT game_account_id, actor_cnt, sumamount
FROM (SELECT u.game_account_id, sum(p.pay_amount) AS sumamount, count(DISTINCT(u.game_actor_id)) AS actor_cnt
	FROM (SELECT game_account_id, game_actor_id
		FROM users WHERE serverno >= 2) AS u
	INNER JOIN
		(SELECT game_account_id, pay_amount
		FROM payment WHERE pay_type = 'CARD') AS p
	ON u.game_account_id = p.game_account_id GROUP BY u.game_account_id) AS a
WHERE actor_cnt >= 2
ORDER BY sumamount DESC;

 

[모범답안]

select *
from(	select a.game_account_id, count(distinct game_actor_id) as actor_cnt, sum(pay_amount)as sumamount 
			from(	select game_account_id, game_actor_id 
					from basic.users 
					where serverno>=2
				)as a 
			inner join 
				(	select distinct game_account_id, pay_amount, approved_at
					from basic.payment
					where pay_type='CARD'
				)as b 
			on a.game_account_id=b.game_account_id 
			group by a.game_account_id
	)as a 
where actor_cnt>=2
order by sumamount desc

## 문제 7-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) 조건절에 avgdiffdate 값이 10일 이상인 경우를 필터링해주세요.

# 그리고 서버번호를 기준으로 내림차순 정렬해주세요.

# 결과값은 아래와 같아야 합니다. 전체결과 중 일부입니다.

#힌트) 소수점을 반올림해주는 round 함수를 활용해주세요!

SELECT j2.serverno, round(avg(diffdate),0) AS avgdiffdate
FROM (SELECT *, datediff(date_format(date2, '%Y-%m-%d'), date_format(first_login_date,'%Y-%m-%d')) AS diffdate
	 FROM (SELECT u.game_account_id, first_login_date, serverno, date2
		 FROM (SELECT game_account_id, first_login_date, serverno
		       FROM users) AS u
		 INNER JOIN
			 (SELECT game_account_id, max(approved_at) AS date2
			  FROM payment GROUP BY game_account_id) AS p
		  ON u.game_account_id = p.game_account_id WHERE u.first_login_date < date2) AS j1) AS j2
WHERE diffdate >= 10
GROUP BY serverno
ORDER BY 1 DESC;

 

[모범답안]

select serverno, round(avg(diffdate),0)as avgdiffdate
from(	select a.game_account_id, datediff(date_format(date2,('%Y-%m-%d')) ,first_login_date) as diffdate,serverno
			from(	  select game_account_id, first_login_date, serverno
					    from basic.users 
				   )as a
			inner join 
				   (	select game_account_id, max(approved_at)as date2 
					    from basic.payment
					    group by game_account_id
				    )as c 
			on a.game_account_id=c.game_account_id 
			where date2>first_login_date
		)as d 
where diffdate>=10
group by serverno
order by serverno desc