인프런 커뮤니티 질문&답변

1번 문제. 주차별 각 카테고리별 평균 할인율이 가장 높았던 기간과 할인율을 구하는 쿼리 작성

WITH transaction_data AS (
  SELECT 111 AS user_id, 1001 AS item_id, 719200 AS actual_price, '01/08/2024 12:00:00' AS transaction_date UNION ALL
  SELECT 111, 2002, 89000, '01/10/2024 12:00:00' UNION ALL
  SELECT 189, 2002, 89000, '01/12/2024 12:00:00' UNION ALL
  SELECT 156, 3002, 459000, '01/15/2024 12:00:00' UNION ALL
  SELECT 121, 1001, 719200, '01/18/2024 12:00:00' UNION ALL
  SELECT 156, 2001, 90300, '01/25/2024 12:00:00' UNION ALL 
  SELECT 145, 3001, 399000, '01/26/2024 12:00:00' UNION ALL
  SELECT 189, 1002, 607200, '01/28/2024 12:00:00' UNION ALL
  SELECT 111, 3001, 399000, '02/05/2024 12:00:00' UNION ALL
  SELECT 178, 1002, 759000, '02/07/2024 12:00:00' UNION ALL
  SELECT 121, 2002, 62300, '02/08/2024 12:00:00' UNION ALL 
  SELECT 156, 1001, 899000, '02/10/2024 12:00:00' UNION ALL
  SELECT 190, 2001, 90300, '02/11/2024 12:00:00' UNION ALL 
  SELECT 189, 2001, 90300, '02/14/2024 12:00:00' UNION ALL 
  SELECT 111, 1002, 759000, '02/15/2024 12:00:00' UNION ALL
  SELECT 156, 3001, 299250, '02/20/2024 12:00:00' UNION ALL
  SELECT 189, 3002, 344250, '02/25/2024 12:00:00' UNION ALL
  SELECT 111, 2001, 90300, '02/28/2024 12:00:00'  
), user_info AS (
  SELECT 111 AS user_id, 'Seoul' AS city, 28 AS age, 'Female' AS gender UNION ALL
  SELECT 121, 'Busan', 35, 'Male' UNION ALL
  SELECT 145, 'Incheon', 42, 'Female' UNION ALL
  SELECT 156, 'Seoul', 31, 'Male' UNION ALL
  SELECT 178, 'Daegu', 25, 'Female' UNION ALL
  SELECT 189, 'Seoul', 39, 'Male' UNION ALL
  SELECT 190, 'Busan', 29, 'Female'
), item_info AS (
  SELECT 1001 AS item_id, 'Electronics' AS category, 'Smartphone' AS item_name, 899000 AS list_price UNION ALL
  SELECT 1002 AS item_id, 'Electronics' AS category, 'Tablet' AS item_name, 759000 AS list_price UNION ALL
  SELECT 2001 AS item_id, 'Fashion' AS category, 'Sneakers' AS item_name, 129000 AS list_price UNION ALL
  SELECT 2002 AS item_id, 'Fashion' AS category, 'Backpack' AS item_name, 89000 AS list_price UNION ALL
  SELECT 3001 AS item_id, 'Home' AS category, 'Coffee Machine' AS item_name, 399000 AS list_price UNION ALL
  SELECT 3002 AS item_id, 'Home' AS category, 'Air Purifier' AS item_name, 459000 AS list_price
),

-- 거래 데이터에 datetime 형식 적용
transaction_base AS (
  SELECT
    user_id,
    item_id,
    actual_price,
    PARSE_DATETIME('%m/%d/%Y %H:%M:%S', transaction_date) AS transaction_datetime
  FROM transaction_data
),

-- 할인율 계산 및 주별 그룹화
discount_data AS (
  SELECT
    transaction_base.user_id,
    transaction_base.item_id,
    100 - (transaction_base.actual_price / item_info.list_price) * 100 AS discount_ratio,
    transaction_base.transaction_datetime,
    item_info.category,
    DATE_TRUNC(transaction_base.transaction_datetime, WEEK) AS transaction_week
  FROM
    transaction_base
  LEFT JOIN
    item_info
  ON
    transaction_base.item_id = item_info.item_id
),

-- 카테고리별 주차 평균 할인율 계산
weekly_discount_avg AS (
  SELECT
    FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', transaction_week) AS transaction_week,
    category,
    AVG(discount_ratio) AS avg_discount_ratio
  FROM
    discount_data
  GROUP BY
    transaction_week, category
),

