온라인 쇼핑몰의 월 별 매출액 집계SELECT STRFTIME('%Y-%m', DATE(A.order_date)) AS `order_month`, SUM(CASE WHEN B.order_id NOT LIKE 'C%' THEN B.price * B.quantity ELSE 0 END) AS `ordered_amount`, SUM(CASE WHEN B.order_id LIKE 'C%' THEN B.price * B.quantity ELSE 0 END) AS `canceled_amount`, SUM(B.price * B.quantity) AS `total_amount`FROM orders AJOIN order_items BON A.order_id = B.order_idGROUP BY STRFTIME('%Y-%..

배송 예정일 예측 성공과 실패SELECT DATE(order_purchase_timestamp) AS `purchase_date`, COUNT(DISTINCT(CASE WHEN order_delivered_customer_date order_estimated_delivery_date THEN order_id END)) AS `fail`FROM olist_orders_datasetGROUP BY DATE(order_purchase_timestamp)HAVING CAST(STRFTIME('%Y', DATE(order_purchase_timestamp)) AS INTEGER) = 2017 AND CAST(STRFTIME('%m', DATE(order_purchase_timestamp)) AS INTEGER)..

할부는 몇 개월로 해드릴까요SELECT payment_installments, COUNT(DISTINCT(order_id)) AS `order_count`, MIN(payment_value) AS `min_value`, MAX(payment_value) AS `max_value`, AVG(payment_value) AS `avg_value` FROM olist_order_payments_datasetWHERE payment_type LIKE '%credit_card%'GROUP BY payment_installments 지역별 주문의 특징같은 사람이 여러 번 주문하는 경우가 있어서 WHEN절에서 order_id를 가져오고 DISTINCT로 중복 제거한 후 order_id의 개수를 셈으로써 각 카테고리별 ..