240628-(S)(40)루시와 엘라 찾기 / 1주차 라이브세션 과제

 

SELECT  ANIMAL_ID,
        NAME,
        SEX_UPON_INTAKE
from ANIMAL_INS
where NAME in ('Lucy','Ella','Pickle','Rogan','Sabrina','Mitty')

 

 

무난히 성공쓰!

 

 

---

1주차 라이브세션 과제

 

https://teamsparta.notion.site/SQL-SQL-3-e9eff65f43e641d1b9ec5cf5b0dbc6a8

[SQL] 데이터와 친해지는 SQL - 3회차 | Notion

 

 [문제1] payment 테이블에서

* 1. pay_type=money

* and 2. pay_amount >=500000

* 데이터의 개수를 count

select count(*)
from basic.payment p 
where pay_type in ('MONEY')
and pay_amount >= 500000;

 

 

[문제2] pay_type 별 "최소" pay_amount를 구하고 그 값이 500 이상인 경우를 추출

select 	pay_type,
		min(pay_amount) as 'min_payment_amt'
from basic.payment p 
group by pay_type
having min_payment_amt>500;

 

[문제3] 서버별로 결제한 사용자의 수

* 서버번호와 해당서버에서 결제한 사용자수를 반환

* 사용자수는 중복제거

* PK는 game_account_id

 

select	u.serverno,
		count(distinct u.game_account_id) as 'users_with_payment'
from basic.users u left join basic.payment p on u.game_account_id=p.game_account_id
where p.pay_amount is not null
group by u.serverno;

 

 

 

[문제4] user테이블에서 serverno가 2이상인 데이터, pay_type card 인 경우 join

game_account_id 별로 game_actor_id의 갯수를 중복 없이 카운트 컬럼명:actorcnt

game_account_id 별 pay_amount의 합을 출력 컬럼명 : sumamount

acorcnt가 2이상인 경우만 추출, sumamount 내림차순 정렬

 

select 	u.game_account_id,
		count(distinct u.game_actor_id) as 'actorcnt',
		sum(p.pay_amount) as 'sumamount'
from basic.users u left join basic.payment p on u.game_account_id=p.game_account_id
where u.serverno >=2
and p.pay_type in ('CARD')
group by u.game_account_id
having actorcnt >= 2
order by sumamount desc;

 

 

[문제5] game_account_id 마지막 접속날짜, 상세로그정보 조회

* 최근에 접속한 순서 정렬

select u.game_account_id,
		case when u.date >= p.approved_at then date_format(u.date,'%Y-%m-%d')
		when p.approved_at is null then date_format(u.date,'%Y-%m-%d')
		else date_format(p.approved_at,'%Y-%m-%d')
		end as 'last_login_date',
		u.logid,
		u.ip_addr,
		u.date,
		u.game_actor_id			
from basic.users u left join basic.payment p on u.game_account_id = p.game_account_id
order by 2 desc;