[SolveSQL] 가구 판매의 비중이 높았던 날 찾기,버뮤다 삼각지대에 들어가버린 택배 찾기

1. 가구 판매의 비중이 높았던 날 찾기 - https://solvesql.com/problems/day-of-furniture/

solvesql

Q. US E-Commerce Records 2020 데이터셋은 미국 이커머스 웹사이트의 판매 데이터 입니다. 이 중 records 테이블은 주문 번호, 주문 날짜, 주문 지역, 카테고리 등 주문의 상세 정보를 담고 있습니다. 이 데이터를 이용하여 가구 판매의 비중이 높았던 날을 찾고 싶습니다. 일별 주문 수가 10개 이상인 날 중에서, ‘Furniture’ 카테고리 주문의 비율이 40% 이상 이었던 날만 출력하는 쿼리를 작성해주세요. 카테고리 정보는 category 컬럼에 기록되어 있습니다.

결과 데이터는 아래의 컬럼들을 포함해야 합니다. Furniture 카테고리의 주문 비율은 백분율로 계산하며, 반올림하여 소수점 둘째자리까지만 출력해주세요. Furniture 카테고리의 주문 비율이 높은 것부터 보여주도록 정렬하고, 비율이 같다면 날짜 순으로 정렬해주세요.

• order_date - 주문 날짜
• furniture - 해당 일의 Furniture 카테고리 주문 수
• furniture_pct - 해당 일의 전체 주문 대비 Furniture 카테고리 주문의 비율 (%)

결과 데이터 예시

결과 데이터 해석

• 2020년 6월 30일 주문 중 6건은 Furniture 카테고리에서 나온 주문입니다. 이는 2020년 6월 30일 전체 주문 중 약 54.55% 비중을 차지합니다.
• 2020년 12월 29일 주문 중 6건은 Furniture 카테고리에서 나온 주문입니다. 이는 2020년 12월 29일 전체 주문 중 약 50% 비중을 차지합니다.

A. 주의)오라클 환경이 아닌, SQLITE 환경에선  '/' 기호를 통해 나누기만 하면 나머지 없이 정수만 나온다는 것. 이를 감안해서 코드를 짜봤다. 방법은 데이터를 float 형태로 바꿔주거나 CAST 함수를 쓰는 방법 2가지가 있는데 나는 전자 픽(?).

SELECT order_date
      ,count(distinct CASE WHEN category = "Furniture" THEN order_id END) as "furniture"
      --,count(distinct order_id) as "total_count"
      ,round(count(distinct CASE WHEN category = "Furniture" THEN order_id END)/(count(distinct order_id)+0.00)*100,2) as furniture_pct
FROM records
GROUP BY order_date
HAVING COUNT(distinct order_id) >= 10
      AND furniture_pct >= 40
ORDER BY furniture_pct desc, order_date

음...HAVING 조건절 때문에 오히려 많이 헤맸다 ^0^ ....

그리고 저 나누기를 잘못하면 쿼리 결과가 '없음'으로 뜨니까.....주의해야 한다 :(

 

2. 버뮤다 삼각지대에 들어가버린 택배 - https://solvesql.com/problems/shipment-in-bermuda/

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_delivered_carrier_date 컬럼에 택배사 도착 시각은 기록되지만, order_delivered_customer_date 컬럼의 값은 null으로 저장됩니다.

일단 원인을 파악하기 전에 이런 일들이 얼마나 발생하고 있는지 현황 파악을 해보려고 합니다. 2017년 1월 한 달 동안 택배사에 전달되었지만 배송 완료는 되지 않은 주문 건수를 택배사 도착일을 기준으로 집계하는 쿼리를 작성해주세요. 쿼리 결과는 택배사 도착일을 기준으로 오름차순 정렬되어야 하고, 아래 컬럼을 포함해야 합니다.

• delivered_carrier_date - 택배사 도착 날짜 (예: 2017-01-16)
• orders - 택배사에 도착했지만, 고객에게 배송되지 않은 주문 건 수

A. 같은 변수에 대해 Group by와 order by를 모두 적용하는 게 개인적으로는 신선했다 :)

SELECT date(order_delivered_carrier_date) as delivered_carrier_date
     , count(*) as orders
FROM olist_orders_dataset
WHERE order_delivered_customer_date is null 
  AND date(order_delivered_carrier_date) between '2017-01-01' and '2017-01-31' 
  AND order_delivered_carrier_date IS NOT NULL
GROUP BY date(order_delivered_carrier_date)
ORDER BY date(order_delivered_carrier_date)

결과 :)