기억보단 기록을 위한 공부

12. 기출 예상 코딩 테스트 SQL 실전 대비 — 기출 예상 코딩 테스트 1 정리 1. 신규 가입자 구매 전환율 계산문제:2024년 1월에 신규로 회원가입을 하고, 가입일로부터 3일 이내에 구매를 완료한 회원 수를 추출하시오.(컬럼명: MEM_CNT)SELECT COUNT(DISTINCT A.MEM_NO) AS MEM_CNTFROM CUSTOMER AS AINNER JOIN SALE AS B ON A.MEM_NO = B.MEM_NOWHERE DATE_FORMAT(JOIN_DATE, '%Y-%m') = '2024-01' AND DATEDIFF(ORDER_DATE, JOIN_DATE) 핵심 포인트신규 가입자 전환율 = “가입 후 구매까지 걸린 기간 ≤ 3일”DATEDIFF()로 기간 계산, JOIN..

11. 캐글 데이터 분석 프로젝트 SQL을 활용한 상품 카테고리별 리뷰·배송 분석 1. 카테고리별 매출 및 리뷰 점수 분석 SELECT pct.product_category_name, SUM(IFNULL(oi.price,0)) + SUM(IFNULL(oi.freight_value,0)) AS sale_amt, COUNT(DISTINCT o.order_id) AS order_cnt, COUNT(DISTINCT customer_unique_id) AS customer_cnt, AVG(review_score) AS review_score_avgFROM olist_orders_dataset AS oINNER JOIN olist_order_items_dataset A..

