걷기반 문제 6. 팀 프로젝트 열심히 했으니 다시 놀아볼까요?!

1) lol_users 테이블에서 각 유저의 레이팅(rating) 순위를 계산하는 쿼리를 작성해주세요! 전체 지역(region) 기준이고 순위는 레이팅이 높을수록 높음.

SELECT 
	  name
	, region
	, rating
	, RANK() OVER (ORDER BY rating DESC) ranking
FROM lol_users;

2) lol_users 테이블에서 가장 늦게 게임을 시작한(join_date) 유저의 이름을 선택하는 쿼리를 작성해주세요!

# 스스로 풀이 
SELECT 
	name
	, join_date
	, ranking
FROM 
( # ranking 구하는 서브쿼리
SELECT 
	name
	, join_date 
	, RANK() OVER (ORDER BY join_date DESC) ranking
FROM lol_users
) ranking_date
WHERE ranking = 1;

# LIMIT 사용
SELECT name 
FROM lol_users 
ORDER BY join_date DESC LIMIT 1;

3) lol_users 테이블에서 지역별로 레이팅이 높은 순으로 유저들을 정렬해서 나열하는 쿼리를 작성해주세요!

SELECT
	*
FROM
	lol_users
ORDER BY 
	region,
	rating;

4) lol_users 테이블에서 지역별로 평균 레이팅을 계산하는 쿼리를 작성해주세요!

SELECT 
	  region
	, AVG(rating) avg_rating
FROM lol_users
GROUP BY region;

LIMIT : 결과 집합에서 반환되는 행(row)의 개수를 제한

SELECT 열이름1, 열이름2, ...
FROM 테이블이름
-- 보통 ORDER BY 뒤에 사용됨
LIMIT 제한된_행_수, OFFSET 특정 위치 지정;
-- OFFSET은 0부터 시작
-- OFFSET 은 쉼표로 생략 가능

걷기반 문제 7. 랭크게임 하다가 싸워서 피드백 남겼어요…

1) lol_feedbacks 테이블에서 만족도 점수(satisfaction_score)에 따라 피드백을 내림차순으로 정렬하는 쿼리를 작성해주세요!

SELECT *
FROM lol_feedbacks
ORDER BY satisfaction_score DESC;

2) lol_feedbacks 테이블에서 각 유저별로 최신 피드백을 찾는 쿼리를 작성해주세요!

SELECT 
	user_name
	# MAX 함수 사용, 가장 큰 날짜 -> 가장 최근!
	, MAX(feedback_date) recent_feedback_date
FROM lol_feedbacks
GROUP BY user_name;

3) lol_feedbacks 테이블에서 만족도 점수가 5점인 피드백의 수를 계산하는 쿼리를 작성해주세요!

# 내 풀이 COUNT(IF)로 조건 부여
SELECT 
	COUNT(IF(satisfaction_score = 5, id, NULL)) score_five
FROM lol_feedbacks;

# 답지 전부 센 후 WHERE 조건 부여
SELECT COUNT(*) 
FROM lol_feedbacks 
WHERE satisfaction_score = 5;

4) lol_feedbacks 테이블에서 가장 많은 피드백을 남긴 상위 3명의 고객을 찾는 쿼리를 작성해주세요!

SELECT 
	  user_name
	, COUNT(*) count_feedbacks
FROM lol_feedbacks
GROUP BY user_name
ORDER BY 2 DESC LIMIT 3;

동률인데 문제에선 딱히 언급이 없다..!

5) lol_feedbacks 테이블에서 평균 만족도 점수가 가장 높은 날짜를 찾는 쿼리를 작성해주세요!

SELECT 
	feedback_date
	, AVG(satisfaction_score) avg_score
FROM lol_feedbacks
GROUP BY feedback_date
ORDER BY 2 DESC LIMIT 1;

걷기반 문제 8. LOL을 하다가 홧병이 나서 병원을 찾아왔습니다.

1) doctors 테이블에서 전공(major)가 성형외과인 의사의 이름을 알아내는 쿼리를 작성해주세요!

SELECT name
FROM doctors
WHERE major = '성형외과';

2) doctors 테이블에서 각 전공 별 의사 수를 계산하는 쿼리를 작성해주세요!

SELECT 
	major ,
	COUNT(*) doctor_number
From doctors
GROUP BY major;

3) doctors 테이블에서 현재 날짜 기준으로 5년 이상 근무(hire_date)한 의사 수를 계산하는 쿼리를 작성해주세요!

SELECT COUNT(*) num_of_doctors 
FROM doctors
# 고용 날짜가 현재시간에서 5년 뺀 것보다 작다 => 5년 이상 근무했다
WHERE hire_date <= DATE_SUB(CURDATE(), INTERVAL 5 YEAR);
# 고용날짜 년도 빼기 현재 년도, 대략적으로 년도만 계산함
WHERE TIMESTAMPDIFF(YEAR, hire_date, curdate()) >=5;

