38일차 - SQL 코딩테스트 연습

Date : 2023-08-10 (프로젝트 기간으로 늦게 작성..)

Topic : SQL 코딩테스트 연습

---

Note

sakila 테이블로 연습하기

# 2006-02-14 기준으로 최근 30일 동안 영화를 대여하지 않은 고객을 찾으세요. -- 다시 풀기
SELECT first_name, last_name
FROM customer cu
LEFT JOIN rental r ON r.customer_id = cu.customer_id AND
	TIMESTAMPDIFF(DAY, r.rental_date, '2006-02-14')
	<= 30
WHERE r.rental_date IS NULL;

/* 가장 최근에 대여한 영화 중, 가장 늦게 반납한 상위 10명의 고객 이름과 그들이 대여한 영화의 이름, 그리고 대여 기간을 출력해 주세요.
고객 이름은 customer_name, 영화 이름은 movie_title, 대여 기간은 rental_duration으로 출력해주세요. */
SELECT
	CONCAT(first_name, ' ', last_name) AS customer_name, -- 고객 이름
	f.title AS movie_title, -- 대여한 영화 이름
    TIMESTAMPDIFF(DAY, r.rental_date, r.return_date) AS rental_duration -- 대여 기간
FROM rental r
JOIN customer cu ON cu.customer_id = r.customer_id
JOIN inventory i ON i.inventory_id = r.inventory_id
JOIN film f ON f.film_id = i.film_id
ORDER BY r.return_date DESC -- 1.가장 늦게 반납한
LIMIT 10; -- 2.상위 10명

/*
SELECT TIMESTAMPDIFF(DAY, '2023-08-11 23:59:59', '2023-08-12 00:00:01') AS difference;
SELECT DATEDIFF('2023-08-12 00:00:01', '2023-08-11 23:59:59') AS difference;
*/

/* 각 직원의 매출을 찾고, 각 직원의 매출이 회사 전체 매출 중 어느 정도 비율을 차지하는지 찾으세요.
결과는 직원 ID, 직원 이름, 각 직원의 매출, 회사 전체 매출에 대한 비율(%)로 보여주세요. */
SELECT
	sf.staff_id,
	sf.first_name,
	sf.last_name,
    SUM(p.amount) AS staff_revenue,
	ROUND(SUM(p.amount)/(SELECT SUM(amount) FROM payment)*100, 2) AS pay_per
FROM payment p
JOIN staff sf ON sf.staff_id = p.staff_id
GROUP BY sf.staff_id;

/* 각 고객별로 어떤 카테고리를 가장 많이 대여하는지 알고 싶습니다. 각 고객별로 가장 많이 대여한 영화 카테고리와
해당 카테고리에서의 총 대여 횟수, 그리고 해당 고객 이름을 조회하는 SQL 쿼리를 작성해주세요.
자주 대여하는 카테고리에 동률이 있을 경우 모두 보여주세요. */ -- 다시 풀기
SELECT
	CONCAT(cu.first_name, ' ', cu.last_name) AS customer_name, -- 고객 이름
	c.name, -- 카테고리 이름
	COUNT(*) AS cnt_category -- 해당 카테고리 대여 횟수 카운트
FROM category c
JOIN film_category fc ON fc.category_id = c.category_id
JOIN inventory i ON i.film_id = fc.film_id
JOIN rental r ON r.inventory_id = i.inventory_id
JOIN customer cu ON cu.customer_id = r.customer_id
GROUP BY cu.customer_id, c.name
HAVING cnt_category = (
	SELECT COUNT(*) FROM rental r2
	JOIN inventory i2 ON i2.inventory_id = r2.inventory_id
	JOIN film_category fc2 ON fc2.film_id = i2.film_id
    WHERE r2.customer_id = cu.customer_id
	GROUP BY fc2.category_id
    ORDER BY COUNT(*) DESC
    LIMIT 1) -- 
ORDER BY cu.customer_id;

/* -- 다시 풀어보기!
SELECT 
 C.first_name, C.last_name, 
    CA.name,
    COUNT(*) rental_count
FROM customer C
JOIN rental R ON R.customer_id = C.customer_id
JOIN inventory INV ON INV.inventory_id = R.inventory_id
JOIN film_category FC ON FC.film_id = INV.film_id
JOIN category CA ON CA.category_id = FC.category_id
GROUP BY C.customer_id, CA.name
HAVING COUNT(*) = (
 SELECT MAX(rental_count) 
    FROM (
        SELECT 
            R2.customer_id, 
            FC2.category_id, 
            COUNT(*) rental_count
        FROM rental R2
        JOIN inventory INV2 ON INV2.inventory_id = R2.inventory_id
        JOIN film_category FC2 ON FC2.film_id = INV2.film_id
        WHERE R2.customer_id = C.customer_id
        GROUP BY R2.customer_id, FC2.category_id
    ) CUSTOMER_RENTAL_COUNT
)
*/

