[SQL] Apple 소매 판매 분석 쿼리

[SQL] Apple 소매 판매 분석 쿼리

2025. 5. 22. 20:01ㆍ카테고리 없음

이번주에는 pcsql 과 sqld 자격증 시험 준비를 위해서 쿼리문 실습과 문제풀이를 통해 sql 데이터 분석을 진행하였다.

애플 소매 판매 데이터 분석을 위해 쿼리문을 통해 테이블을 생성하였다.

데이터

Dataset.zip

12.39MB

테이블 생성

1. stores 테이블: 매장 정보 관리

create table stores(
  store_id varchar(10) primary key,
  store_name varchar(30),
  city varchar(30),
  country varchar(30)
);

stores 테이블은 제품이 판매되는 매장의 정보를 저장한다. 매장 고유 식별자인 store_id를 기본키로 사용하며, 매장 이름(store_name), 도시(city), 국가(country) 등의 정보를 함께 관리한다.

2. category 테이블: 제품 카테고리 관리

create table category(
  category_id varchar(10) primary key,
  category_name varchar(30)
);

제품을 분류하기 위해 category 테이블을 설계하였다. category_id는 카테고리의 고유 ID이며, category_name은 카테고리명을 나타낸다. 예를 들어, ‘전자제품’, ‘가전제품’과 같은 항목이 될 수 있다.

3. products 테이블: 제품 정보 관리

create table products(
  product_id varchar(10) primary key,
  product_name varchar(35),
  category_id varchar(10),
  launch_date date,
  price float,
  constraint fk_category foreign key (category_id) references category(category_id)
);

products 테이블은 각 제품의 상세 정보를 담는다. 제품 ID(product_id)를 기본키로 하고, 이름, 출시일, 가격 정보를 함께 저장한다. 또한 category_id를 외래키로 지정해 category 테이블과의 연관 관계를 형성하여 제품이 어느 카테고리에 속하는지 확인할 수 있다.

4. sales 테이블: 판매 이력 관리

create table sales(
  sale_id varchar(10) primary key,
  sale_date date,
  store_id varchar(10),
  product_id varchar(10),
  quantity int,
  constraint fk_store foreign key (store_id) references stores(store_id),
  constraint fk_product foreign key (product_id) references products(product_id)
);

sales 테이블은 제품의 실제 판매 이력을 기록한다. 어떤 제품이, 어떤 매장에서, 언제, 몇 개 팔렸는지를 기록하며, store_id와 product_id는 각각 stores, products 테이블과 외래키 관계를 맺는다.

5. warranty 테이블: A/S 이력 관리

create table warranty(
  claim_id varchar(10) primary key,
  claim_date date,
  sale_id varchar(10),
  repair_status varchar(20),
  constraint fk_sale foreign key (sale_id) references sales(sale_id)
);

warranty 테이블은 제품에 대한 A/S 이력을 추적한다. 고객이 A/S를 요청한 경우, sale_id를 통해 어떤 판매 건에 대한 클레임인지 확인할 수 있다. 수리 상태(repair_status)는 '처리중', '완료', '불가' 등의 값을 가질 수 있다.

데이터 탐색 쿼리문

1. --To view Data

select * from category;
select * from products;
select * from sales;
select * from stores;
select * from warranty;

분석 전 전체 테이블 내용을 미리 조회
테이블 구조 및 샘플 데이터 확인 목적
이후 EDA와 조인 로직 설계를 위한 기초 작업

2. --Exploratory Data Analysis

select distinct repair_status from warranty;
select distinct store_name from stores;
select distinct category_name from category;
select distinct product_name from products;
select count(*) from sales;

distinct로 각 테이블의 범주형 데이터 분포 파악
count(*)로 데이터의 총 규모 확인
데이터 탐색의 시작단계로, 컬럼별 고유값 확인은 중요

3. --Query Performance Analysis

explain Analyze select * from sales where product_id ='P-40';

쿼리 실행 계획 및 성능 측정 (Planning Time, Execution Time)
인덱스 전 성능 기준값 확보

4. --Improve Query Performance

create index sales_product_id on sales(product_id);
select * from sales where product_id ='P-40';

create index sales_store_id on sales(store_id);
create index sales_quantity on sales(quantity);
create index sale_date on sales(sale_date);
create index sales_product_id_store_id on sales(product_id, store_id);

WHERE, JOIN, ORDER BY 등에 자주 쓰이는 컬럼에 인덱스 부여
다중 컬럼 인덱스(product_id, store_id)는 복합 조건에 유리
인덱스 생성 후 쿼리 성능이 극적으로 향상됨

5. Find number of stores in each country

select 
country,
count(store_id) as Total_Stores
from stores
group by country
order by count(store_id) desc;

국가별 매장 수 집계
group by + count()는 지역별 분포 파악에 유용

6. Calculate the total number of units sold by each store

select
s.store_id,
st.store_name,
sum(s.quantity) as total_units_sold
from sales as s
join stores as st
on st.store_id = s.store_id
group by 1, 2
order by 3 desc;