11. 캐글 데이터 분석 프로젝트 SQL로 수행한 카테고리별 구매주기 분석 1. 분석 개요분석 목표카테고리별 평균 구매주기를 계산하여, 재구매 마케팅 타이밍 도출카테고리별 매출 기여도를 함께 분석해 우선순위 마케팅 타깃 파악회원별 구매주기를 병행 분석하여 고객 기반 리텐션 전략 수립구매주기 정의 (CIRCLE)(최근구매일자 − 최초구매일자) ÷ (구매횟수 − 1) 예시: 2025.01.01 / 2025.01.10 / 2025.01.31 구매 → (31−1)/(3−1) = 15일 주기로 구매 2. 카테고리별 구매주기 계산1. 카테고리별 데이터 마트 생성 CREATE TABLE ORDER_INFO_BY_CATEGORY AS (SELECT pct.product_category_name, MIN(o..

11. 캐글 데이터 분석 프로젝트 RFM 고객 세분화 및 지역별 배송 분석 1. 지역별 배송 분석 (1) 셀러 1인당 고객 수셀러 한 명당 고객 수가 높은 지역: 'PA', 'MA', 'PI', 'MT', 'PE', 'BA', 'SE'셀러가 거의 없는 지역: 'AL', 'AP', 'RR', 'TO'SELECT c.customer_state, customer_cnt, seller_cnt, customer_cnt / seller_cnt AS number_customers_per_sellerFROM ( SELECT customer_state, COUNT(DISTINCT customer_unique_id) AS customer_cnt FROM olist_customers_dataset GROUP..

11. 캐글 데이터 분석 프로젝트 SQL로 구현하는 RFM 고객 세분화 분석 1. RFM 정의 지표 의미 계산 기준 Recency (최근성)고객의 마지막 구매일로부터 경과한 일수DATEDIFF('기준일자', MAX(order_purchase_timestamp))Frequency (빈도)특정 기간 내 고객의 총 주문 횟수COUNT(DISTINCT order_id)Monetary (금액)고객의 총 구매 금액(상품+배송비)SUM(price + freight_value)💡 이번 분석에서는 기준일자 '2018-10-17'을 사용하여 Recency 계산 2. RFM 테이블 생성olist_orders_dataset, olist_order_items_dataset, olist_customers_dataset을..

11. 캐글 데이터 분석 프로젝트 캐글(Kaggle) 브라질 E-Commerce 데이터 분석 프로젝트 1. 프로젝트 개요데이터셋: Olist Store (브라질 최대 오픈마켓 플랫폼)분석 목적:테이블 간 관계 파악주문·상품·리뷰·결제 데이터 연결 구조 이해주문/매출/배송/지역별 인사이트 도출데이터베이스명: Brazilian구성 테이블:주문: olist_orders_dataset주문 상세: olist_order_items_dataset고객: olist_customers_dataset상품: olist_products_dataset셀러: olist_sellers_dataset결제: olist_order_payments_dataset리뷰: olist_order_reviews_dataset번역 테이블: pro..

10. 저장 프로시져 SQL 저장 프로시저 (Stored Procedure) 1. 저장 프로시저란?정의: 여러 SQL 명령문을 하나로 묶은 재사용 가능한 SQL 블록목적: 자주 반복되는 쿼리나 복잡한 데이터 처리 로직을 효율적으로 관리특징:DELIMITER를 사용해 여러 명령문을 하나로 정의CALL 명령어로 실행필요 시 DROP PROCEDURE로 삭제 가능💡 기본 예시DELIMITER //CREATE PROCEDURE CUSTOMER_GENDER_PROCEDURE(IN INPUT_A VARCHAR(20))BEGIN SELECT * FROM CUSTOMER WHERE GENDER = INPUT_A;END //DELIMITER ;CALL CUSTOMER_GENDER_PROCEDURE('M');→ ..

9. 뷰 테이블 SQL 뷰 테이블 (View Table) 1. 뷰 테이블의 개념뷰(View) 는 실제 데이터를 저장하지 않으며, SELECT 쿼리 결과를 가상 테이블 형태로 정의한 객체다.복잡한 조인(Join)이나 서브쿼리를 자주 사용할 때, 이를 하나의 논리적 테이블로 재사용할 수 있도록 만들어준다.뷰는 단순히 데이터를 보여주는 역할만 하므로, 데이터의 보안·관리 효율성을 높이는 데도 유용하다.예시CREATE VIEW CUSTOMER_INFO AS (SELECT A.*, ORDER_DATE, ORDER_DETAIL_NO, QTY, PRICE, QTY * PRICE AS SALES_AMT FROM CUSTOMER AS A LEFT JOIN SALE AS B ON A.MEM_NO = B.MEM_NO ..

8. 윈도우 함수 SQL 윈도우 함수 1. 윈도우 함수의 기본 개념SQL 함수는 다음 세 가지로 구분된다.구분설명단일행 함수각 행마다 독립적으로 결과 반환 (UPPER, DATE_FORMAT 등)복수행 함수(집계)여러 행을 하나의 값으로 요약 (SUM, AVG, COUNT 등)윈도우 함수그룹 내 연산을 수행하지만 각 행별로 결과를 반환, 원본 데이터 유지윈도우 함수는 다음과 같은 구문 구조를 가진다:함수명(컬럼) OVER ( [PARTITION BY 컬럼] ORDER BY 컬럼 ASC/DESC )​ ORDER BY: 순서를 지정 (행 간의 관계 정의)PARTITION BY: 그룹을 나누어 그룹별 계산 수행예시SELECT DENSE_RANK() OVER(ORDER BY ORDER_DATE ASC) AS ..

7. 서브쿼리 1. 서브쿼리의 종류 구분 위치 설명 SELECT절 서브쿼리SELECT 뒤계산된 값을 컬럼처럼 반환. 결과가 반드시 1행 이하이어야 함FROM절 서브쿼리FROM 뒤쿼리 결과를 임시 테이블처럼 사용 (AS 별칭 필수)WHERE절 서브쿼리WHERE 뒤조건절에 다른 쿼리 결과를 삽입하여 필터링 수행 예시SELECT A.*, (SELECT GENDER FROM CUSTOMER B WHERE A.MEM_NO = B.MEM_NO) AS GENDERFROM SALE AS A;→ 고객 테이블에서 성별을 불러와 판매 데이터에 추가 WHERE절 서브쿼리 예시: SELECT *FROM SALEWHERE MEM_NO IN (SELECT MEM_NO FROM CUSTOMER WHERE JOIN_DATE = ..