[PostgreSQL] solvesql LV.3 배송 예정일 예측 성공과 실패

https://solvesql.com/problems/estimated-delivery-date/

🔍 문제

2017년 1월 한 달 동안 발생한 주문 데이터에서 배송 완료 예정 날짜 컬럼인 `olist_estimated_delivery_date`보다 일찍 도착했으면 `success`, 그렇지 못하면 `fail`컬럼에 주문 수를 계산하시오

---

🎯정답

-- CTE를 이용해서 배송이 예정 날짜보다 일찍 도착하면 1, 그렇지 못하면 0으로 집계되도록 만들었다
with delivery_result as (
select
  to_char(order_purchase_timestamp, 'YYYY-MM-DD') as "purchase_date",
  case
    when order_delivered_customer_date < order_estimated_delivery_date then 1
    else 0 end as "delivery_ss"
from olist_orders_dataset
where to_char(order_purchase_timestamp, 'YYYY-MM-DD') BETWEEN '2017-01-01' and '2017-01-31' and order_status = 'delivered'
order by order_purchase_timestamp ASC)

-- 이후 1이 들어간 행은 success컬럼에 더했고, 0이 들어간 행은 fail컬럼에 더했다
select
  purchase_date,
  sum(case when delivery_ss = 1 then 1 else 0 end) as "success",
  sum(case when delivery_ss = 0 then 1 else 0 end) as "fail"
from delivery_result
group by purchase_date

❓풀이

• 조금 복잡하게 풀었다고 생각한다.. 먼저 배송 예정 날짜보다 일찍 도착했으면 1, 그렇지 못하면 0으로 집계될 수 있게 CTE문으로 새로운 테이블을 만들었다.
• 이후 1로 집계된 행의 수를 구하여 `success` 컬럼에 개수를 구했고, 0으로 집계된 행의 수를 구해서 `fail`컬럼에 구했다.
• 다른 사람들의 풀이를 보니 더 쉽게 구할 수 있는 방법이 있어서 가져와봤다.

select
  date(order_purchase_timestamp) as purchase_date,
  count(case when date(order_delivered_customer_date) < date(order_estimated_delivery_date) then order_id end) as "success",
  count(case when date(order_delivered_customer_date) >= date(order_estimated_delivery_date) then order_id end) as "fail"
from
  olist_orders_dataset
where
  order_delivered_customer_date is not null
  AND
  date(order_purchase_timestamp) BETWEEN '2017-01-01' and '2017-01-31'
group by
  date(order_purchase_timestamp)
order by 1

• CTE 없이 바로 계산해서 count했다. 불필요한 CTE문을 만들 필요가 없기 때문에 연산량이 줄어들 것으로 생각한다.

---

❗️최적의 코드

위에 두 쿼리문은 인덱스 사용이 어려운 단점이 존재한다고 한다.

SELECT
  order_purchase_timestamp::date AS purchase_date,
  SUM((order_delivered_customer_date <  order_estimated_delivery_date)::int) AS "success",
  SUM((order_delivered_customer_date >= order_estimated_delivery_date)::int) AS "fail"
FROM olist_orders_dataset
WHERE order_status = 'delivered'
  AND order_purchase_timestamp >= TIMESTAMP '2017-01-01'
  AND order_purchase_timestamp <  TIMESTAMP '2017-02-01'
  AND order_delivered_customer_date IS NOT NULL
  AND order_estimated_delivery_date IS NOT NULL
GROUP BY order_purchase_timestamp::date
ORDER BY purchase_date;

• 타임스탬프를 이용해서 비교하였고, 기간은 반개구간을 사용했기 때문에 인덱스 활용이 가능해진다