SQL | SOLVESQL 코딩테스트 : Lv4. (CASE/IF)

Lv4. 가구 판매의 비중이 높았던 날 찾기

https://solvesql.com/problems/day-of-furniture/

SELECT order_date 
  , COUNT(DISTINCT CASE WHEN category = 'Furniture' THEN order_id END) AS 'furniture'
  , ROUND(COUNT(DISTINCT CASE WHEN category = 'Furniture' THEN order_id END) / COUNT(DISTINCT order_id) *100,2) AS 'furniture_pct'
FROM records
GROUP BY order_date
HAVING COUNT(DISTINCT order_id) >= 10 AND furniture_pct >= 40
ORDER BY furniture_pct DESC, order_date ASC

❗주문 건을 집계해야하니 order_id를 기준으로 집계
❗DISTINCT 사용해서 중복 방지
❗반올림 조건이 있으니 ROUND() 함수 사용해서 조건 충족
❗GROUP BY 후 HAVING 절 사용해서 집계 후 조건 충족

 

Lv4. 월별 주문 리텐션 (클래식 리텐션)

https://solvesql.com/problems/monthly-classic-retention/

WITH T1 AS (
  SELECT CS.customer_id
    , DATE_FORMAT(first_order_date,'%Y-%m-01') AS first_order_month
    , DATE_FORMAT(order_date,'%Y-%m-01') AS order_date
    , DATE_FORMAT(last_order_date,'%Y-%m-01') AS last_order_date
  FROM customer_stats AS CS 
    LEFT JOIN records AS R ON CS.customer_id = R.customer_id
)

SELECT first_order_month
  , COUNT(DISTINCT customer_id) AS month0
  , COUNT(DISTINCT CASE WHEN order_date = DATE_ADD(first_order_month, INTERVAL 1 MONTH) THEN customer_id END ) AS month1
  , COUNT(DISTINCT CASE WHEN order_date = DATE_ADD(first_order_month, INTERVAL 2 MONTH) THEN customer_id END ) AS month2
  , COUNT(DISTINCT CASE WHEN order_date = DATE_ADD(first_order_month, INTERVAL 3 MONTH) THEN customer_id END ) AS month3
  , COUNT(DISTINCT CASE WHEN order_date = DATE_ADD(first_order_month, INTERVAL 4 MONTH) THEN customer_id END ) AS month4
  , COUNT(DISTINCT CASE WHEN order_date = DATE_ADD(first_order_month, INTERVAL 5 MONTH) THEN customer_id END ) AS month5
  , COUNT(DISTINCT CASE WHEN order_date = DATE_ADD(first_order_month, INTERVAL 6 MONTH) THEN customer_id END ) AS month6
  , COUNT(DISTINCT CASE WHEN order_date = DATE_ADD(first_order_month, INTERVAL 7 MONTH) THEN customer_id END ) AS month7
  , COUNT(DISTINCT CASE WHEN order_date = DATE_ADD(first_order_month, INTERVAL 8 MONTH) THEN customer_id END ) AS month8
  , COUNT(DISTINCT CASE WHEN order_date = DATE_ADD(first_order_month, INTERVAL 9 MONTH) THEN customer_id END ) AS month9
  , COUNT(DISTINCT CASE WHEN order_date = DATE_ADD(first_order_month, INTERVAL 10 MONTH) THEN customer_id END ) AS month10
  , COUNT(DISTINCT CASE WHEN order_date = DATE_ADD(first_order_month, INTERVAL 11 MONTH) THEN customer_id END ) AS month11
FROM T1
GROUP BY first_order_month
ORDER BY first_order_month

❗COUNT(), DISTINCT 사용해서 중복없이 집계
❗CASE WHEN 사용해서 조건에 해당하는 칼럼 새로 지정
❗DATE_ADD() 사용해서 날짜 폭 조정
❗ORDER DATE와 DATE_ADD() 한 날짜가 같으면 리텐션이 발생한다고 보고 고객ID 중복없이 집계

+) WITH 절

❗DATE_FORMAT() 사용해서 첫 주문일, 주문일, 마지막 주문일 포맷을 동일하게 맞춤
❗LEFT JOIN 으로 고객 정보를 기준으로 테이블 결합

 

Lv4. 월별 주문 리텐션 (롤링 리텐션)

https://solvesql.com/problems/monthly-rolling-retention/

WITH T1 AS (
  SELECT customer_id
    , DATE_FORMAT(first_order_date,'%Y-%m-01') AS first_order_month
    , DATE_FORMAT(last_order_date,'%Y-%m-01') AS last_order_month
  FROM customer_stats
)

SELECT first_order_month
  , COUNT(DISTINCT customer_id) AS month0 
  , COUNT(DISTINCT CASE WHEN last_order_month >= DATE_ADD(first_order_month, INTERVAL 1 MONTH) THEN customer_id END) AS month1
  , COUNT(DISTINCT CASE WHEN last_order_month >= DATE_ADD(first_order_month, INTERVAL 2 MONTH) THEN customer_id END) AS month2
  , COUNT(DISTINCT CASE WHEN last_order_month >= DATE_ADD(first_order_month, INTERVAL 3 MONTH) THEN customer_id END) AS month3
  , COUNT(DISTINCT CASE WHEN last_order_month >= DATE_ADD(first_order_month, INTERVAL 4 MONTH) THEN customer_id END) AS month4
  , COUNT(DISTINCT CASE WHEN last_order_month >= DATE_ADD(first_order_month, INTERVAL 5 MONTH) THEN customer_id END) AS month5
  , COUNT(DISTINCT CASE WHEN last_order_month >= DATE_ADD(first_order_month, INTERVAL 6 MONTH) THEN customer_id END) AS month6
  , COUNT(DISTINCT CASE WHEN last_order_month >= DATE_ADD(first_order_month, INTERVAL 7 MONTH) THEN customer_id END) AS month7
  , COUNT(DISTINCT CASE WHEN last_order_month >= DATE_ADD(first_order_month, INTERVAL 8 MONTH) THEN customer_id END) AS month8
  , COUNT(DISTINCT CASE WHEN last_order_month >= DATE_ADD(first_order_month, INTERVAL 9 MONTH) THEN customer_id END) AS month9
  , COUNT(DISTINCT CASE WHEN last_order_month >= DATE_ADD(first_order_month, INTERVAL 10 MONTH) THEN customer_id END) AS month10
  , COUNT(DISTINCT CASE WHEN last_order_month >= DATE_ADD(first_order_month, INTERVAL 11 MONTH) THEN customer_id END) AS month11

FROM T1
GROUP BY first_order_month
ORDER BY first_order_month

❗COUNT(), DISTINCT 사용해서 중복없이 집계
❗CASE WHEN 사용해서 조건에 해당하는 칼럼 새로 지정
❗DATE_ADD() 사용해서 날짜 폭 조정
❗마지막 주문일보다 와 DATE_ADD() 한 날짜가 작으면 구매가 발생했다고 가정(이탈하지 않았다) 롤링 리텐션이 발생한다고 보고 고객ID 중복없이 집계

+) WITH 절

❗DATE_FORMAT() 사용해서 첫 주문일, 주문일, 마지막 주문일 포맷을 동일하게 맞춤