-- 카테고리별 최고 평균 할인율 주차 및 할인율 찾기
category_discount_rank AS (
  SELECT
    transaction_week,
    category,
    avg_discount_ratio,
    RANK() OVER(PARTITION BY category ORDER BY avg_discount_ratio DESC) AS rank
  FROM
    weekly_discount_avg
)

-- 최종 결과 출력
SELECT
  transaction_week,
  category,
  avg_discount_ratio AS highest_avg_discount_ratio
FROM
  category_discount_rank
WHERE
  rank = 1
ORDER BY
  category;

2번 문제. 2024년 1월에 가장 많은 매출을 기록한 카테고리를 구하는 쿼리 작성

WITH transaction_data AS (
  SELECT 111 AS user_id, 1001 AS item_id, 719200 AS actual_price, '01/08/2024 12:00:00' AS transaction_date UNION ALL
  SELECT 111, 2002, 89000, '01/10/2024 12:00:00' UNION ALL
  SELECT 189, 2002, 89000, '01/12/2024 12:00:00' UNION ALL
  SELECT 156, 3002, 459000, '01/15/2024 12:00:00' UNION ALL
  SELECT 121, 1001, 719200, '01/18/2024 12:00:00' UNION ALL
  SELECT 156, 2001, 90300, '01/25/2024 12:00:00' UNION ALL 
  SELECT 145, 3001, 399000, '01/26/2024 12:00:00' UNION ALL
  SELECT 189, 1002, 607200, '01/28/2024 12:00:00' UNION ALL
  SELECT 111, 3001, 399000, '02/05/2024 12:00:00' UNION ALL
  SELECT 178, 1002, 759000, '02/07/2024 12:00:00' UNION ALL
  SELECT 121, 2002, 62300, '02/08/2024 12:00:00' UNION ALL 
  SELECT 156, 1001, 899000, '02/10/2024 12:00:00' UNION ALL
  SELECT 190, 2001, 90300, '02/11/2024 12:00:00' UNION ALL 
  SELECT 189, 2001, 90300, '02/14/2024 12:00:00' UNION ALL 
  SELECT 111, 1002, 759000, '02/15/2024 12:00:00' UNION ALL
  SELECT 156, 3001, 299250, '02/20/2024 12:00:00' UNION ALL
  SELECT 189, 3002, 344250, '02/25/2024 12:00:00' UNION ALL
  SELECT 111, 2001, 90300, '02/28/2024 12:00:00'  
), user_info AS (
  SELECT 111 AS user_id, 'Seoul' AS city, 28 AS age, 'Female' AS gender UNION ALL
  SELECT 121, 'Busan', 35, 'Male' UNION ALL
  SELECT 145, 'Incheon', 42, 'Female' UNION ALL
  SELECT 156, 'Seoul', 31, 'Male' UNION ALL
  SELECT 178, 'Daegu', 25, 'Female' UNION ALL
  SELECT 189, 'Seoul', 39, 'Male' UNION ALL
  SELECT 190, 'Busan', 29, 'Female'
), item_info AS (
  SELECT 1001 AS item_id, 'Electronics' AS category, 'Smartphone' AS item_name, 899000 AS list_price UNION ALL
  SELECT 1002 AS item_id, 'Electronics' AS category, 'Tablet' AS item_name, 759000 AS list_price UNION ALL
  SELECT 2001 AS item_id, 'Fashion' AS category, 'Sneakers' AS item_name, 129000 AS list_price UNION ALL
  SELECT 2002 AS item_id, 'Fashion' AS category, 'Backpack' AS item_name, 89000 AS list_price UNION ALL
  SELECT 3001 AS item_id, 'Home' AS category, 'Coffee Machine' AS item_name, 399000 AS list_price UNION ALL
  SELECT 3002 AS item_id, 'Home' AS category, 'Air Purifier' AS item_name, 459000 AS list_price
),

-- 1월 데이터 필터링 및 카테고리별 매출 합계 계산
january_sales_data AS (
  SELECT
    trans.actual_price,
    item.category
  FROM
    transaction_data AS trans
  LEFT JOIN
    item_info AS item
  ON
    trans.item_id = item.item_id
  WHERE
    trans.transaction_date >= '01/01/2024'
    AND trans.transaction_date < '02/01/2024'
)

-- 카테고리별 매출 합계 계산 및 순위 설정
SELECT
  category,
  total_sales
FROM
  (
    SELECT 
      category,
      SUM(actual_price) AS total_sales,
      RANK() OVER(ORDER BY SUM(actual_price) DESC) AS sales_rank
    FROM
      january_sales_data
    GROUP BY
      category
  ) AS ranked_sales
