[본캠프 67일차] QCC ⑥, 네트워킹 데이,

 

250522 오늘은 네트워킹 데이가 있는 날~

 

---

 

SQL 공부: 제6회 QCC 회고

1. 각 성별 기준으로 시험 점수 상위 3명 조회하기

각 성별 기준으로 시험 점수가 높은 상위 3명의 학생 성별, 이름과 점수를 반환하는 SQL 문을 작성하세요.
결과는 성별(GENDER) 오름차순, 순위 오름차순으로 정렬하여 출력하세요.
두 학생이 동점일 경우 나이가 많은 학생이 더 순위가 높습니다.

-- 1번 문제의 제출한 쿼리
WITH sm AS (
  SELECT gender,
         name,
         score
  FROM students
  WHERE gender = 'M'
  ORDER BY score DESC, age DESC LIMIT 3
),
     sf AS (
  SELECT gender,
         name,
         score
  FROM students
  WHERE gender = 'f'
  ORDER BY score DESC, age DESC LIMIT 3
     )

SELECT *
FROM sf
UNION ALL
SELECT *
FROM sm

 

2. 도서의 미결제 금액과 결제 완료 금액 조회하기

모든 도서에 대해 도서 제목(TITLE)과 다음 정보를 반환하는 SQL 쿼리를 작성하세요 :
 - 미결제 금액(DUE): 아직 결제되지 않은 총 금액 (반올림하여 정수로 반환)
 - 결제 완료 금액(PAID): 결제 완료된 총 금액 (반올림하여 정수로 반환)
결과는 도서 제목을 기준으로 오름차순으로 정렬하세요. DUE 또는 PAID 값이 NULL인 경우에는 0으로 처리하세요.

-- 2번 문제의 제출한 쿼리
WITH bo3 AS(
  SELECT bo2.id, b.title, bo2.line_total, bo2.paid_date
  FROM (
    SELECT boi.id, 
           boi.book_id, 
           boi.line_total, 
           bo.paid_date
    FROM book_order_items boi LEFT JOIN book_orders bo ON boi.order_id = bo.id
    ) bo2
    LEFT JOIN books b ON bo2.book_id = b.id
)

SELECT title,
       ROUND(SUM(CASE WHEN paid_date IS NULL THEN line_total ELSE 0 END)) due,
       ROUND(SUM(CASE WHEN paid_date IS NOT NULL THEN line_total ELSE 0 END)) paid 
FROM bo3
GROUP BY title
ORDER BY title

 

3. 코호트 분석

각 고객의 첫 주문이 속한 월(FIRST_ORDER_MONTH)을 기준으로 Cohort를 정의합니다.
그 후 해당 Cohort에 속한 고객들이 0개월부터 6개월 후까지 얼마나 다시 주문했는지 월 단위로 재구매한 고객 수를 집계하세요.
 - 첫 주문이 2023년 1월부터 6월 사이인 고객만 분석에 포함합니다.
 - 0개월 후는 해당 월에 첫 주문을 한 고객 수 또는 Cohort에 속해 있는 고객 수입니다.

-- 3번 문제의 제출한 쿼리 (..미완)
WITH customers AS (
  SELECT user_id
  FROM (
    SELECT *
    FROM orders
    WHERE (user_id, order_date) IN (
      SELECT user_id,
             MIN(order_date)
      FROM orders
      GROUP BY user_id
    )
  ) o2
  WHERE DATE_FORMAT(order_date, '%Y-%m-%d') BETWEEN '2023-01-01' AND '2023-06-31'
) -- 첫 주문이 2023년 1월부터 6월 사이인 고객들의 id

SELECT 
FROM orders
WHERE user_id IN (
  SELECT user_id
  FROM customers)