[SQL] 라이브세션 3일차 과제

저번 SQL 라이브세션 과제는 TIL에 포함했었는데,
이제는 따로 게시글을 분리하고자 한다.

• 로 달아놓은 내용은 해설 들으면서 추가한 내용

---

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

SELECT
	serverno
	, substr(first_login_date, 1, 7) as 'm'
	, count(distinct game_account_id) as 'usercnt'
FROM
	USERS
GROUP BY
	serverno
	, 2
;

처음에는 date_format() 함수를 사용하려 했는데
first_login_date가 문자열(varchar) 형식이더라
왜 날짜 데이터가 아닌지는 모르겠지만, 문자열이라면 substr() 함수가 더 맞는 것 같아서 이쪽으로 선회

• substr() 대신 date_format(first_login_date, "%Y-%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
	count(*) > 10
;

• having count(distinct game_actor_id) > 10

---

3. 조건1) group by 절을 사용하여 서버별, 유저구분(기존/신규) 게임캐릭터id수를 구해주세요. 중복값을 허용하지 않는 고유한 갯수로 추출해주세요.
   조건2) 기존/신규 기준→ 첫 접속일자가 2024-01-01 보다 작으면(미만) 기존유저, 그렇지 않은 경우 신규유저
   조건3) 또한, 서버별 평균레벨을 함께 추출해주세요.

SELECT
	serverno
	, CASE
			WHEN first_login_date < '2024-01-01' THEN '기존유저'
			ELSE '신규유저'
		END as 'gb'
	, count(distinct game_actor_id) as 'cnt_character'
	, avg(level) AS 'avg_level'
FROM
	USERS
GROUP BY
	1
	, 2
;

사실 이 문제는 풀이를 듣고 싶은 마음인데,
조건3이 avg(level)으로 바로 해결되는 게 잘 이해가 되지 않는다
GROUP BY를 1, 2로 했으니까 severno와 gb로 한 것인데,
그렇다면 엄밀히 말해서 '서버별 평균레벨'이 아니라 '서버별, 기존/신규유저의 평균레벨'이 맞는 표현 아닐까 싶어서...
'서버별 평균레벨'은 기존, 신규 유저를 전부 합해서 평균을 계산해야 맞지 않을까?

그래서 예시 답안 이미지가 공개되기 전까지 저 식이 아니라고 생각했고,
GROUP BY를 서버로만 해놓은 서브쿼리를 만들어서 조인을 하고, 양쪽에서 하나씩 따서 출력하는 방식으로 해야하는 지 고민하고 있었다...

---

4. 조건1) 문제2번을 having 이 아닌 인라인 뷰 서브쿼리를 사용하여 추출해주세요.
   * 첫 접속 일자별 게임 캐릭터 수를 중복없이 구하기 (10개 초과하는 경우만)

SELECT 
	*
FROM
	(
	SELECT 
		first_login_date
		, count(DISTINCT game_actor_id) AS 'actor_cnt'
	FROM
		basic.USERS 
	GROUP BY
		FIRST_LOGIN_DATE 
	) aa
WHERE 
	aa.actor_cnt > 10
;

---

5. 조건1) 레벨이 30 이상인 캐릭터를 기준으로, 게임계정 별 캐릭터 수를 중복값 없이 추출해주세요.
   조건2) having 구문을 사용하여 캐릭터 수가 2 이상인 게임계정만 추출해주세요.
   조건3) 인라인 뷰 서브쿼리를 활용하여 캐릭터 수 별 게임계정 개수를 중복값 없이 추출해주세요.

처음엔 여기까지만 진행했다
조건 2까지는 수월했는데, 조건 3은 바로 떠오르지 않았기에

SELECT 
	DISTINCT actor_cnt
FROM(
	SELECT 
		GAME_ACCOUNT_ID 
		, count(DISTINCT GAME_ACTOR_ID) AS 'actor_cnt' 
	FROM
		basic.USERS 
	WHERE
		LEVEL >= 30
	GROUP BY
		GAME_ACCOUNT_ID 
	HAVING 
		actor_cnt >= 2
	) aa
ORDER BY 
	actor_cnt
;

다시 처음부터 생각해보다가,
서브쿼리만 떼어내서 만져보다가 그 출력 결과를 보고 다시 생각을 해보았다


"그냥 이걸 count하면 되는 거 아닌가?"

SELECT 
	DISTINCT actor_cnt
	, count(actor_cnt) AS 'accnt'
    # 서브쿼리에서 group by를 사용했기에 위의 count는 distinct가 필수가 아니다
FROM(
	SELECT 
		GAME_ACCOUNT_ID 
		, count(DISTINCT GAME_ACTOR_ID) AS 'actor_cnt' 
	FROM
		basic.USERS 
	WHERE
		LEVEL >= 30
	GROUP BY
		GAME_ACCOUNT_ID 
	HAVING 
		actor_cnt >= 2
	) aa
GROUP BY 
	actor_cnt
ORDER BY 
	actor_cnt
;