라이브세션 ③/⑤ SQL과제

 

[SQL] 예제로 익히는 SQL - 3회차 (notion.site)

[SQL] 예제로 익히는 SQL - 3회차 | Notion

 

라이브세션 3회차 SQL과제

 

 

문제1 - 집계함수의 활용

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

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

 

(방법 1) date_format(컬럼, '%')

select serverno,

date_format(first_login_date,'%Y-%m') as 'm',

count(distinct game_actor_id) 'usercnt'

from users

group by 1,2

 

(방법2) substr (컬럼, 시작, 끝)

 

select serverno,

          substr(first_login_date,1,7) as m ,

          count(distinct game_account_id) as usercnt

from basic.users

group by 1,2

 

 

문제2 - 집계함수와 조건절의 활용

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

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

 

select first_login_date,

count(distinct game_actor_id) 'actor_cnt'

from users

group by 1

having count(distinct game_actor_id)>10

 

 

 

문제3 - 집계함수와 조건절의 활용

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

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

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

(IF 절 방법)

select serverno,

           if(first_login_date<'2024-01-01','기존유저','신규유저') 'gb',

           count(distinct game_actor_id) actor_cnt,

           avg(level) 'avg_level'

from users

group by 1,2

 

(case when 방법)

select serverno,

          case when first_login_date <'2024-01-01' then '기존유저'

          else '신규유저' end as gb ,

          count(distinct game_actor_id) as actor_cnt,

          avg(level)as avg_level

from users

group by 1,2

 

 

 

🔥문제4 - SubQuery의 활용

2번 문제를 having 이 아닌 인라인 뷰 subquery 를 사용하여, 추출해주세요.

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

 

(오답_ 인라인 뷰 서브쿼리 혼자 처음 써봄 예시대로 했는데 에러-_-;)

select a.first_login_date,

game_actor_id 'actor_cnt'

from (select first_login_date, game_actor_id from users where count(distinct game_actor_id)>10 group by 1) as a

 

(정답)

select *

from

(select first_login_date, count(distinct game_actor_id) as actor_cnt

from users

group by 1) as a

where actor_cnt > 10

 

 

 

🔥🔥문제5 - SubQuery의 응용

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

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

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

 

select a.actor_cnt,

count(distinct game_account_id) 'accnt'

from

(

select game_account_id,

count(distinct game_actor_id) 'actor_cnt'

from users

where level>=30

group by 1) a

group by 1

having actor_cnt>=2

 

 

 

[작동순서]: FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY

라이브세션 5회차 SQL 과제

 

[SQL] 예제로 익히는 SQL - 5회차 (notion.site)

[SQL] 예제로 익히는 SQL - 5회차 | Notion

 

코트카타

 

(문제 31) 오랜 기간 보호한 동물(1)

아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.

 

select a.name,
           a.datetime
from animal_ins a left join animal_outs b on a.animal_id= b.animal_id
where b.datetime is null
order by datetime asc
limit 3

 

 

(문제 55) 조건에 맞는 사용자와 총 거래금액 조회하기

USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 완료된 중고 거래의 총금액이 70만 원 이상인 사람의 회원 ID, 닉네임, 총거래금액을 조회하는 SQL문을 작성해주세요. 결과는 총거래금액을 기준으로 오름차순 정렬해주세요.

 

select user_id,
       nickname,
       total_price
from
(
SELECT b.user_id 'user_id',
       b.nickname 'nickname',
       sum(a.price) 'total_price'
from used_goods_board a inner join used_goods_user b on a.writer_id=b.user_id
where status='done'
group by 1) a
where total_price >= 700000
order by 3

 

 

 

문제 36 보호소에서 중성화한 동물

보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화1되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.

 

SELECT a.animal_id animal_id,
              a.animal_type animal_type,
              a.name name
from animal_ins a inner join animal_outs b on a.animal_id=b.animal_id
where a.sex_upon_intake like "%intact%" and (b.sex_upon_outcome like "%spayed%" or b.sex_upon_outcome like "%neutered%")
order by animal_id

 

 

테이블 가져오기 

- new table 먼저 만들고 - 오른쪽 마우스 클릭 - 데이터 가져오기 csv 열기

 

