[2주차] 프로그래밍 기초 주차 (4/5)

ㅇ 오늘 목표 : 데이터 리터러시 완강 + 세션 과제 검증 + 생활코딩 DB 5, 6강

 

 

어제 세션에서 어려웠던 2가지 문제가 있다. (덕분에 어제 2시에 잤다..)

쿼리나 문제 조건을 모두 부합하는데, 숫자가 달라, 오전 내내 고민했다.

결국 A문제는 users테이블의 서브쿼리 내 group by에 불필요한 serverno가 들어있었고, 

B문제는 평균과 반올림까지 모두 연산된 diffdate에서 '>=10'의 조건을 거니 두 문제 모두 숫자가 틀어질 수밖에 없었다.

 

sum(pay_amount)에 distinct 를 사용하면 안 된다는 것도 이번에 알았는데,

group by 마에 불필요한 컬럼이 들어있으면 숫자가 틀어질 수 있다는 것을 알게 되었다. 

 

그래도 두 문제 모두 풀고 나니 마음이 매우 가볍다. 

일단 내일부턴 파이썬 강의를 빡세게 들어볼 예정이다. (부디 기억이 돌아오길..) 

sqld 접수가 내일까진데, 병행이 가능할지 의문이다.

 

<문제 A>

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

 

<쿼리>

select u.game_account_id
	   , u.actorcnt
	   , p.sumamount 
from(
		select game_account_id       # 중복 값 제거
			   , count(distinct game_actor_id) actorcnt       # 중복 값 제거
               #, serverno  -- 요녀석!!
		from users u 
		where serverno >= 2       # 조건: 서버 2이상
		group by game_account_id#, serverno  -- 요녀석!!
	)as u
left join
	(
		select game_account_id
		       , sum(pay_amount) sumamount 
		from payment p
		where pay_type = 'CARD'       # 조건 : 결제타입_카드
		group by 1
	)as p
on u.game_account_id = p.game_account_id
where actorcnt >= 2       # 조건 : 캐릭터수 2이상
	  and p.sumamount is not null       # 조건 : 매출액 != null
group by 1,2,3
order by game_account_id
;

 

<결과>

 

 

<문제 B>

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

 

<쿼리>

select u.serverno
	   , round(avg(datediff(p.p_date,u.m_date)),0) as diffdate  # 날짜계산, 평균, 반올림순(정수처리)
from(
		select game_account_id
			  , `date` m_date
			  , serverno 
		from users u 
	)as u
left join
	(
		select game_account_id
			   , max(date_format(approved_at,'%Y-%m-%d')) p_date  # 최근 값
		from payment p
		group by game_account_id
	)as p
on u.game_account_id = p.game_account_id
where datediff(p.p_date,u.m_date) >= 10  # 날짜gap 10일 이상 필터링
# where diffdate >= 10  -- 요 녀석!!
group by u.serverno
order by u.serverno desc  # 서버번호 기준 내림차순
;

 

<결과>

 

 

ㅇ 내일 목표 : 파이썬 종합반 강의 수강 + 생활코딩 DB 7, 8강 (시간이 남으면 SQL코드카타 3문제까지)