[241011] SQL 스터디

과제

• 3회차
  • 로그정의서

    구분	상세	schema
    logid	로그id	int
    ip_addr	ip주소	string
    first_login_date	첫 접속일자, yyyy-mm-dd	string
    game_account_id	게임계정id	string
    game_actor_id	게임캐릭터id	int
    level	캐릭터 레벨	int
    exp	현재경험치	int
    serverno	서버넘버	int
    zone_id	지역넘버	int
    etc_num1	파티id	int
    etc_num2	파티원수	int
    etc_str1	아이템 획득경로	string
    etc_num3	아이템 획득량	int
    etc_str2	아이템 이름	string

  • 💪 지난번 DBeaver에 업로드 한 users.csv 파일을 기준으로, 아래 쿼리문을 작성해 주세요.

    1. 문제1 - 집계함수의 활용

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

          select serverno,
          			 substring(first_login_date, 1, 7),
          			 count(distinct game_account_id)
          from users u 
          group by 1, 2;

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

       ---

    2. 문제2 - 집계함수와 조건절의 활용
       - 조건1) group by 를 활용하여 첫 접속일자별 게임캐릭터수를 중복값 없이 구하고,
       - 조건2) having 절을 사용하여 그 값이 10개를 초과하는 경우의 첫 접속일자 및 게임캐릭터id 개수를 추출해주세요.

        select first_login_date, count(distinct game_actor_id) 
        from users u 
        group by 1
        having count(distinct game_actor_id) > 10;

       ---

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

        select serverno,
        	   count(case when first_login_date < '2024-01-01' then 1 end) as '기존',
        	   count(case when first_login_date >= '2024-01-01' then 1 end) as '신규',
        	   avg(`level`) 
        from users u 
        group by 1
        order by 1;

       ---

    4. 문제4 - SubQuery의 활용
       - 위와 같은 문제를 having 이 아닌 인라인 뷰 subquery를 사용하여, 추출해주세요.
       - 조건1) 문제 2번을 having 이 아닌 인라인 뷰 서브쿼리를 사용하여 추출해주세요.
       - 힌트: 인라인 뷰 서브쿼리는 from 절 뒤에 위치하여, 마치 하나의 테이블 같은 역할을 했었습니다!

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

       ---

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

        select cnt_actor_id, count(distinct game_account_id) 
        from
        (
        select game_account_id, count(distinct game_actor_id) as cnt_actor_id
        from users u 
        where level >= 30
        group by 1
        having count(distinct game_actor_id) >= 2 
        ) a
        group by 1;

       ---

• 5회차
  • 로그정의서 - users

    구분	상세	schema
    logid	로그id	int
    ip_addr	ip주소	string
    first_login_date	첫 접속일자, yyyy-mm-dd	string
    game_account_id	게임계정id	string
    game_actor_id	게임캐릭터id	int
    level	캐릭터 레벨	int
    exp	현재경험치	int
    serverno	서버넘버	int
    zone_id	지역넘버	int
    etc_num1	파티id	int
    etc_num2	파티원수	int
    etc_str1	아이템 획득경로	string
    etc_num3	아이템 획득량	int
    etc_str2	아이템 이름	string

  • 로그정의서 - payment

    구분	상세	schema
    game_account_id	게임계정id	string
    pay_amount	결제금액	bigint
    pay_type	결제수단	string
    approved_at	결제승인일시	string

  • 문제1 - JOIN 활용
    1. 조건1) 알맞은 join 방식을 사용하여 users 테이블을 기준으로, payment 테이블을 조인해주세요.
    2. 조건2) case when 구문을 사용하여 결제를 한 유저와 결제를 하지 않은 게임계정을 구분해주시고, 컬럼이름을 gb로 지정해주세요.
    3. 조건3) gb를 기준으로 게임계정수를 추출해주세요. 컬럼 이름은 usercnt로 지정해주시고, 결과값은 아래와 같아야 합니다.

                    select gb, count(distinct game_account_id)
                    from
                    (
                    select u.game_account_id,
                           case when p.game_account_id is null then '결제안함'
                           else '결제함' end as gb
                    from users u 
                    left join payment p on u.game_account_id = p.game_account_id 
                    ) a
                    group by 1;

• 힌트: 기준이 되는 테이블의 데이터는 그대로 두어야겠죠?