문제:
아래 조건을 만족하여, 알맞은 조인방식으로 users 테이블과 payment 테이블을 조인해주시고,
게임계정별 보유캐릭터수 및 평균 결제금액을 구해주세요. 전체 결과값 중 일부입니다.
조건1) users 테이블에서 서버번호가 2 이상인 데이터를 구해주세요.
그리고, payment 테이블에서 결제방식이 CARD 인 경우의 게임계정별 결제금액 합계를 구
하고 이름을 total_amt 로 지정해주세요.
그 다음,이 조건 모두를 만족하는 경우를 알맞은 방식으로 join 해 주세요.
조건2) 조인한 결과를 바탕으로 users 테이블의 게임계정을 기준으로 게임캐릭터수를 중복값없이 세고 컬럼 이름을 actor_cnt로 지정해주세요. 또한 total_amt 값의 평균을 구해주시고, 컬럼 이름을 avg_amount로 지정해주세요.
조건3) having 을 사용하지 않고, 인라인 뷰 subquery 사용으로 actor_cnt수가 2 이상인 경우만 추출해주세요. 그리고 avg_amount를 기준으로 내림차순 정렬해주세요.
박지완
select
c.game_account_id,
c.actor_cnt,
c.avg_amount
from
(
select
a.game_account_id,
count(distinct a.game_actor_id)as actor_cnt,
avg(b.total_amt)as avg_amount
from
(select *
from users
where serverno >= 2)a
inner join
(select game_account_id, sum(pay_amount) as total_amt
from payment
where pay_type = "CARD"
group by game_account_id)b
on
a.game_account_id = b.game_account_id
group by
a.game_account_id)c
where
c.actor_cnt >= 2
order by
c.avg_amount desc
SQL
복사
배시환
select c.game_account_id,
c.actor_cnt,
c.avg_amount
from (
select a.game_account_id, count(distinct(a.game_actor_id)) as actor_cnt, avg(b.total_amt) as avg_amount
from( select *
from users
where serverno >= 2
) as a
left join
( select game_account_id, sum(pay_amount) as total_amt
from payment
where pay_type = "CARD"
group by 1
) as b
on a.game_account_id = b.game_account_id
group by a.game_account_id
) as c
where c.actor_cnt >= 2
order by c.avg_amount desc
SQL
복사
이하진
select *
from(
select u.game_account_id, count(distinct game_actor_id) actor_cnt, avg(total_amt) avg_amount
from(
select game_account_id, game_actor_id
from basic.users
where serverno>=2
) u
inner join(
select game_account_id, sum(pay_amount) total_amt
from basic.payment
where pay_type='CARD'
group by 1
) p
on u.game_account_id = p.game_account_id
group by 1
) a
where actor_cnt>=2
order by 3 desc
SQL
복사
김종선
select c.game_account_id ,
c.actor_cnt ,
c.avg_amount
from
(
select a.game_account_id ,
COUNT(distinct a.game_actor_id) as actor_cnt,
AVG(b.total_amt) as avg_amount
from
(
select game_account_id, game_actor_id
from basic.users
where serverno >= 2
) as a
inner join
(
select game_account_id,
SUM(pay_amount) as total_amt
from basic.payment
where pay_type = 'CARD'
group by game_account_id
) as b
on a.game_account_id = b.game_account_id
group by a.game_account_id
) c
where c.actor_cnt >= 2
order by c.avg_amount desc
SQL
복사