아무리 사소하더라도 배움이 없는 날은 없다.
SQL 문제풀이 복습
Leetcode
문제 링크
union과 서브쿼리, join과 case when,
count, sum 등 여러가지 개념들을 섞어서 푸는 문제.
차근히 따라가다 보면 어렵지는 않다.
1트(7/21) 때는 모든 서브쿼리를 CTE로 만들어서 풀었는데,
이 문제의 경우는 가독성을 생각했을 때 서브쿼리보단 CTE가 나을 것 같기도.
어떤 사람의 풀이를 보니
team 테이블을 메인으로 놓고
sum과 if 함수만 써서 푼 경우도 있더라.
union이나 서브쿼리, CTE도 없이(!) 오오 서양용자 오오
SELECT team_name,
matches_played,
points,
goal_for,
goal_against,
( goal_for - goal_against ) AS "goal_diff"
FROM (SELECT team,
COUNT(*) AS "matches_played",
SUM(goals) AS "goal_for"
FROM (SELECT home_team_id AS "team",
home_team_goals AS "goals"
FROM Matches
UNION ALL
SELECT away_team_id,
away_team_goals
FROM Matches) a
GROUP BY 1) a2
JOIN (SELECT team,
SUM(goal_a) AS "goal_against"
FROM (SELECT home_team_id AS "team",
away_team_goals AS "goal_a"
FROM Matches
UNION ALL
SELECT away_team_id,
home_team_goals
FROM Matches) b
GROUP BY 1) b2
ON a2.team = b2.team
JOIN Teams t
ON a2.team = t.team_id
JOIN (SELECT team,
SUM(points) AS "points"
FROM (SELECT home_team_id AS "team",
CASE
WHEN home_team_goals > away_team_goals THEN 3
WHEN home_team_goals = away_team_goals THEN 1
ELSE 0
end AS "points"
FROM Matches
UNION ALL
SELECT away_team_id,
CASE
WHEN home_team_goals < away_team_goals THEN 3
WHEN home_team_goals = away_team_goals THEN 1
ELSE 0
end AS "points"
FROM Matches) c
GROUP BY 1) c2
ON a2.team = c2.team
ORDER BY points DESC,
goal_diff DESC,
team_name ASC;문제 링크
본 쿼리에서 2개의 테이블을 cross join해야
쉽게 풀릴 것 같아서 이 문제는 서브쿼리 대신 CTE를 만들었다.
본 쿼리를 만들 때 하나 유의해야 하는 점이,
PERIOD_DIFF 함수를 쓰려면 날짜 인자를 넣을 때 포맷이 %Y%m으로 고정이다.
즉, 202410 이런 식으로 써야만 오류가 없고 2024-10처럼 쓰면 오류가 뜬다.
이 때문에 CTE를 만들 때 %Y%m으로 썼던 것.
WITH result
AS (SELECT t.account_id,
DATE_FORMAT(day, '%Y%m') AS "ym",
Sum(CASE
WHEN type = 'Creditor' THEN amount
END) AS "income",
max_income
FROM Transactions t
JOIN Accounts a
ON t.account_id = a.account_id
GROUP BY 1,
2
HAVING income > max_income)
SELECT DISTINCT r1.account_id
FROM result r1,
result r2
WHERE r1.account_id = r2.account_id
AND PERIOD_DIFF(r1.ym, r2.ym) = '1';만약 익숙한 형태의 timestampdiff를 써서
똑같은 정답을 내려면 아래와 같은 형태로 쿼리를 바꾸면 된다.
이 때 주의해야 할 점은
timestampdiff를 써서 정확히 개월수 차이를 계산하고 싶으면
date_format을 써서 모든 날짜를 1일 시작으로 맞춰줘야 한다는 것.
그렇지 않고 %Y-%m-%d 로만 쓰면 인접한 날짜의 차이를 무조건 1로 계산하기 때문에
2021-05-27 17:16:32와 2021-07-28 12:54:50의 차이도 1이라고 뜨는
오류가 생긴다.
WITH result
AS (SELECT t.account_id,
DATE_FORMAT(day, '%Y-%m-01') AS "ymd",
Sum(CASE
WHEN type = 'Creditor' THEN amount
END) AS "income",
max_income
FROM Transactions t
JOIN Accounts a
ON t.account_id = a.account_id
GROUP BY 1,
2
HAVING income > max_income)
SELECT DISTINCT r1.account_id
FROM result r1,
result r2
WHERE r1.account_id = r2.account_id
AND TIMESTAMPDIFF(month, r1.ymd, r2.ymd) = 1;문제 링크
dense_rank를 쓸 줄 아는지 묻는 문제.
중간에 공동 등수가 끼어 있더라도 unique 3를 골라야 하므로
dense_rank를 써 줘야 한다. (그냥 rank를 쓰면 2등이 2명일 경우 3등이 씹힘)
SELECT Department,
Employee,
Salary
FROM (SELECT d.NAME AS "Department",
e.NAME AS "Employee",
salary,
DENSE_RANK()
OVER(
partition BY e.departmentId
ORDER BY salary DESC) AS "ranking"
FROM Employee e
JOIN Department d
ON e.departmentId = d.id) a
WHERE ranking <= 3; 프로그래머스
프로그래머스 문제는 참 코멘트 할 게 별로 없다;
date_format으로 짜친 함정을 파거나..
가끔씩 튀어나오는 이해할 수 없는 정답 기준 때문인 듯.
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS "SALES_DATE",
PRODUCT_ID,
USER_ID,
SALES_AMOUNT
FROM ONLINE_SALE
WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') = '2022-03'
UNION ALL
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS "SALES_DATE",
PRODUCT_ID,
NULL,
SALES_AMOUNT
FROM OFFLINE_SALE
WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') = '2022-03'
ORDER BY 1,
2,
3;SELECT DISTINCT o1.USER_ID,
o1.PRODUCT_ID
FROM ONLINE_SALE o1
JOIN ONLINE_SALE o2
ON o1.USER_ID = o2.USER_ID
AND o1.PRODUCT_ID = o2.PRODUCT_ID
AND o1.ONLINE_SALE_ID <> o2.ONLINE_SALE_ID
ORDER BY 1,
2 DESC;SELECT COUNT(*) AS "USERS"
FROM USER_INFO
WHERE AGE BETWEEN 20 AND 29
AND YEAR(JOINED) = '2021';SELECT DATE_FORMAT(s.sales_date, '%Y') year,
DATE_FORMAT(s.sales_date, '%m') month,
COUNT(DISTINCT s.user_id) purchased_users,
ROUND(COUNT(DISTINCT s.user_id) / (SELECT COUNT(DISTINCT user_id)
FROM user_info
WHERE joined LIKE '2021%'), 1)
purchased_ratio
FROM online_sale s
LEFT JOIN user_info u
ON s.user_id = u.user_id
WHERE u.joined LIKE '2021%'
GROUP BY 1,
2
ORDER BY 1,
2;SELECT p.PRODUCT_CODE,
SUM(SALES_AMOUNT * PRICE) AS "SALES"
FROM OFFLINE_SALE o
JOIN PRODUCT p
ON o.PRODUCT_ID = p.PRODUCT_ID
GROUP BY 1
ORDER BY 2 DESC,
1;