SQL 문제풀이 복습
문제 링크
window함수 활용 문제.
ranking이 딱 3인 경우만 구하면 돼서 그나마 편하다.
SELECT user_id,
spend AS "third_transaction_spend",
transaction_date AS "third_transaction_date"
FROM (SELECT user_id,
spend,
transaction_date,
RANK()
OVER(
partition BY user_id
ORDER BY transaction_date) AS "ranking",
LAG(spend)
OVER(
partition BY user_id
ORDER BY transaction_date) AS "step1",
LAG(spend, 2)
OVER(
partition BY user_id
ORDER BY transaction_date) AS "step2"
FROM Transactions) result
WHERE ranking = 3
AND spend > step1
AND spend > step2
ORDER BY 1;
문제 링크
1트(8/20)처럼 굳이 cte를 만들지 않아도
group by + having + 서브쿼리 조합으로
간단하게 구할 수 있다.
SELECT city
FROM Listings
GROUP BY 1
HAVING AVG(price) > (SELECT AVG(price)
FROM Listings)
ORDER BY 1;
문제 링크
어떤 행의 값이 최대/최소인 경우의 값을 뽑아야 할 때는
서브쿼리보다는 cte를 쓰는 게 더 알아보기 쉽고
쿼리를 짜기도 편한 것 같다.
WITH temp
AS (SELECT dep_id,
Count(*) AS "cnt"
FROM Employees
GROUP BY 1)
SELECT e.emp_name AS "manager_name",
e.dep_id
FROM Employees e
JOIN temp t
ON e.dep_id = t.dep_id
WHERE position = 'Manager'
AND cnt = (SELECT Max(cnt)
FROM temp)
ORDER BY 2;