4) doctors 테이블에서 각 의사의 근무 기간을 계산하는 쿼리를 작성해주세요!

# TIMESTAMPDIFF 사용 
SELECT 
	name
    	-- 뒤에꺼 빼기 앞에꺼!!!
	, TIMESTAMPDIFF(DAY, hire_date, CURDATE())
FROM doctors 

# DATEDIFF 사용
SELECT 
	name
    	-- 앞에꺼 빼기 뒤에꺼!!!
	, DATEDIFF(Curdate(), hire_date)
FROM doctors 

날짜 차이 계산

TIMESTAMPDIFF 두 날짜를 직접적으로 뺌

# 뒤에꺼 빼기 앞에꺼!
TIMESTAMPDIFF(단위, 시작날짜, 끝날짜)

DATEDIFF 날짜 차이만 계산

DATEDIFF(시작날짜, 끝날짜) = TIMESTAMPDIFF(day, 시작날짜, 끝날짜)
# 하지만 DATEDIFF는 앞에꺼 빼기 뒤에꺼!!!

DATE_SUB 날짜를 기준으로 특정 기간(연도, 월, 일 등)을 뺌

DATE_SUB(기준날짜, INTERVAL n 단위) 

단위 모음
YEAR	연도 단위의 차이
QUARTER	분기(3개월) 단위의 차이
MONTH	월 단위의 차이
WEEK	주 단위의 차이
DAY		일 단위의 차이
HOUR	시간 단위의 차이
MINUTE	분 단위의 차이
SECOND	초 단위의 차이

걷기반 문제 9. 아프면 안됩니다! 항상 건강 챙기세요!

1) patients 테이블에서 각 성별(gender)에 따른 환자 수를 계산하는 쿼리를 작성해주세요!

SELECT 
	gender,
	COUNT(*) patients_num
FROM patients
GROUP BY gender;

2) patients 테이블에서 현재 나이가 40세 이상인 환자들의 수를 계산하는 쿼리를 작성해주세요!

SELECT 
	COUNT(*) over_40
FROM patients
WHERE birth_date <= DATE_SUB(CURDATE(), INTERVAL 40 YEAR);

3) patients 테이블에서 마지막 방문 날짜(last_visit_date)가 1년 이상 된 환자들을 선택하는 쿼리를 작성해주세요!

SELECT *
FROM patients
WHERE last_visit_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

4) patients 테이블에서 생년월일이 1980년대인 환자들의 수를 계산하는 쿼리를 작성해주세요!

SELECT 
	COUNT(*) 80s
FROM patients
WHERE YEAR(birth_date) LIKE '198%';

4번문제 답지에 있는 답 돌려봤는데 에러 뜸.. 뭐냐

걷기반 문제 10. 이젠 테이블이 2개입니다

1) 현재 존재하고 있는 총 부서의 수를 구하는 쿼리를 작성해주세요!

SELECT COUNT(name) `총 부서 수`
FROM departments;

2) 모든 직원과 그들이 속한 부서의 이름을 나열하는 쿼리를 작성해주세요!

SELECT 
	  e.name
	, d.name
FROM employees e
	LEFT JOIN departments d
	ON e.department_id = d.id;

3) '기술팀' 부서에 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!

SELECT 
	  e.name
	, d.name
FROM employees e
	LEFT JOIN departments d
	ON e.department_id = d.id
WHERE d.name = '기술팀';

4) 부서별로 직원 수를 계산하는 쿼리를 작성해주세요!

SELECT 
	  d.name
	, COUNT(e.name) count_enplyees
FROM employees e
	LEFT JOIN departments d
	ON e.department_id = d.id
GROUP BY d.name;

5) 직원이 없는 부서의 이름을 찾는 쿼리를 작성해주세요!

SELECT d.name
FROM employees e
	LEFT JOIN departments d
	ON e.department_id = d.id
WHERE e.id IS NULL 

6) '마케팅팀' 부서에만 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!

SELECT 
	e.name
FROM employees e
	LEFT JOIN departments d
	ON e.department_id = d.id
WHERE d.name = '마케팅팀';

수업에서 연습하던 테이블 JOIN보다 훨씬 쉬워서 바로바로 풀었다.
마지막 연습 문제는 시간 관계상 간단히 생각하고 답 맞춰보면서 풀어서 풀이를 쓰지 않겠다. SQL 걷기반 문제 풀이 종료!

느낀점

SQL 문제 풀이까지 마무리했다. 다음주에 아티클 스터디 + 파이썬 정리하고 문제풀이까지 한 다음, 그 다음주에는 데이터 문해력 책 스터디하고 직접 타이타닉, 보스턴집값 데이터 분석을 해볼 수 있으면 좋겠다!