[SQL] WHERE절 'IN'으로 두 개 이상 컬럼 비교하기

코드카타 97번 | LeetCode - immediate food delivery

문제

If the customer's preferred delivery date is the same as the order date, then the order is called immediate; otherwise, it is called scheduled.

The first order of a customer is the order with the earliest order date that the customer made. It is guaranteed that a customer has precisely one first order.

Write a solution to find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places.

Input: 
Delivery table:
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1           | 1           | 2019-08-01 | 2019-08-02                  |
| 2           | 2           | 2019-08-02 | 2019-08-02                  |
| 3           | 1           | 2019-08-11 | 2019-08-12                  |
| 4           | 3           | 2019-08-24 | 2019-08-24                  |
| 5           | 3           | 2019-08-21 | 2019-08-22                  |
| 6           | 2           | 2019-08-11 | 2019-08-13                  |
| 7           | 4           | 2019-08-09 | 2019-08-09                  |
+-------------+-------------+------------+-----------------------------+
Output: 
+----------------------+
| immediate_percentage |
+----------------------+
| 50.00                |
+----------------------+

접근

1. customer_id 별 첫 주문을 알아낸다.
2. 첫 주문들만 뽑아서 그 주문이 immediate인지, scheduled인지 구한다.
3. immediate인 주문의 비율을 구한다.

풀이

WITH 절 내의 쿼리로 각 customer_id의 첫 주문 날짜를 뽑는다. 그리고 바깥 쿼리에서 해당 쿼리를 customer_id와 order_date를 기준으로 Delivery 테이블과 inner join을 시행한다. 이를 통해 각 손님의 첫 번째 주문만 남기고 SELECT 절로 immediate한 주문의 비율을 구한다.

WITH cte AS (SELECT customer_id, min(order_date) first_order
    FROM Delivery
    GROUP BY customer_id)
SELECT ROUND(SUM(d.order_date = d.customer_pref_delivery_date)/COUNT(*) * 100, 2) immediate_percentage
FROM cte INNER JOIN Delivery d ON cte.customer_id = d.customer_id
	AND cte.first_order = d.order_date

개선된 풀이

위 쿼리에서 WITH절 내부 쿼리로 첫 주문 정보를 (customer_id, order_date) 쌍으로 가져와 inner join 시 키로 사용하는 이유는 WITH절 내부 쿼리에서 min(order_date)로 각 고객 별 최초 주문 날짜를 구할 때 프라이머리 키인 delivery_id는 가져올 수 없기 때문이다. 만약 delivery_id를 가져올 수 있다면 WHERE절에서 IN을 사용하여 원하는 조건을 필터링할 수 있었을 것이다.
위와 같은 방법으로 WHERE 필터링을 할 때, 하나의 값으로만 IN을 사용할 수 있는 게 아니라는 것을 다른 사람의 솔루션을 통해 알게 되었다. 조건을 걸고 싶은 값을 ()로 묶고, 해당 컬럼을 가진 서브쿼리를 생성하면 WITH절 없이 원하는 결과를 얻을 수 있다. 개선된 쿼리는 아래와 같다.

Select round(avg(order_date = customer_pref_delivery_date)*100, 2) as immediate_percentage
from Delivery
where (customer_id, order_date) in (
  Select customer_id, min(order_date) 
  from Delivery
  group by customer_id
);

• WHERE (customer_id, order_date) IN (SELECT customer_id, min(order_date) FROM Delivery GROUP BY customer_id)

이 방법을 사용하면 컬럼이 두 개 이상일 때에도 WHERE-IN 문법을 사용할 수 있다.