SQL 코드카타
SELECT c.name AS Customers
FROM customers c
LEFT JOIN orders o
ON c.id = o.customerid
WHERE o.id IS NULL
WITH a
AS (SELECT t.request_at,
Count(CASE
WHEN u.banned = 'No'
AND u2.banned = 'No' THEN id
ELSE NULL
END) AS cnt_total
FROM trips t
LEFT JOIN users u
ON t.client_id = u.users_id
LEFT JOIN users u2
ON t.driver_id = u2.users_id
GROUP BY 1),
b
AS (SELECT t.request_at,
Count(CASE
WHEN u.banned = 'No'
AND u2.banned = 'No'
AND status LIKE '%cancelled%' THEN id
ELSE NULL
END) AS cnt_cancelled
FROM trips t
LEFT JOIN users u
ON t.client_id = u.users_id
LEFT JOIN users u2
ON t.driver_id = u2.users_id
GROUP BY 1)
SELECT a.request_at AS 'Day',
Round(cnt_cancelled / cnt_total, 2) AS 'Cancellation Rate'
FROM a
INNER JOIN b
ON a.request_at = b.request_at
WHERE Round(cnt_cancelled / cnt_total, 2) IS NOT NULL
AND a.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY 1
오랜만에 leetcode에서 만난 난이도 hard 문제.
CTE를 잘 쓰면 푸는 건 어렵지 않았는데
조건을 한두개씩 빼먹다가 풀이 시간을 많이 잡아먹었다.
drivers와 client 모두 banned_user가 아니어야 하므로 u1과 u2 모두 banned = 'No' 인 경우에 한해 날짜별 total user와 cancelled user를 카운트한다.
날짜가 10월 1부터 10월 3일까지라고 문제에 나와 있었는데, 계속 이걸 빼먹어서 에러가 났다ㅠ 문제 잘 읽기...
그냥 count로 처리하면 cancelled user가 없어서 (즉, 모든 user가 completed 상태여서) 취소율이 0인 경우와 banned user가 섞여서 취소율이 null인 경우를 효과적으로 구분하기 어렵다.
따라서 그냥 count를 하지 말고 특정 조건을 만족할 때(case when)만 count를 수행하고 다른 경우는 null로 처리하게 한 뒤, 메인 쿼리의 where 절에서 null인 경우를 제외하는 게 좋겠다.
이렇게 해야 0으로 표시하면서 출력 결과에 포함시켜야 할 것들과 아예 표시조차 하지 않고 출력 결과에서 제외시켜야 할 것들이 명확하게 구분될 듯.
SELECT player_id,
Min(event_date) AS first_login
FROM activity
GROUP BY 1
WITH result
AS
(
SELECT customer_number,
count(*) AS cnt
FROM orders
GROUP BY 1)
SELECT customer_number
FROM result
ORDER BY cnt DESC
LIMIT 1
문제에서 추가로 준 상황.
Follow up: What if more than
one customer has the largest number of orders,
can you find all the customer_number in this case?
최고 주문 건수를 기록한 고객이 여러 명일 때는
어떻게 쿼리를 짤 것인지를 물어보는 건데,
그럴 때는 아래와 같이 해결하면 된다.
WITH result
AS (SELECT customer_number,
Count(*) AS cnt
FROM orders
GROUP BY 1)
SELECT customer_number
FROM result
WHERE cnt = (SELECT Max(cnt)
FROM result)
WITH a
AS (SELECT o.sales_id,
s.NAME AS name_excluded
FROM orders o
INNER JOIN salesperson s
ON o.sales_id = s.sales_id
INNER JOIN company c
ON o.com_id = c.com_id
WHERE c.NAME = 'RED')
SELECT s.NAME
FROM salesperson s
LEFT JOIN a
ON s.sales_id = a.sales_id
WHERE a.sales_id IS NULL
오늘은 컨디션이 안좋아서
클러스터링 복습은 못 했다ㅜ
지금은 할 일 욕심내지 말고
다음 팀프로젝트 시작하기 전에 얼른 회복부터 해야지.