USE mydb;

-- subquery 방법으로 작성
-- Group by 사용

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
order by store_rank
) 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
    -- CASE WHEN (조건)  THEN (조건이 True인 경우의 output) ELSE (False일 경우에 output)
    -- if a:
    -- return a
    -- else b
    -- END
    , ROUND(100 * SUM(CASE WHEN store_rank <= 20 THEN 1 ELSE 0 END) / count(distinct StoreNumber), 2) store_rato_under_20
from store_rank_table
group by city;

CREATE TEMPORARY TABLE store rank data
SELECT
		City
		, StoreNumber
		,SUM(SaleDollars) sales_sum
		,ROW_NUMBER() OVER(ORDER BY SUM(SaleDollars) DESC) store rank
FROM city data
GROUP BY City, StoreNumber;

SELECT *
FROM store rank data;

-- tempor 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;

-- tempor 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;

-- 매출편차가 왜 이렇게 큰지 다시한번 자세히 알아보자
select *, round(sales_sum) sales_sum
from store_rank_table
where store_rank <= 20;

-- 결론
-- 안정적인 수익을 얻고 싶다면?
-- 20위권 순위 비율이 가장 높고 평균 매출이 가장 높은 mount vernon을 추천합니다.
-- 공격적으로 사업을 해서 무조건 1위를 하겠다!!
-- 매출이 높은 store 갯수가 많은 DES MOINES를 추천하겠습니다.

-- 지난시간 recap
-- 5위 중 평균 매출과 매출이 높은 스토어의 비중이 높은 곳은 
-- 'MOUNT VERNON'
-- 공격적인 사업을 해 볼만한 곳은
-- 'DES MOINES'

-- 수익이 좋은 주류 종류는 무엇인가?

-- 매출 1등 주류: 저희가 선택한 2가지 도시
USE mydb;

-- row_number : 순위를 매김, 동일한 값이 나와도 다른 값을 참조해서 순위 나눔 (1,1,2,3 -> 1,2,3,4)
-- rank : 순위를 매김, 동일한 값인 경우 동일한 순위 부여 (1,1,2,3 -> 1,1,3,4)

select
	t2.ItemDescription
    , round(sum(SaleDollars)) totla_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)
    , rank() over(partition by t1.City order by sum(SaleDollars) desc) item_rank -- partition by 로 각 city 의 item 을 그룹핑 해준다.
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
;

-- 각 지역별로 몇개의 아이템을 팔고 있는가?

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;
-- MOUNT VERNON: 262
-- DES MOINES: 690

-- 각 아이템들이 매출에 차지하는 비줄은 어느정도인가?
-- 첫번째: city 총 매출이 얼마인지
-- 두번째: city, item 매출이 얼마, 순위가 얼마 

select
	a.City
    , a.ItemDescription
    , a.item_rank
    , b.total_sales
    , round(100 * a.item_total_sales / b.total_sales, 2) item_sales_ratio
from (
	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
) 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위권까지의 판매량 비중을 합쳐본다면?
-- 첫번째 : 아이템의 매출량과, 순위가 필요
-- 두번째 : 지역별로 아이템 총 매출량
-- 순위에서 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(100 * item_sales_table.item_total_sales / t2.total_sales), 1) item_sales_ratio_sum
    from item_sales_table
    left join (
		select City, sum(SaleDollars) total_sales
        from city_data
        group by City
	) t2
    ON item_sales_table.City = t2.City
    where item_rank <= 10
    group by item_sales_table.City 
    ;

select City, count(distinct VendorNumber)
from rank_table
where item_rank <=10
group by City;

SUM([Sale Dollars]) - LOOKUP(SUM([Sale Dollars]), -1)

{FIXED [City]: sum([Sale Dollars])}

Index()

TOTAL(SUM([Sale Dollars]))

{FIXED [City], [Store Name]: SUM([Sale Dollars])}

{FIXED [City]:sum([Sale Dollars])}

if [도시] == 'Iowa' then 'Iowa' END

<특성(랭킹)>위
도시:	<도시>
총 매출:	<합계(총 매출)>

if [도시 선택] = [City] then True else False end

추천 지역 상세보기 (지역:<매개 변수.도시 선택>)

<시트 이름> (도시: <매개 변수.도시 선택>)

<시트 이름> (도시: <매개 변수.도시 선택>)