보단잉의 블로그

온라인 쇼핑몰의 월 별 매출액 집계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의 개수를 셈으로써 각 카테고리별 ..

제목이 모음으로 끝나지 않는 영화SELECT title FROM filmWHERE ( rating = 'R' OR rating = 'NC-17' ) AND ( title NOT LIKE '%A' AND title NOT LIKE '%E' AND title NOT LIKE '%I' AND title NOT LIKE '%O' AND title NOT LIKE '%U' ) 언더스코어(_)가 포함되지 않은 데이터 찾기_에는 이스케이프 문자(\)를 추가해줘야 하는데, SQLite는 추가로 ESCAPE '\'를 붙여줘야 한다고 한다.SELECT DISTINCT(page_location) AS `page_location` FROM gaWHERE page_location N..

크리스마스 게임 찾기SELECT game_id, name, year FROM gamesWHERE name LIKE '%Christmas%' OR name LIKE '%Santa%' 펭귄 조사하기SELECT DISTINCT(species), island FROM penguinsORDER BY island ASC, species ASC 지자체별 따릉이 정류소 개수 세기SELECT local, COUNT(*) as `num_stations` FROM stationGROUP BY localORDER BY `num_stations` ASC 메리 크리스마스 2024SELECT 'Merry Christmas!'

레스토랑의 대목SELECT A.total_bill, A.tip, A.sex, A.smoker, A.day, A.time, A.size FROM tips AS A JOIN ( SELECT day, SUM(total_bill) AS `sales` FROM tips GROUP BY day HAVING `sales` >= 1500) AS B ON A.day = B.day 레스토랑의 요일별 VIPSELECT A.total_bill, A.tip, A.sex, A.smoker, A.day, A.time, A.size FROM tips AS A JOIN ( SELECT MAX(total_bill) as `max`, day FROM tips GROUP BY day) AS B ON (A.day = B.day) A..

첫 주문과 마지막 주문DATE()를 사용하면 yyyy-MM-dd 형태로 변환된다.SELECT DATE(MIN(order_purchase_timestamp)) AS `first_order_date`,DATE(MAX(order_purchase_timestamp)) AS `last_order_date`FROM olist_orders_datasetORDER BY order_purchase_timestamp 많이 주문한 테이블 찾기SELECT * FROM tipsWHERE total_bill > (SELECT AVG(total_bill) FROM tips) 레스토랑의 일일 평균 매출액 계산하기SELECT ROUND(AVG(`sales`), 2) AS `avg_sales` FROM ( SELECT day, SUM..