스터디 노트 51일차

USE Mydb;

select City , count(StoreNumber) high_sale_store_cnt
from ( select City
, StoreNumber
, SUM(SaleDollars) sales_sum
, row_number() over(order by SUM(SaleDollars) DESC ) store_rank
from city_data
group by 1,2
) t1
where t1.store_rank <= 20
group by t1.City
order by high_sale_store_cnt DESC;

-- with 절

with store_rank_table as (
select City
, StoreNumber
, SUM(SaleDollars) sales_sum
, row_number() over(order by SUM(SaleDollars) DESC ) store_rank
from city_data
group by 1,2
)
select City , count(StoreNumber) high_sale_store_cnt
from store_rank_table
where store_rank <= 20
group by City
order by high_sale_store_cnt DESC;

-- 매출 상위 20위권 스토어의 비중이 많은 지역 분석
with store_rank_table as (
select City
		, StoreNumber
		, SUM(SaleDollars) sales_sum
		, row_number() over(order by SUM(SaleDollars) DESC ) store_rank
from city_data
group by 1,2

)
select City
, COUNT(DISTINCT StoreNumber) total_store_cnt
, SUM(CASE WHEN store_rank <= 20 THEN 1 ELSE 0 END) store_cnt_under_20
, round(SUM(CASE WHEN store_rank <= 20 THEN 1 ELSE 0 END) / COUNT(DISTINCT StoreNumber) * 100 , 2) store_ratio_under_20
from store_rank_table
group by City
;

-- temporary table

CREATE TEMPORARY TABLE store_rank_table
select City
, StoreNumber
, SUM(SaleDollars) sales_sum
, row_number() over(order by SUM(SaleDollars) DESC ) store_rank
from city_data
group by 1,2
;

select *
from store_rank_table
;

-- 20위 안에 드는 점포의 매출 편차

select
round(min(sales_sum), 2) min_sales
, round(max(sales_sum) , 2) max_sales
, round(std(sales_sum) , 2) std_sales
, round(max(sales_sum) - min(sales_sum) , 2) sales_gap
from store_rank_table
where store_rank <= 20
;

select * , round(sales_sum) sales_sum
from store_rank_table
where store_rank <= 20
;

-- 결론
-- 안정적인 수입을 얻고싶다
-- 20위권 순위 비율이 가장 높고 평균 매출이 가장 높은 MOUNT VERNON을 추천
-- 공격적으로 사업해서 무조건 1위를 하겠다
-- 매출이 높은 store가 많은 DES MOINES를 추천

use mydb;

select
t2.ItemDescription
, round(SUM(SaleDollars)) total_sales
, rank() over(ORDER BY sum(SaleDollars) DESC) item_rank
from city_data t1
left join item_meta t2
on t1.ItemNumber = t2.ItemNumber
where
t1.City = 'MOUNT VERNON' OR t1.City = 'DES MOINES'
group by t2.ItemDescription
;

-- 우리가 선택한 2개의 지역은 유사한 주류 순위를 가졌는가 분석

select
t1.City
, t2.ItemDescription
, SUM(SaleDollars) total_sum
, rank() over(partition by t1.City order by sum(SaleDollars) DESC ) item_rank
from city_data t1
left join item_meta t2
on t1.ItemNumber = t2.ItemNumber
where
t1.City = 'MOUNT VERNON' OR t1.City = 'DES MOINES'
group by t1.City , t2.ItemDescription
order by item_rank
;

-- 각 지역별로 몇개의 아이템을 팔고 있는가 분석 ( MOUNT ~ : 1240개 , DES ~ : 3606개 )

select t1.City , Count(DISTINCT t1.ItemNumber)
from city_data t1
where t1.City = 'MOUNT VERNON' OR t1.City = 'DES MOINES'
group by t1.City
;

-- 각 아이템들이 매출에 차지하는 비중 분석
-- 1. city의 총 매출이 얼마인지
-- 2. city , item별로 매출 , 순위

WITH a as
(select
t1.City
, t2.ItemDescription
, sum(SaleDollars) item_total_sales
, rank() over(partition by t1.City order by sum(t1.SaleDollars) DESC ) Item_rank
from city_data t1
left join item_meta t2
on t1.ItemNumber = t2.ItemNumber
where t1.City = 'MOUNT VERNON' OR t1.City = 'DES MOINES'
group by t1.City , t2.ItemDescription
)

select
a.City
, a.ItemDescription
, a.Item_rank
, b.total_sales
, round(a.item_total_sales / b.total_sales * 100 , 2) item_sales_ratio
from a
left join
(select City, sum(SaleDollars) total_sales
from city_data
group by City
) b
on a.City = b.City
order by item_rank, City;
;

-- 10위권까지의 판매량 비중을 합쳐본다면?
-- 1. 아이템의 매출량과 순위
-- 2. 지역별 총 매출량alter
-- 순위에서 10위권 까지만 사용을 해서 총 매출량 대비 10위권 까지의 아이템 매출합 나눠주면 -> 비중

with item_sales_table as(

select City , ItemNumber , sum(SaleDollars) item_total_sales
	, rank() over(partition by City order by sum(SaleDollars) DESC ) item_rank
from city_data
where City = 'MOUNT VERNON' OR City = 'DES MOINES'
group by City, ItemNumber

)
select
item_sales_table.City
, round(sum(item_sales_table.item_total_sales / b.total_sales * 100) , 1) item_sales_ratio_sum
from item_sales_table
left join (
select City , sum(SaleDollars) total_sales
from city_data
group by City
) b
on item_sales_table.City = b.City
where item_rank <= 10
group by item_sales_table.City
;

-Tableau를 사용하여 시각화하기
2022년 매출동향, 주 별 도매상 및 아이템 수 , 누적 매출 현황(running사용)
,전월 대비 매출 성장 정도 , 아이템 매출 순위 Top20 , 카테고리 매출 순위
, 도매상 매출 순위(도구설명에 아이템 매출 그래프 삽입) , 아이템 매출 그래프