[SQL 문제] 요일별 매출액, 최초/마지막 주문일, 요일별 매출액 평균

1. 요일별 매출액 집계. 매출액은 반올림하여 소수점 둘째자리까지만 출력

SELECT day
	, ROUND(SUM(total_bill)) as 매출액
FORM tips
GROUP BY day -- 요일별

Q. 아래 형식에 맞춰 요일별, 시간대별 매출액을 집계해주세요. 매출액은 반올림하여 소수점 둘째자리까지만 출력해주세요.

day	lunch	dinner
Sun
Sat
Thur
Fri

SELECT day, 
      round(sum(if(time = "Lunch", total_bill, 0)),2) as Lunch,
      round(sum(if(time = "Dinner", total_bill, 0)),2) as Dinner
FROM tips
GROUP BY day

 

 

2. 첫번째 주문일자(min), 마지막 주문일자(max)

SELECT DATE(min(order_purchase_timestamp)) first_order_date
      ,DATE(max(order_purchase_timestamp)) last_order_date
FROM olist_orders_dataset

 

 

3. 고객의 구매 일자별로 배송 예정 시각 안에 고객에게 도착한 주문과, 배송 예정 시각이 지나서 고객에게 도착한 주문을 각각 집계

• 배송 완료 또는 배송 예정 시각 데이터가 없는 경우는 계산에서 제외
• 계산 결과는 구매 날짜를 기준으로 오름차순 정렬

SELECT DATE(order_purchase_timestamp) purchase_date
      , COUNT(CASE WHEN order_delivered_customer_date <= order_estimated_delivery_date 
							THEN order_id END) AS success
      , COUNT(CASE WHEN order_delivered_customer_date > order_estimated_delivery_date 
							THEN order_id END) AS fail 
FROM olist_orders_dataset o
WHERE o.order_purchase_timestamp BETWEEN '2017-01-01 00:00:00' AND '2017-01-31 23:59:59'
AND order_delivered_customer_date IS NOT NULL 
AND order_estimated_delivery_date IS NOT NULL 
GROUP BY order_purchase_timestamp
ORDER BY order_purchase_timestamp ASC

 

 

 

4. 하루 평균 몇번의 범죄가 일어나는지 구하기

SELECT daily_stats.week
			,AVG(daily_stats.incidents_daily)
FROM (
			SELECT week
						, date
						, COUNT(incident_id) AS incidents_daily
			FROM crimes
			GROUP BY week,date
			) daily_stats -- 테이블처럼 사용가능
GROUP BY daily_stats.week

 

 

5. 레스토랑의 요일별 총 매출액의 평균을 구하기

SELECT AVG(sales)
FROM (
  SELECT day
        ,SUM(total_bill) AS sales
  FROM tips
  GROUP BY day
) AS daily -- 서브쿼리 테이블 별칭 필수!!

 

-- WITH문 활용 (자주 사용)
WITH daily AS(
  SELECT day
        ,SUM(total_bill) AS sales
  FROM tips
  GROUP BY day
)

SELECT *
FROM daily

 

 

 

6. 각 영수 금액이, 요일별 매출액에서 차지하는 비율을 계산하기

(비율은 반올림하여 소수점 둘째자리까지만 출력하고, 영수 금액이 높은 것부터 출력)

WITH daily AS(
  SELECT day
        ,SUM(total_bill) AS sales
  FROM tips
  GROUP BY day
)

SELECT tips.day
      ,tips.total_bill
      ,ROUND(tips.total_bill * 100 / daily.sales,2) as pct
FROM tips
    INNER JOIN daily ON tips.day = daily.day
ORDER BY total_bill desc