문제1 - JOIN 활용

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

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

조건3) gb를 기준으로 게임계정수를 추출해주세요. 컬럼 이름은 usercnt로 지정해주시고, 결과값은 아래와 같아야 합니다. 힌트: 기준이 되는 테이블의 데이터는 그대로 두어야겠죠?

 

(값은 다르긴 한데 일단,  제출하고 내일 해설보쟈) 

select case when b.pay_amount is null then '결제안함'

else '결제함' end 'gb',

count(a.game_account_id) 'usercnt'

from users a left join payment b on a.game_account_id = b.game_account_id

group by 1

 

usercnt (내꺼)

 

정답 usercnt

 

(정답)

 

# 정답 쿼리
select case when b.game_account_id is null then '결제안함' else '결제함' end as gb
, count(distinct a.game_account_id) as usercnt 
from

(select game_account_id from basic.users ) as a left outer join ( select game_account_id from basic.payment) as b
on a.game_account_id=b.game_account_id
group by case when b.game_account_id is null then '결제안함' else '결제함' end
;

 

문제2 - JOIN 응용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 game_account_id,

count(distinct game_actor_id) 'actor_cnt',

sum(pay_amount) 'sumamount'

from

(

select distinct b.game_account_id, b.pay_amount, b.pay_type, b.approved_at, a.game_actor_id

from users a inner join payment b on a.game_account_id = b.game_account_id

where a.serverno>=2 and b.pay_type='CARD') a

group by 1) b

where actor_cnt>=2

order by 3 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

 

 

매출 금액이 중복되는 것을 방지하기 위해, 아래와 같은 구문이 사용되었습니다. distinct game_account_id, pay_amount, approved_at ← 이는 distinct 뒤의 세 컬럼을 하나의 덩어리로 인식하고, 이 하나의 덩어리를 고유하게 추출해줍니다. 실제로 데이터의 중복 적재가 일어나는 경우를 방지하기 위해 많이 사용됩니다. (고객은 1번 결제했는데 똑같은 데이터가 2번 기록되면, 매출이 2배로 잘못 찍힘을 방지)

 

 

 

 

문제3 - JOIN 응용2

조건1) user 테이블에서 game_account_id, first_login_date, serverno 를 추출한 결과와

조건2) payment 테이블에서 game_account_id 별 가장 마지막 결제일자를 찾고 그 컬럼이름을 date2로 지정해주세요. 그 다음 inner join 을 진행해주세요. 다만, 첫 접속일자보다 마지막 결제일자가 큰 경우만 추출해주세요.

조건3) 조인 결과를 바탕으로 마지막 결제일자-첫 접속일자 를 구해주세요. 그리고 컬럼이름을 diffdate로 설정해주세요. 두 날짜의 형식은 같아야 합니다.

조건4) 인라인 뷰 subquery 를 이용하여 서버별 평균 diffdate를 구해주시고, 컬럼이름을diffdate로 설정해주세요. 해당컬럼은 정수 형태로 출력되어야 합니다.

조건5) 조건절에 diffdate 값이 10일 이상인 경우를 필터링해주세요. 그리고 서버번호를 기준으로 내림차순 정렬해주세요. 결과값은 아래와 같아야 합니다. 전체결과 중 일부입니다.

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

 

 

（훔、 안에 값이 다름 ㅠ—ㅠ ｄａｔｅ를 붙여도 다름 －ㅁ－ 움、、 일단 내일 해설을 봐야겠음 -> AVG 문제오류）

select serverno,

avgdiffdate

from

(select serverno,

round(avg(diffdate),0)'avgdiffdate'

from

(select game_account_id,

first_login_date,

serverno,

date2,

date2-date(first_login_date) 'diffdate'

from

(select a.game_account_id,

a.first_login_date,

a.serverno,

max(date(b.approved_at)) 'date2'

from users a inner join payment b on a.game_account_id = b.game_account_id

group by 1,2,3)a

where first_login_date < date2)b

group by 1)c

where avgdiffdate >= 10

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

 

 

 

 

다중조인 - 비추 대신 두 개 하고 다시 조인 건다..!

 

 

길더라도 정확하게 추출하기!!