인프런 커뮤니티 질문&답변
작성한 질문수
[3]_[인프런 빅쿼리 빠짝스터디 3주차] 리텐션 과제와 추가 문제 풀이
작성
·
63
0
추가 문제
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번 문제(15분)
-- - 주차별 각 카테고리별 평균 할인율이 가장 높았던 기간과 할인율을 구하는 쿼리를 작성해주세요
-- - 단, 날짜 데이터를 YYYY-MM-DD 23:59:39 이런 형태로 변경해주세요
, real_base as (
select
*
from (
select
*,
avg(diff_price) as avg_price,
from(
SELECT
transaction_date,
category,
list_price,
(list_price - actual_price) as diff_price
FROM item_info as item
left join transaction_data trans_date
on item.item_id = trans_date.item_id
) as base
group by all
) as base2
)
## 주차별 평균 할인율 가장 높은 기간
--> 2024-01-08 ~ 2024-01-18
-- select
-- FORMAT_TIMESTAMP('%Y-%m-%d 23:59:39', PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', transaction_date)) AS formatted_date,
-- avg_price
-- from real_base
-- group by
-- 1,2
-- order by
-- 2 desc
## 카테고리별 평균 할인율이 가장 높았던 기간
select
*,
round((max_price / list_price) * 100, 2) as `할인율`
from (
select
distinct
FORMAT_TIMESTAMP('%Y-%m-%d 23:59:39', PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', transaction_date)) AS formatted_date,
category,
max(avg_price) as max_price,
list_price
from real_base
group by all
order by
3 desc
) as avg_price_table
order by
5 desc
## 할인율이 가장 높았던 주차
-- 01/25/2024
-- 02/08/2024
-- 02/11/2024
-- 02/14/2024
-- 02/28/2024
-- select
-- transaction_date,
-- round((diff_price / list_price) * 100, 2) as `할인율`
-- -- transaction_date,
-- -- safe_divide(diff_price, list_price)
-- from real_base
-- group by
-- 1,2
-- order by
-- 2 desc
## 할인율이 높았던 카테고리
-- Fashion
-- select
-- category,
-- round((diff_price / list_price) * 100, 2) as `할인율`
-- -- transaction_date,
-- -- safe_divide(diff_price, list_price)
-- from real_base
-- group by all
-- order by
-- 2 desc
2번
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
)
# 2번 문제(10분) 1610
-- - 2024년 1월에 가장 많은 매출을 기록한 카테고리를 구하는 쿼리를 작성해주세요
-- 날짜 테이블
-- 아이템 테이블 활용
-- 기간에 필터링 걸기
-- 카테고리
-- 매출
-- 카테고리별 구매가
-- 구매가의 합 = 매출
## 카테고리별 구매가
, category_price as (
select
item_date.transaction_date,
item.category,
item_date.actual_price
from item_info as item
left join transaction_data as item_date
on item.item_id = item_date.item_id
group by all
)
## 매출 | 카테고리별 구매가의 합
, `매출_테이블` as (
select
*,
sum(actual_price) over(partition by category) as `매출`
from category_price
)
## 카테고리별 매출에 1월 필터링
select
*
from `매출_테이블`
where
transaction_date between '01/08/2024' and '01/28/2024'
3번
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
)
# 3번 문제(10분) 1646
-- - 유저별 총 구매 금액이 200만원 이상인 유저들이 가장 많이 구매한 카테고리를 찾는 쿼리를 작성해주세요
-- 유저별 총 구매 금액
-- 유저 | 총 구매금액
-- 유저별 | 구매금액
-- 구매금액 합산 = 총 구매금액
, `유저별_총구매금액` as (
select
user_id,
`총 구매금액`
from (
select
user_id,
sum(actual_price) over(partition by user_id) as `총 구매금액`
from (
SELECT
user_id,
actual_price,
FROM transaction_data
group by all
) as base1
) base2
group by
1,2
)
-- 200만원 이상 필터링
, `200만원 이상 필터링` as (
select
*
from `유저별_총구매금액`
where
`총 구매금액` >= 2000000
)
## 유저들이 가장 많이 구매한 카테고리
-- 유저 | 카테고리 | 구매
-- 구매가가 있는 카테고리 수를 세면 어떨까?
-- 카테고리별 카운트?
select
category,
`카테고리별카운트`
from (
select
*,
sum(`카테고리수`) over(partition by category) as `카테고리별카운트`
from (
select
user_id,
category,
count(category) over(partition by user_id) as `카테고리수`
from (
select
td2.user_id,
item2.category,
td2.actual_price
from transaction_data as td2
left join item_info as item2
on td2.item_id = item2.item_id
) b_base
) as b_base2
) as b_base3
group by
1,2
order by
2 desc답변 1
0
안녕하세요, 인프런 AI 인턴입니다. 레스님께서 작성하신 SQL 쿼리에 대한 질문과 풀이를 잘 확인했습니다. 추가 문제를 해결하시면서 다양한 분석을 시도하셨습니다. 주어진 문제에 대한 상세한 분석은 기존의 유사한 질문과 답변에서 많은 도움이 될 수 있습니다. 특히, 할인율 계산과 카테고리 분석 관련 쿼리 작성에 대해 더 깊이 있는 이해를 돕기 위한 기존의 답변을 추천드립니다.
유사한 질문 및 참고할 수 있는 내용:
리텐션 관련 문제 해결 방법은 이미 제시된 리텐션 과제_연습문제 2번 에서 다양한 사례로 설명되고 있습니다. 이 답변은 여러 가지 데이터 처리 방법과 함께 쿼리 최적화 및 문제 해결 전략에 대해 논의합니다.
할인율 및 카테고리 분석에 대한 추가적인 설명은 리텐션 SQL 작성하기 와 퍼널별 유저들의 새로운 세션 시작 횟수의 총합 _ 윈도우 함수 마지막 문제 응용 에서 확인할 수 있습니다. 이 답변들은 데이터 분석을 위한 SQL 쿼리를 작성하는 방법과, 그 결과를 해석하는 방법에 대해 추가 인사이트를 제공합니다.
풍부한 설명 추가:
- SQL을 사용하여 주차별 할인율, 카테고리별 매출을 분석할 때, 각 카테고리나 시간 주기별로 데이터를 집계하고 필터링하는 것은 매우 중요합니다. 이를 통해 어떤 시기에 어떤 카테고리가 높은 매출이나 할인율을 기록했는지 명확히 알 수 있습니다.
- 특히, 집계 쿼리를 작성할 때는 GROUP BY 와 OVER 절을 적절히 사용하여 중복된 데이터를 제거하고, 필요한 분석을 효율적으로 수행해야 합니다.
- 할인율의 경우, (list_price - actual_price) / list_price * 100 방식으로 계산하여 가격 차이에 따른 백분율로 보여주는 것이 좋습니다.
- 판매량이나 매출이 높은 상품이나 카테고리를 찾기 위해서는 SUM() 과 COUNT() 를 잘 활용하는 것이 중요합니다.
저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다.
현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏
추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.