[sql] DAY14 (solvesql)

lv쉬움. Aggregate : 점검이 필요한 자전거 찾기
2021년 2월 정기점검 대상 자전거를 추출하려고 합니다. rental_history 테이블을 사용해 2021년 1월 한 달간 총 주행 거리가 50km 이상인 자전거의 ID를 출력하는 쿼리를 작성해주세요.

#where절에는 집계 함수를 바로 쓸 수 없다는 사실을 까먹지 말자

select distinct
  bike_id
from
  rental_history
where
  rent_at like '2021-01%'
group by
  bike_id
having
  sum(distance) >= 50000

---

lv쉬움. Aggregate : 첫 주문과 마지막 주문
주문 일자를 나타내는 order_purchase_timestamp 컬럼을 통해 첫 주문 일자와 마지막 주문 일자를 알아보려고 합니다. 아래 두 컬럼을 포함하는 쿼리를 작성해주세요.

first_order_date - 첫 주문 일자 (예: 2018-01-01)
last_order_date - 마지막 주문 일자 (예: 2018-08-31)

select
  min(date(order_purchase_timestamp)) as first_order_date,
  max(date(order_purchase_timestamp)) as last_order_date
from
  olist_orders_dataset

---

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

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

/*case~when문의 사용법을 잘 보여주는 예제
between 날짜 and 날짜에서 뒤의 날짜는 +1일 해줘야 원하는 사이 값을 구할 수 있음.
왜 order_estimated_delivery_date와 order_delivered_customer_date의 비교 순서를 바꾸면
정답이 아니라고 나오는지 모르겠다... 부등호도 바꿔서 썼는데 말이죠ㅠ*/

select
  date(order_purchase_timestamp) purchase_date,
  count(
    case
      when order_estimated_delivery_date > order_delivered_customer_date then order_id
    end
  ) success,
  count(
    case
      when order_estimated_delivery_date <= order_delivered_customer_date then order_id
    end
  ) fail
from
  olist_orders_dataset
where
  order_purchase_timestamp between '2017-01-01' AND '2017-02-01'
group by
  purchase_date
order by
  purchase_date

---

lv쉬움. SELECT : 많이 주문한 테이블 찾기
tips 테이블에는 식사 금액, 팁, 결제자 성별, 요일, 시간대 등 어느 레스토랑의 테이블 당 결제에 관련된 데이터가 들어있습니다. 식사 금액이 테이블 당 평균 식사 금액보다 더 많은 경우를 모두 출력하는 쿼리를 작성해주세요. 결과에는 tips 테이블에 있는 모든 컬럼이 포함되어야 합니다.

#where절에 select문 붙여서 집계 함수 쓰기

select
  *
from
  tips
where
  total_bill >= (
    select
      avg(total_bill)
    from
      tips
  )

---

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

#왜 in 생각을 못했을까ㅠ

select
  *
from
  tips
where
  day in (
    select
      day
    from
      tips
    group by
      day
    having
      sum(total_bill) >= 1500
  )