02/21 제출
시계열 일/주/월/분기별 매출액 및 주문 건수
•
일별 매출
select date_trunc('day', order_date)::date as day
, sum(amount) as sum_amount, count(distinct a.order_id) as daily_ord_cnt
from nw.orders a
join nw.order_items b on a.order_id = b.order_id
group by date_trunc('day', order_date)::date
order by 1;
SQL
복사
SQL 쿼리 작성
위 시각화 차트 구성
월별 매출
•
매출, 개수
select date_trunc('month', order_date)::date as month
, sum(amount) as sum_amount, count(distinct a.order_id) as monthly_ord_cnt
from nw.orders a
join nw.order_items b on a.order_id = b.order_id
group by date_trunc('month', order_date)::date
order by 1;
SQL
복사
SQL 쿼리 작성
위 시각화 차트 구성
분기별
•
매출, 개수, 상품별
select date_trunc('quarter', order_date)::date as quarter, c.product_name
, sum(amount) as sum_amount, count(distinct a.order_id) as quarterly_ord_cnt
from nw.orders a
join nw.order_items b on a.order_id = b.order_id
join nw.products c on b.product_id = c.product_id
group by date_trunc('quarter', order_date)::date, c.product_name
order by quarter;
SQL
복사
SQL 쿼리 작성
시각화 차트 구성 (자유롭게)
월별 상품카테고리별 매출액 및 주문 건수, 월 전체 매출액 대비 비율
•
step 1: 상품 카테고리 별 월별 매출액 추출
•
step 2: step 1의 집합에서 전체 매출액을 analytic으로 구한 뒤에 매출액 비율 계산.
SELECT
DATE_TRUNC('month', order_date)::date AS month,
c.category_name,
SUM(amount) AS category_sales_amount,
COUNT(DISTINCT a.order_id) AS order_count
FROM
nw.orders a
JOIN nw.order_items oi ON a.order_id = oi.order_id
JOIN nw.products p ON oi.product_id = p.product_id
JOIN nw.categories c ON p.category_id = c.category_id
GROUP BY
DATE_TRUNC('month', order_date)::date, c.category_name;
SQL
복사
SQL 쿼리 작성
위 시각화 차트 구성
상품별 전체 매출액 및 해당 상품 카테고리 전체 매출액 대비 비율, 해당 상품카테고리에서 매출 순위
•
step 1: 상품별 전체 매출액을 구함
•
step 2: step 1의 집합에서 상품 카테고리별 전체 매출액을 구하고, 비율과 매출 순위를 계산.
SQL 쿼리 작성
시각화 차트 구성 (자유롭게)
동년도 월별 누적 매출 및 동일 분기 월별 누적 매출
•
step 1: 월별 매출액을 구한다
•
step 2: 월별 매출액 집합에 동일 년도의 월별 누적 매출과 동일 분기의 월별 누적 매출을 구함.
WITH MonthlySales AS (
SELECT
DATE_TRUNC('month', order_date)::date AS month,
EXTRACT(YEAR FROM o.order_date) AS year,
EXTRACT(QUARTER FROM o.order_date) AS quarter,
SUM(oi.amount) AS sales_amount
FROM
nw.orders o
JOIN nw.order_items oi ON o.order_id = oi.order_id
GROUP BY
1, 2, 3
),
CumulativeSales AS (
SELECT
month,
year,
quarter,
sales_amount,
SUM(sales_amount) OVER (PARTITION BY year ORDER BY month) AS cumulative_yearly_sales,
SUM(sales_amount) OVER (PARTITION BY year, quarter ORDER BY month) AS cumulative_quarterly_sales
FROM
MonthlySales
)
SELECT
month,
sales_amount,
cumulative_yearly_sales,
cumulative_quarterly_sales
FROM
CumulativeSales
ORDER BY
month;
SQL
복사
SQL 쿼리 작성
시각화 차트 구성 (월별 라인차트)
5일 이동 평균 매출액 구하기. (매출액의 경우 주로 1주일 이동 평균 매출을 구하나 데이터가 토,일 매출이 없음.)
SQL 쿼리 작성
시각화 차트 구성 (날짜별 라인차트)