[SolveSQL] 배송 예정일 예측 성공과 실패, 레스토랑의 대목

datarian SQL 데이터 분석 캠프 실전반

1. 배송 예정일 예측 성공과 실패 - https://solvesql.com/problems/estimated-delivery-date/

solvesql

Q. Brazilian E-Commerce Public Dataset by Olist 데이터셋은 브라질의 이커머스 웹사이트인 Olist Store의 판매 데이터 입니다. 그 중 olist_orders_dataset 테이블에는 주문 ID, 고객 ID, 주문 상태, 구매 시각 등 주문 내역 데이터가 들어있습니다. Olist의 주문부터 배송까지 프로세스는 다음 단계를 통해 이루어지고, 각 단계마다 시각을 기록하고 있습니다.

1. 고객의 구매
   • order_purchase_timestamp 컬럼에 구매 시점이 저장됨
2. 판매자가 주문을 승인
   • order_approved_at 컬럼에 승인 시점이 저장됨
3. 택배사에 도착하여 배송 시작
   • order_delivered_carrier_date 컬럼에 배송 시작 시점이 저장됨
4. 배송 완료
   • order_delivered_customer_date 컬럼에 배송 완료 시점이 저장됨

추가로 order_estimated_delivery_date 컬럼에는 주문 시점에 계산한 배송 예정 시각이 저장되어 있습니다.

2017년 1월 한 달 동안 발생한 주문의 배송 예측이 정확했는지 분석을 하려고 합니다. 고객의 구매 일자별로 배송 예정 시각 안에 고객에게 도착한 주문과, 배송 예정 시각이 지나서 고객에게 도착한 주문을 각각 집계하는 쿼리를 작성해주세요. 배송 완료 또는 배송 예정 시각 데이터가 없는 경우는 계산에서 제외합니다. 계산 결과는 구매 날짜를 기준으로 오름차순 정렬되어야 하고, 아래 컬럼을 포함해야 합니다.

• purchase_date - 구매 날짜 (예: 2017-01-01)
• success - 배송 예정 시각 안에 고객에게 도착한 주문 수
• fail - 배송 예정 시각이 지나 고객에게 도착한 주문 수

A. when을 활용해서(case when) 날짜 조건을 추가하고, order by 등장시키기 :-) 들여쓰기를 제대로 하지 않으면 select ~ , 들여쓰기 후 count를 넣게 되면 쿼리는 26개 도출된다(=실패). 주의!

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 order_estimated_delivery_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 date(order_purchase_timestamp);

정답은 이렇게 총 27개 행이 나와야 한다!

2. 레스토랑의 대목 - https://solvesql.com/problems/high-season-of-restaurant/

solvesql

Q. tips 테이블에는 식사 금액, 팁, 결제자 성별, 요일, 시간대 등 어느 레스토랑의 테이블 당 결제에 관련된 데이터가 들어있습니다. 요일별 매출액 합계를 구하고, 매출이 1500 달러 이상인 요일의 결제 내역을 모두 출력하는 쿼리를 작성해주세요. 쿼리 결과에는 tips 테이블에 있는 모든 컬럼이 포함되어야 합니다.

 

A. tips 테이블의 모든 컬럼이므로 Select * From tips가 된다. Where 조건을 마저 작성해보면 Day가 (쿼리문 2차 작성), 2차로 작성된 쿼리문에는 Select day, from tips, + group by & Having 조건이 있어야 한다.

SELECT * FROM TIPS
WHERE DAY IN (SELECT DAY FROM TIPS GROUP BY DAY HAVING SUM(TOTAL_BILL) >= 1500);

무사 출력 완 :)