---

employees 테이블로 연습하기

# employees 테이블에서 'Geert', 'Parto' 등의 이름을 가진 사원의 emp_no 알아내기 (emp_no 와 이름 출력하기)
SELECT
	emp_no,
	CONCAT(first_name, ' ', last_name) AS name
FROM employees
WHERE first_name IN ('Geert', 'Parto') OR last_name IN ('Geert', 'Parto');

# departments 테이블에서 'Sales', 'Marketing' 부서의 dept_no 알아내기 (dept_no 와 부서명 출력하기)
SELECT dept_no FROM departments
WHERE dept_name IN ('Sales', 'Marketing');

# salaries 테이블에서 100000 이상의 연봉을 받는 사원의 emp_no와 연봉 출력하기
SELECT emp_no, salary FROM salaries
WHERE salary >= 100000;

# titles 테이블에서 'Senior Engineer', 'Staff'의 title을 가진 사원의 emp_no와 직위명 출력하기
SELECT emp_no, title FROM titles
WHERE title IN ('Senior Engineer', 'Staff');

# 사원번호(emp_no) 10005번인 사원의 이름과 성별 출력하기
SELECT first_name, last_name, gender FROM employees
WHERE emp_no = 10005;

# Marketing 부서에서 일하는 모든 사원의 emp_no와 부서명 출력하기
SELECT emp_no, dept_name FROM departments d
JOIN dept_emp de ON de.dept_no = d.dept_no
WHERE dept_name = 'Marketing';

# 'Senior Engineer' 직위에서 일하는 사원 중 연봉이 70000 이상인 사원의 emp_no, 직위명, 연봉 출력하기
SELECT
	e.emp_no,
	t.title,
	MAX(s.salary) AS salary -- 2.사원번호를 기준으로 가장 높은 연봉을 출력한다(사원번호 중복 제외하기 위해)
FROM titles t
JOIN employees e ON e.emp_no = t.emp_no
JOIN salaries s ON s.emp_no = e.emp_no
WHERE title = 'Senior Engineer' AND salary >= 70000
GROUP BY e.emp_no; -- 1.연봉 협상으로 한 사람의 연봉이 매년 상승하기 때문에

# 사원의 성(last_name)이 'Markovitch'인 사원의 emp_no, 이름, 성 출력하기
SELECT emp_no, first_name, last_name FROM employees
WHERE last_name = 'Markovitch';

# 'Finance' 부서에서 'Senior Staff'로 일하는 모든 사원의 emp_no와 부서명, 직위명 출력하기
SELECT e.emp_no, dept_name, title
FROM employees e
JOIN dept_emp de ON de.emp_no = e.emp_no
JOIN departments d ON d.dept_no = de.dept_no
JOIN titles t ON t.emp_no = e.emp_no
WHERE title = 'Senior Staff' AND dept_name = 'Finance';

# 1990년에 고용된 모든 사원의 emp_no, 이름, 고용날짜 출력하기
SELECT
	e.emp_no,
	CONCAT(first_name, ' ', last_name) AS emp_name,
	from_date
FROM employees e
JOIN salaries s ON e.emp_no = s.emp_no
WHERE from_date LIKE '%1990-%';

/* 현재 'Engineer' 직위를 가진 모든 사원의 emp_no와 직위명을 출력하기
(titles 테이블의 to_date는 직위의 종료일을 나타냅니다. '9999-01-01'은 아직 직위가 종료되지 않았음을 나타냅니다) */
SELECT emp_no, title FROM titles
WHERE title = 'Engineer' AND to_date LIKE '9999-%';

# 1995년에 고용된 사원 중 연봉이 60000 이상인 사원의 emp_no, 고용날짜, 연봉 출력하기
SELECT
	e.emp_no,
	e.hire_date,
	s.salary
FROM employees e
JOIN salaries s ON s.emp_no = e.emp_no
WHERE s.salary >= 60000 AND e.hire_date LIKE '1995-%';

# 'Senior Engineer' 직위를 가지고 있는 사원들 중에서, 'd005' 부서에 속한 사원의 emp_no, 직위명, 부서번호 출력
SELECT de.emp_no, title, d.dept_no
FROM departments d
JOIN dept_emp de ON de.dept_no = d.dept_no
JOIN titles t ON t.emp_no = de.emp_no
WHERE title = 'Senior Engineer' AND d.dept_no = 'd005';

