240625-(P) 라이브세션 1회차 과제

2회차 수업 시간에 확인 받고 최종 수정 예정

 

/*문제1 date 컬럼이 2023-01-01 보다 큰 날짜의 game_account_id, game_actor_id, serverno를 추출해주세요.*/

select game_account_id,

game_actor_id,

serverno,

date

from users u

where date > '2023-01-01'

 

 

 

/*문제2 level > 10,

* serverno !=1

* etc_str2='레벨업 패키지' or '시즌패스'

* etc_str1 '상점에서 구매'

* date, ip_addr, exp, zone_id 조회 date 기준 내림차순 */

 

select date,

ip_addr,

exp,

zone_id

from users u

where serverno > 1

and etc_str2 in ('레벨업%' or '시즌패스')

and etc_str1 in ('상점에서 구매')

order by date desc;

 

/*문제3 레벨구간을 구분하고 game_actor_id, level, levelgroup, date 조회 date기준 내림차순*/

 

select game_account_id,

level,

case when level between 1 and 10 then '1~10Lv'

when level between 11 and 20 then '11~20Lv'

when level between 21 and 30 then '21~30Lv'

when level between 31 and 40 then '31~40Lv'

when level between 41 and 50 then '41~50Lv'

when level between 51 and 60 then '51~60Lv'

when level between 61 and 70 then '61~70Lv'

when level between 71 and 80 then '71~80Lv'

when level between 81 and 90 then '81~90Lv'

else '91~100Lv'

end 'levelgroup',

date

from users u

order by date desc

 

/*문제4 date를 yyyy-mm 으로 바꾸고 이름 m

game_account_id, game_actor_id 추출 월기준으로 오름차순 월이 같으면 game_actor_id 기준 내림 차순*/

 

select game_account_id,

game_actor_id,

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

date_format(date,'%m') as 'm2'

from users u

order by date_format(date,'%m') desc, game_actor_id desc