판매량 분석의 기본 → 매장별 누적 수량
JOIN + GROUP BY를 활용해 다차원 집계 가능

7. Identify how many sales occurred in December 2023

select 
count(*) as total_sales
from sales
where to_char(sale_date, 'MM-YYYY') = '12-2023';

특정 월 기준 필터링 → to_char로 날짜 형식 문자열화
월 단위 시계열 분석에 적합

8. Determine how many stores have never had a warranty claim filed

select count(*) from stores
where store_id not in (
  select distinct store_id from sales as s 
  right join warranty as w on s.sale_id = w.sale_id
);

A/S 경험이 없는 매장 추적
NOT IN + JOIN으로 부정 조건 처리

9. Calculate the percentage of warranty claims marked as "Rejected"

select 
ROUND(
  count(claim_id)/(select count(*) from warranty)::numeric * 100, 2) as rejected_percentage
from warranty
where repair_status = 'Rejected';

전체 A/S 대비 '거절' 비율 산출
::numeric 캐스팅 → 정밀한 비율 계산을 위한 처리

10. Identify which store had the highest total units sold in the last year

select 
s.store_id,
st.store_name,
sum(s.quantity)
from sales as s
join stores as st
on s.store_id = st.store_id
where sale_date >= (select current_date - interval '1 year')
group by 1, 2
order by 3 desc
limit 1;

최근 1년 기준 상위 매장 확인
interval로 날짜 필터링 + limit 1로 1등만 추출

11. For each store, identify the best-selling day based on highest quantity sold

select * from
(
	select
    store_id,
    to_char(sale_date, 'day') as day_name,
    sum(quantity) as Total_Quantity_sold,
    rank() over(partition by store_id order by sum(quantity) desc) as rank
    from sales
    group by 1,2
) as tb1
where rank = 1

매장별 요일 단위 판매 분석
rank() 윈도우 함수로 그룹 내 정렬 → 최상위 추출
요일별 데이터의 주간 트렌드 파악 가능

12. Identify the least selling product in each country

with product_rank as (
  select 
    st.country,
    p.product_name,
    sum(s.quantity),
    rank() over(partition by st.country order by sum(s.quantity)) as leaft_sold_product
  from sales as s
  join stores as st on s.store_id = st.store_id
  join products as p on s.product_id = p.product_id
  group by 1, 2
)
select * from product_rank where leaft_sold_product = 1;

rank()로 국가별 최저 판매량 제품 추출
CTE로 복잡한 순위 계산을 분리
다국가 시장 분석에 효과적

13. Analyze the year-by-year growth ratio for each store

with yearly_sales as (
  select
    S.store_id,
    st.store_name,
    extract(year from sale_date) as Year_of_sale,
    sum(p.price * s.quantity) as total_sale
  from sales as s
  join products as p on s.product_id = p.product_id
  join stores as st on st.store_id = s.store_id
  group by 1, 2, 3
),
growth_ratio as (
  select
    store_name,
    year_of_sale,
    lag(total_sale, 1) over(partition by store_name order by year_of_sale) as last_year_sale,
    total_sale as current_year_sale
  from yearly_sales
)
select
  store_name,
  year_of_sale,
  last_year_sale,
  current_year_sale,
  round((current_year_sale - last_year_sale)::numeric / last_year_sale::numeric * 100, 2) as growth_ratio_YOY
from growth_ratio
where last_year_sale is not null;

lag() 함수로 전년도 매출 참조
YOY 성장률 계산 → 전년도 대비 증감
CTE 두 개를 활용해 단계별 가독성과 유지 보수성 향상

14. Identify the store with the highest percentage of "Completed" claims

with completed as (
  select
    s.store_id,
    count(w.claim_id) as completed
  from sales as s
  right join warranty as w on s.sale_id = w.sale_id
  where w.repair_status = 'Completed'
  group by 1
), 
total_repaired as (
  select
    s.store_id,
    count(w.claim_id) as total_repaired
  from sales as s
  right join warranty as w on s.sale_id = w.sale_id
  group by 1
)
select 
  tr.store_id,
  tr.total_repaired,
  c.completed,
  ROUND(c.completed::numeric / tr.total_repaired::numeric * 100, 2) as percentage_of_completed
from completed as c
join total_repaired as tr on c.store_id = tr.store_id
order by 4 desc;

A/S 완료율 계산 → completed / total
분자, 분모를 각각 별도 CTE로 처리해 깔끔한 분리
::numeric을 활용한 소수점 정확도 유지

15. Monthly running total of sales over 4 years

with monthly_sales as (
  select
    store_id,
    extract(year from sale_date) as year,
    extract(month from sale_date) as month,
    sum(p.price * s.quantity) as Total_profit
  from sales as s
  join products as p on s.product_id = p.product_id
  group by 1, 2, 3
)
select
  store_id, 
  year, 
  month, 
  Total_profit, 
  sum(total_profit) over(partition by store_id order by year, month) as Running_total
from monthly_sales;

sum() over(...)로 누적합(Running Total) 구현
월 단위 시계열 누적 추이 분석 가능
전년비, 추세 분석 등에 활용 가능

ParkS2.tistory