[SQL] 데이터와 친해지는 SQL | 3회차 숙제

※ 문제에 사용되는 table 구조

1. USERS 테이블

 

2. PAYMENT 테이블

 

※ Table 칼럼 설명

 

 

 

1. payment 테이블에서 pay_type이 ‘MONEY’이고 pay_amount가 500,000 이상인 데이터의 개수를 count 해주세요. 

-- 풀이
select count(*)
from payment
where pay_type = "MONEY" and pay_amount>=500000

 

실행결과

 

 

2. group by 와 having 절을 사용하여 pay_type 별 최소 pay_amount를 구하고, 그 값이 500이상인 경우를 추출해주세요. 

-- 풀이
select pay_type, min(pay_amount) as min_pay_amt
from payment
group by pay_type
having min_pay_amt >= 500

 

실행결과

 

 

 

3. 각 서버(serverno)별로 결제한 사용자의 수를 계산하세요. 서버 번호와 해당 서버에서 결제한 사용자 수를 반환해야 합니다. 사용자 수는 중복 제거한 상태로 계산해주세요.  (순서 상관X)

-- 풀이
select u.serverno, 
	count(distinct(p.game_account_id)) users_with_payment 
from users u inner join payment p on u.game_account_id =p.game_account_id 
group by u.serverno

-> 사용자 수 중복 제거해 주어야 하니까 count 쓰기 전에  distinct 쓰는거 주의해주기

 

실행결과

 

 

 

 

4.  user 테이블에서 서버번호(serverno)가 2 이상인 데이터와 payment 테이블에서 결제 수단(pay_type)이 CARD 인 경우를 join해 주시고

-  게임계정id(game_account_id)별로 게임캐릭터id(game_actor_id)의 갯수를 중복값없이 세어주시고, actorcnt 으로 컬럼명을 명시해주세요.

-  게임계정id(game_account_id) 별 결제금액(pay_amount) 의 합을 출력해주시고, sumamount 으로 컬럼명을 명시해주세요.

-  최종적으로 actorcnt가 2 이상인 경우만 추출하고, sumamount 의 내림차순으로 정렬해주세요.

 

-- 풀이
select u.game_account_id, 
	count(distinct(game_actor_id)) as actorcnt,
	sum(pay_amount) as sumamount 
from
(select * from users where serverno >= 2) u
inner join
(select * from payment where pay_type ='CARD') p
on u.game_account_id = p.game_account_id
group by game_account_id
having actorcnt >= 2
order by sumamount desc

-> 이거는 차근차근 순서대로 풀면된당

실행결과

 

 

 

5. - 게임 계정 ID(game_account_id)별로 마지막으로 접속한 날짜와 그 날짜의 상세 로그 정보를 조회하세요. ‘마지막 접속한 날짜’를 의미하는 컬럼의 컬럼명은 *last_login_date* 로 지정해주세요.

- 여기서 마지막 접속 날짜는 가장 최근의 날짜를 의미하며, 사용자의 상세 로그는 logid, ip_addr, date, game_actor_id를 포함해야 합니다.

- 결과는 사용자 구분 없이 최근에 접속한 순서로 정렬되어야 합니다.

 

-- 풀이
select u.game_account_id, last_login_date, logid, ip_addr, `date`, game_actor_id
from (
select game_account_id, max(`date`) last_login_date
from users
group by game_account_id
) as a, users u
where u.game_account_id = a.game_account_id and u.`date` = a.last_login_date
order by 2 desc

-> 내 쿼리보다 좋은 방법이 있을 것 같기는 한데, 일단 나는 이렇게 풀었다.

 

서브쿼리 부분만 써 놓고, logid, ip_addr, date, game_actor_id를 어떻게 뽑아올지 고민했다.

처음에 select로 위 컬럼들을 모두 선택하고  group by에도 이 컬럼들을 다 추가하니까,

group by는 포함되는 컬럼들의 고유한 조합을 모두 그룹화하기 때문에, last_login_date를 만든 의미가 없이 중복되었었다.

 

그래서 서브쿼리를 사용해야 겠다고 전환.

where절에 서브쿼리를 작성하는 방법으로 할려다가, 각이 안나와서 인라인 뷰로 서브쿼리 작성하였다. 

game_account_id와 last_login_date 가 같은걸 뽑고 싶은데 생각이 안나서, 냅다 from에 테이블 2개 넣어서 where로 추출했다.

실행결과

 

 

(쓰다가 알게 된건데, 서브쿼리와 원래 테이블을 game_account_id와 last_login_date 를 공통 컬럼으로 inner join을 사용했다면 더 좋았을 것 같다.)

SELECT u.game_account_id, subquery.last_login_date, u.logid, u.ip_addr, u.`date`, u.game_actor_id
FROM users AS u
INNER JOIN (
    SELECT game_account_id, MAX(`date`) AS last_login_date
    FROM users
    GROUP BY game_account_id
) AS subquery
ON u.game_account_id = subquery.game_account_id AND u.`date` = subquery.last_login_date
ORDER BY 2 DESC;

-> 이런식으로 말이다