# 1990년부터 2000년까지 고용된 사원 중에서, 'Sales' 부서에서 일하고 있는 사원의 emp_no, 이름, 고용날짜, 부서명 출력
SELECT
	e.emp_no,
	CONCAT(first_name, ' ', last_name) AS emp_name,
	e.hire_date,
	dept_name
FROM employees e
JOIN dept_emp de ON de.emp_no = e.emp_no
JOIN departments d ON d.dept_no = de.dept_no
WHERE
	(YEAR(e.hire_date) BETWEEN 1990 AND 2000) AND	-- 2.이렇게 타입을 명시해주고 (YEAR()) 숫자(연도)로 비교하는 것이 더 정확
    # ('1990-01-01' <= e.hire_date <= '2000-01-01')	-- 1.DATETIME과 문자열이 혼합되어 있을 때 단순히 크다 작다로 비교하기 보다
	dept_name = 'Sales';

# 현재 'Marketing' 부서에서 일하면서 연봉이 80000 이상인 사원의 emp_no, 부서명, 연봉 출력하기
SELECT
	de.emp_no,
	d.dept_name,
	s.salary
FROM departments d
JOIN dept_emp de ON de.dept_no = d.dept_no
JOIN salaries s ON s.emp_no = de.emp_no
WHERE
	dept_name = 'Marketing' AND
	salary >= 80000 AND
	de.to_date = '9999-01-01'; -- 문제에 '현재' 일하고 있다는 조건 있음. 놓치지 않도록 주의⭐

# 모든 부서에서 연봉 상위 5명의 사원의 emp_no, 부서명, 연봉 출력하기 -- 다시 풀기
SELECT s.emp_no, dept_name, salary
FROM salaries s
JOIN dept_emp de ON de.emp_no = s.emp_no
JOIN departments d ON d.dept_no = de.dept_no
ORDER BY salary DESC
LIMIT 5;

/*
정답: 
SELECT emp_no, dept_name, salary
FROM (
    SELECT 
        e.emp_no, 
        d.dept_name, 
        s.salary,
        DENSE_RANK() OVER (PARTITION BY d.dept_name ORDER BY s.salary DESC) AS rank_in_dept
    FROM employees e
    JOIN dept_emp de ON e.emp_no = de.emp_no
    JOIN departments d ON de.dept_no = d.dept_no
    JOIN salaries s ON e.emp_no = s.emp_no
) AS RankedSalaries
WHERE rank_in_dept <= 5
ORDER BY dept_name, salary DESC;
*/

# 각 부서별로 연봉 평균이 가장 높은 직위를 가진 사원의 emp_no, 부서명, 직위명, 평균 연봉 출력하기
/* SELECT
*
FROM salaries s
JOIN dept_emp de ON de.emp_no = s.emp_no
JOIN departments d ON d.dept_no = de.dept_no
JOIN titles t ON t.emp_no = de.emp_no

정답: 
SELECT rd.emp_no, rd.dept_name, rd.avg_salary
FROM (SELECT
 e.emp_no, d.dept_name, AVG(s.salary) AS avg_salary,
 DENSE_RANK() OVER (PARTITION BY d.dept_name ORDER BY AVG(s.salary) DESC) AS ranking
FROM employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
JOIN departments d ON de.dept_no = d.dept_no
JOIN titles t ON e.emp_no = t.emp_no
JOIN salaries s ON e.emp_no = s.emp_no
GROUP BY e.emp_no, d.dept_name, t.title
) rd
WHERE rd.ranking = 1 */

---

8월 둘째주부터 2주 조금 안되는 시간 동안 진행한 프로젝트로, 밀렸던 SQL 코딩테스트 연습을 이제서야 올리게 됐다. 프로젝트 기간에도 물론 SQL을 사용했지만 파이썬이나 엑셀 같은 툴을 같이 사용하다보니 잠시 연습 안했다고 그새 감이 좀 죽었다. 그러다보니 '-- 다시 풀어보기' ← 라는 주석을 많이 달게 되었는데 제출 기한 상 어쩔 수 없이 오답노트 개념으로 학습일지를 적게 되었지만,,, 다시 연습하면 그만이니까 ㅎㅎ 어느 정도 감을 익혀야 자신 있게 SQL코테에서 써먹을 수 있을까 아직도 깜깜하고 걱정되지만 프로젝트를 비롯하여 그동안 배운 것들이 피와 살이 된 것 같아 감사한 점이 많다. 왜 내 회고는 항상 이렇게 감상적이게 되는지🥲 프로젝트가 끝난지 D+2인데 쉴 새 없이 다시 무언가를 준비해야 하는 게 아쉽기도 하면서 설렌다😎 아무튼!! 다시 열심히 공부하고, 체크해놓은 부분 잊지 않고 복습할 것. 이상 일지 작성 끝.