SQL 문제 풀이 챌린지(21~25)

저작권 문제로 인해, 직접 작성한 쿼리문만 공개.

 

 

 

21. A/B 테스트를 위한 버킷 나누기 1

https://solvesql.com/problems/ab-testing-buckets-1/

SELECT DISTINCT
  customer_id,
  CASE 
    WHEN MOD(customer_id, 10)= 0 THEN "A" ELSE "B"
  END AS bucket
FROM transactions
ORDER BY customer_id ASC

 

 

 

22. 연속된 이틀간의 누적 주문 계산하기
https://solvesql.com/problems/cumulative-orders/

WITH daily_counts AS (
    SELECT
        DATE(purchased_at) AS order_date,
        DAYNAME(purchased_at) AS weekday,
        SUM(is_online_order) AS num_orders_today
    FROM transactions
    WHERE DATE_FORMAT(purchased_at, '%Y-%m') IN ('2023-11','2023-12')
    GROUP BY DATE(purchased_at), DAYNAME(purchased_at)
)

SELECT
    order_date,
    weekday,
    num_orders_today,
    SUM(num_orders_today) OVER(
        ORDER BY order_date
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS num_orders_from_yesterday
FROM daily_counts
ORDER BY order_date ASC

 

 

 

23. A/B 테스트를 위한 버킷 나누기 2
https://solvesql.com/problems/ab-testing-buckets-2/

WITH customer_stats AS (
    SELECT
        customer_id,
        CASE WHEN MOD(customer_id, 10) = 0 THEN 'A' ELSE 'B' END AS bucket,
        COUNT(*) AS num_orders,
        SUM(total_price) AS sum_sales
    FROM transactions
    WHERE is_returned = 0
    GROUP BY customer_id, bucket
)

SELECT
    bucket,
    COUNT(*) AS user_count,
    ROUND(AVG(num_orders), 2) AS avg_orders,
    ROUND(AVG(sum_sales), 2) AS avg_revenue
FROM customer_stats
GROUP BY bucket

24. 도시별 VIP 고객 찾기
https://solvesql.com/problems/vip-of-cities/

WITH customer_totals AS (
    SELECT
        city_id,
        customer_id,
        SUM(total_price - discount_amount) AS total_spent
    FROM transactions
    WHERE is_returned = 0
    GROUP BY city_id, customer_id
)

SELECT
    city_id,
    customer_id,
    total_spent
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER(PARTITION BY city_id ORDER BY total_spent DESC) AS rank_spent
    FROM customer_totals
) t
WHERE rank_spent = 1
ORDER BY city_id

25. 산타의 웃음 소리

https://solvesql.com/problems/laugh-of-santa-claus/

SELECT CONCAT(
    SUBSTRING('산타의', 1, 0), 'Ho ',
    SUBSTRING('웃음', 1, 0), 'Ho ',
    SUBSTRING('소리', 1, 0), 'Ho'
) AS 'Santa_Laughing_Loudly'