BigQuery 최종 문제

문제

1. 사용자별 총 주문 금액 계산

# 문제 : ### **1. 사용자별 총 주문 금액 계산**
-- - 각 사용자의 총 주문 횟수와 총 지출 금액을 확인할 수 있는 쿼리를 작성해주세요
# 쿼리를 작성하는 목표, 확인할 지표 : 사용자 별 / 총 주문 횟수 / 총 지출 금액
# 쿼리 계산 방법 : group by user_id / count order_id / sum total_amount
# 데이터의 기간 : 
# 사용할 테이블 : orders
# Join KEY : 
# 데이터 특징 : 
# 작성 흐름 : FROM - JOIN - WHERE - GROUP BY - HAVING - SELECT- ORDER BY - LIMIT 

SELECT
  user_id,
  COUNT(order_id) AS order_cnt,
  SUM(total_amount) AS total_sum
FROM `ecommerce_advanced.orders`
GROUP BY
  user_id
ORDER BY user_id ASC

2. 카테고리별 제품 판매량 및 매출액 분석

# 문제 : 각 카테고리별 판매된 제품의 총 수량과 총 매출액을 확인할 수 있는 쿼리를 작성해주세요
# 쿼리를 작성하는 목표, 확인할 지표 : 카테고리 별 / 총 수량 / 총 매출액
# 쿼리 계산 방법 : 카테고리id <- 프로덕트id <- order_items <- orders / count order_id / sum total_amount
# 데이터의 기간 : 
# 사용할 테이블 : categories, products, order_items, orders
# Join KEY : categories.category_id = products.category_id / products.product_id = order_items.product_id 
#            / order_items.order_id = orders.order_id
# 데이터 특징 : 
# 작성 흐름 : FROM - JOIN - WHERE - GROUP BY - HAVING - SELECT- ORDER BY - LIMIT 

SELECT
 cat.category_id,
 cat.category_name,
 COUNT(ord.order_id) AS order_cnt,
 SUM(ord.total_amount) AS order_total
FROM ecommerce_advanced.categories AS cat
LEFT JOIN ecommerce_advanced.products AS pro
ON cat.category_id = pro.category_id
LEFT JOIN ecommerce_advanced.order_items AS oi
ON pro.product_id = oi.product_id
LEFT JOIN ecommerce_advanced.orders AS ord
ON oi.order_id = ord.order_id
GROUP BY
  cat.category_id,
  cat.category_name
ORDER BY cat.category_id ASC

3. 베스트셀러 제품 TOP 5 조회

# 문제 : 판매 수량 기준으로 인기 있는 상위 5개 제품을 조회할 수 있는 쿼리를 작성해주세요
# 쿼리를 작성하는 목표, 확인할 지표 : 판매 수량 기준 / 상위 5개 / 제품 뭔데?
# 쿼리 계산 방법 : ORDER BY 판매 수량 DESC / LIMIT 5 / 제품 이름 => order_id 갯수 = 판매수량
# 데이터의 기간 : 
# 사용할 테이블 : products, order_items
# Join KEY : products.product_id = order_items.product_id
# 데이터 특징 : 
# 작성 흐름 : FROM - JOIN - WHERE - GROUP BY - HAVING - SELECT- ORDER BY - LIMIT 

SELECT
  pro.product_name,
  COUNT(oi.order_id) AS sell_cnt
FROM `ecommerce_advanced.products` AS pro
LEFT JOIN ecommerce_advanced.order_items AS oi
ON pro.product_id = oi.product_id
GROUP BY
  pro.product_name
ORDER BY COUNT(oi.order_id) DESC
LIMIT 5

4. 특정 기간 동안의 매출 추이 분석

# 문제 : 월별 총 매출액과 주문 건수를 조회할 수 있는 쿼리를 작성해주세요
# 쿼리를 작성하는 목표, 확인할 지표 : 월별 / 총 매출액 / 총 주문건수
# 쿼리 계산 방법 : 월별 = DATA_TRUNC MONTH => 년도가 다를 수도 있으니까! extract는 아니다! / sum total_amount / count order_id
# 데이터의 기간 : 
# 사용할 테이블 : orders
# Join KEY : 
# 데이터 특징 : 
# 작성 흐름 : FROM - JOIN - WHERE - GROUP BY - HAVING - SELECT- ORDER BY - LIMIT 

WITH base AS(
  SELECT
  DATETIME(order_date, "Asia/Seoul") AS order_datetime,
  total_amount,
  order_id
FROM `ecommerce_advanced.orders`
)

SELECT
  DATE_TRUNC(order_datetime, MONTH) AS order_month,
  SUM(total_amount) AS total_sales_amount,
  COUNT(order_id) AS order_cnt
FROM base
GROUP BY
  DATE_TRUNC(order_datetime, MONTH)
ORDER BY DATE_TRUNC(order_datetime, MONTH) ASC

5. 고객 세그먼트별 구매 패턴 분석

# 문제 : 회원 유형(일반/프리미엄)별 평균 주문 금액과 주문 빈도를 확인하는 쿼리를 작성해주세요
# 쿼리를 작성하는 목표, 확인할 지표 : 유형 별 / 평균 주문금액 / 주문 빈도 = 주문 횟수!
# 쿼리 계산 방법 : GROUP BY user_type (REGULAR / PREMIUM) / AVG total_amount / count order_id
# 데이터의 기간 : 
# 사용할 테이블 : users, orders
# Join KEY : user_id
# 데이터 특징 : 
# 작성 흐름 : FROM - JOIN - WHERE - GROUP BY - HAVING - SELECT- ORDER BY - LIMIT 

SELECT
  users.user_type,
  COUNT(orders.order_id) AS order_cnt,
  ROUND(AVG(orders.total_amount), 0) AS average_total
FROM `ecommerce_advanced.users` AS users
LEFT JOIN `ecommerce_advanced.orders` AS orders
ON users.user_id = orders.user_id
GROUP BY
  users.user_type