WHERE
  sales_rank = 1;

3번 문제. 유저별 총 구매 금액이 200만원 이상인 유저들이 가장 많이 구매한 카테고리를 찾는 쿼리 작성

WITH transaction_data AS (
  SELECT 111 AS user_id, 1001 AS item_id, 719200 AS actual_price, '01/08/2024 12:00:00' AS transaction_date UNION ALL
  SELECT 111, 2002, 89000, '01/10/2024 12:00:00' UNION ALL
  SELECT 189, 2002, 89000, '01/12/2024 12:00:00' UNION ALL
  SELECT 156, 3002, 459000, '01/15/2024 12:00:00' UNION ALL
  SELECT 121, 1001, 719200, '01/18/2024 12:00:00' UNION ALL
  SELECT 156, 2001, 90300, '01/25/2024 12:00:00' UNION ALL 
  SELECT 145, 3001, 399000, '01/26/2024 12:00:00' UNION ALL
  SELECT 189, 1002, 607200, '01/28/2024 12:00:00' UNION ALL
  SELECT 111, 3001, 399000, '02/05/2024 12:00:00' UNION ALL
  SELECT 178, 1002, 759000, '02/07/2024 12:00:00' UNION ALL
  SELECT 121, 2002, 62300, '02/08/2024 12:00:00' UNION ALL 
  SELECT 156, 1001, 899000, '02/10/2024 12:00:00' UNION ALL
  SELECT 190, 2001, 90300, '02/11/2024 12:00:00' UNION ALL 
  SELECT 189, 2001, 90300, '02/14/2024 12:00:00' UNION ALL 
  SELECT 111, 1002, 759000, '02/15/2024 12:00:00' UNION ALL
  SELECT 156, 3001, 299250, '02/20/2024 12:00:00' UNION ALL
  SELECT 189, 3002, 344250, '02/25/2024 12:00:00' UNION ALL
  SELECT 111, 2001, 90300, '02/28/2024 12:00:00'  
), user_info AS (
  SELECT 111 AS user_id, 'Seoul' AS city, 28 AS age, 'Female' AS gender UNION ALL
  SELECT 121, 'Busan', 35, 'Male' UNION ALL
  SELECT 145, 'Incheon', 42, 'Female' UNION ALL
  SELECT 156, 'Seoul', 31, 'Male' UNION ALL
  SELECT 178, 'Daegu', 25, 'Female' UNION ALL
  SELECT 189, 'Seoul', 39, 'Male' UNION ALL
  SELECT 190, 'Busan', 29, 'Female'
), item_info AS (
  SELECT 1001 AS item_id, 'Electronics' AS category, 'Smartphone' AS item_name, 899000 AS list_price UNION ALL
  SELECT 1002 AS item_id, 'Electronics' AS category, 'Tablet' AS item_name, 759000 AS list_price UNION ALL
  SELECT 2001 AS item_id, 'Fashion' AS category, 'Sneakers' AS item_name, 129000 AS list_price UNION ALL
  SELECT 2002 AS item_id, 'Fashion' AS category, 'Backpack' AS item_name, 89000 AS list_price UNION ALL
  SELECT 3001 AS item_id, 'Home' AS category, 'Coffee Machine' AS item_name, 399000 AS list_price UNION ALL
  SELECT 3002 AS item_id, 'Home' AS category, 'Air Purifier' AS item_name, 459000 AS list_price
),

-- 사용자별 총 구매 금액 계산 및 200만원 이상 필터링
user_total_purchase AS (
  SELECT
      user_id,
      SUM(actual_price) AS total_purchase_amount
  FROM
      transaction_data
  GROUP BY
      user_id
  HAVING
      total_purchase_amount >= 2000000
),

-- 필터링된 유저의 카테고리별 구매 금액 합계와 순위 계산
category_sales_rank AS (
  SELECT
    item_info.category AS category,
    SUM(transaction_data.actual_price) AS total_category_sales,
    RANK() OVER(ORDER BY SUM(transaction_data.actual_price) DESC) AS sales_rank
  FROM
    transaction_data
  LEFT JOIN
    item_info
  ON
    transaction_data.item_id = item_info.item_id
  WHERE
    transaction_data.user_id IN (
                  SELECT
                    user_id
                  FROM
                    user_total_purchase
                )
  GROUP BY category
)

-- 결과 출력
SELECT
  category,
  total_category_sales
FROM
  category_sales_rank
WHERE
  sales_rank = 1;