[Solvesql] 실전반 무료 문제 풀이 (SQLite)

 

난이도 1. 첫 주문과 마지막 주문

select STRFTIME('%Y-%m-%d',MIN(order_purchase_timestamp)) AS first_order_date,
      STRFTIME('%Y-%m-%d',MAX(order_purchase_timestamp)) AS last_order_date
FROM olist_orders_dataset;

 

SQLITE는 문법이 다르구나. MYSQL에서는 DATEFORMAT을 SQLITE에서는 STARFTIME을 사용한다.

 

 

난이도 1. 많이 주문한 테이블 찾기

SELECT *
FROM tips A
WHERE total_bill > (SELECT AVG(total_bill) AS total_bill FROM tips);

 

 

난이도 1. 레스토랑의 일일 매출 평균액 계산하기

SELECT ROUND(AVG(avg_sales), 2) AS avg_sales
FROM (SELECT SUM(total_bill) AS avg_sales
FROM tips
GROUP BY day) A;

 

 

난이도 2. 레스토랑의 대목

SELECT *
FROM tips
WHERE day IN (SELECT day
		FROM tips
		GROUP BY day
		HAVING SUM(total_bill) >= 1500);

 

 

난이도 2. 레스토랑의 요일별 VIP

SELECT *
FROM tips
WHERE (total_bill, day) IN (SELECT MAX(total_bill) AS total_bill, day
				FROM tips
				GROUP BY day);

 

 

난이도 3. 배송 예정일 예측 성공과 실패 

SELECT DATE(order_purchase_timestamp) AS 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
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);