[커머스] H&M 고객/매출 데이터 목표 설정

연령대별 고객의 숫자, 사용한 금액, 사용한 금액의 평균 쿼리

SELECT CASE WHEN c.age BETWEEN 1 AND 9 THEN '1-9'
        WHEN c.age BETWEEN 10 AND 19 THEN '10-19'
        WHEN c.age BETWEEN 20 AND 29 THEN '20-29'
        WHEN c.age BETWEEN 30 AND 39 THEN '30-39'
        WHEN c.age BETWEEN 40 AND 49 THEN '40-49'
        WHEN c.age BETWEEN 50 AND 59 THEN '50-59'
        WHEN c.age BETWEEN 60 AND 69 THEN '60-69'
        WHEN c.age BETWEEN 70 AND 79 THEN '70-79'
        WHEN c.age BETWEEN 80 AND 89 THEN '80-89'
        WHEN c.age >= 90 THEN '90+'
    END age_range,
    COUNT(DISTINCT c.customer_id) customer_count,
    SUM(t.price) total_spent,
    AVG(t.price) average_spent
FROM basic.transactions_hm t
JOIN basic.customer_hm c ON t.customer_id = c.customer_id
JOIN basic.articles_hm a ON t.article_id = a.article_id
GROUP BY age_range
ORDER BY age_range
LIMIT 10

나이가 20대인 고객들이 많이 구매한 상품이름과 구매한 개수

SELECT a.prod_name, COUNT(t.article_id) count
FROM basic.transactions_hm t
JOIN basic.customer_hm c ON t.customer_id = c.customer_id
JOIN basic.articles_hm a ON t.article_id = a.article_id
WHERE c.age BETWEEN 20 AND 29
GROUP BY a.prod_name
ORDER BY count DESC
LIMIT 20

위의 쿼리에서 가장 많이구매한 상품의 유형,그룹,색상,카테고리,색션

SELECT a.prod_name,
       a.product_type_name,
       a.product_group_name,
       a.colour_group_name,
       a.department_name,
       a.section_name
FROM basic.articles_hm a
WHERE a.prod_name = (SELECT a.prod_name
FROM basic.transactions_hm t
JOIN basic.customer_hm c ON t.customer_id = c.customer_id
JOIN basic.articles_hm a ON t.article_id = a.article_id
WHERE c.age BETWEEN 20 AND 29
GROUP BY a.prod_name
LIMIT 1)

월별 상품 구매수

SELECT MONTH(t_dat) month, 
    COUNT(*) frequency
FROM basic.transactions_hm
GROUP BY month
ORDER BY month, frequency DESC

고객별, 월별, 상품 구매수

SELECT customer_id,
    MONTH(t_dat) month, 
    COUNT(*) frequency
FROM basic.transactions_hm
GROUP BY customer_id, month
ORDER BY month, frequency DESC

연령대별 잘팔리는 상품 3위씩

SELECT age_range, prod_name, count
FROM (SELECT CASE WHEN c.age BETWEEN 1 AND 9 THEN '1-9'
                  WHEN c.age BETWEEN 10 AND 19 THEN '10-19'
                  WHEN c.age BETWEEN 20 AND 29 THEN '20-29'
                  WHEN c.age BETWEEN 30 AND 39 THEN '30-39'
                  WHEN c.age BETWEEN 40 AND 49 THEN '40-49'
                  WHEN c.age BETWEEN 50 AND 59 THEN '50-59'
                  WHEN c.age BETWEEN 60 AND 69 THEN '60-69'
                  WHEN c.age BETWEEN 70 AND 79 THEN '70-79'
                  WHEN c.age BETWEEN 80 AND 89 THEN '80-89'
                  WHEN c.age >= 90 THEN '90+'
                  END age_range,
                  a.prod_name,
                  COUNT(t.article_id) count, 
                  ROW_NUMBER() OVER (PARTITION BY CASE 
           WHEN c.age BETWEEN 1 AND 9 THEN '1-9'
           WHEN c.age BETWEEN 10 AND 19 THEN '10-19'
           WHEN c.age BETWEEN 20 AND 29 THEN '20-29'
           WHEN c.age BETWEEN 30 AND 39 THEN '30-39'
           WHEN c.age BETWEEN 40 AND 49 THEN '40-49'
           WHEN c.age BETWEEN 50 AND 59 THEN '50-59'
           WHEN c.age BETWEEN 60 AND 69 THEN '60-69'
           WHEN c.age BETWEEN 70 AND 79 THEN '70-79'
           WHEN c.age BETWEEN 80 AND 89 THEN '80-89'
           WHEN c.age >= 90 THEN '90+'
       END ORDER BY COUNT(t.article_id) DESC) row_num
    FROM basic.transactions_hm t
    JOIN basic.customer_hm c ON t.customer_id = c.customer_id
    JOIN basic.articles_hm a ON t.article_id = a.article_id
    GROUP BY age_range, a.prod_name) ranked
WHERE row_num <= 3
ORDER BY age_range, count DESC;