CodeKata

SQL

70. 그룹별 조건에 맞는 식당 목록 출력하기

• 작성 쿼리

WITH review_king AS (
  SELECT
    member_id
  FROM
    rest_review
  GROUP BY
    member_id
  ORDER BY
    COUNT(member_id) DESC
  LIMIT 1
)
SELECT
  member_name
  , review_text
  , DATE_FORMAT(review_date, '%Y-%m-%d') AS review_date
FROM
  member_profile mp
  JOIN rest_review rr
  USING(member_id)
WHERE member_id IN (TABLE review_king)
ORDER BY
  3, 2

→ 이렇게 하면 1등이 여러 명일 때에도 한 명만 구해져서 별로임

WITH review_king AS (
  SELECT
    member_id
    , RANK() OVER(ORDER BY COUNT(member_id) DESC) AS review_rank
  FROM
    rest_review
  GROUP BY
    member_id
)
SELECT
  member_name
  , review_text
  , DATE_FORMAT(review_date, '%Y-%m-%d') AS review_date
FROM
  member_profile
  JOIN rest_review
  USING(member_id)
  JOIN review_king
  USING(member_id)
WHERE
  review_king.review_rank = 1
ORDER BY
  3, 2

참고할 만한 다른 풀이

1. 다중 서브쿼리 & MAX()

SELECT RP.MEMBER_NAME, RR.REVIEW_TEXT, DATE_FORMAT(RR.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE FROM REST_REVIEW AS RR, MEMBER_PROFILE AS RP
WHERE RP.MEMBER_ID = RR.MEMBER_ID AND  RR.MEMBER_ID IN (SELECT B.MEMBER_ID FROM REST_REVIEW AS B
GROUP BY B.MEMBER_ID
HAVING COUNT (*) =(SELECT MAX(C.TEST) AS ANS
FROM (SELECT MEMBER_ID,COUNT(*) AS TEST FROM REST_REVIEW
GROUP BY MEMBER_ID
ORDER BY TEST DESC) AS C))
ORDER BY REVIEW_DATE ASC, RR.REVIEW_TEXT ASC

2. DENSE_RANK() & 서브쿼리

SELECT MEMBER_NAME, REVIEW_TEXT, 
       DATE_FORMAT(REVIEW_DATE,'%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE JOIN REST_REVIEW USING (MEMBER_ID)
WHERE MEMBER_ID IN (SELECT MEMBER_ID
                    FROM (SELECT *, DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS REVIEW_RANK
                          FROM REST_REVIEW 
                          GROUP BY MEMBER_ID) INLINE
                    WHERE REVIEW_RANK = 1
                   )
ORDER BY REVIEW_DATE, REVIEW_TEXT

SELECT  A.MEMBER_NAME
        ,B.REVIEW_TEXT
        ,DATE_FORMAT(B.REVIEW_DATE,'%Y-%m-%d') AS REVIEW_DATE
  FROM  MEMBER_PROFILE A 
  JOIN  (
        SELECT  A.*
          FROM  REST_REVIEW A
          JOIN  (
                SELECT  A.*
                        ,DENSE_RANK() OVER(ORDER BY TOT_CNT DESC) AS RNK 
                  FROM  (
                        SELECT  MEMBER_ID
                                ,COUNT(REVIEW_ID) AS TOT_CNT
                          FROM  REST_REVIEW
                         GROUP
                            BY  MEMBER_ID
                         ORDER
                            BY  2 DESC
                        ) A
               ) B
           ON  A.MEMBER_ID = B.MEMBER_ID
        WHERE  B.RNK = 1   
        ) B
    ON  A.MEMBER_ID = B.MEMBER_ID
 ORDER
    BY  3,2

3. COUNT()

SELECT P.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(R.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM (SELECT *, COUNT(MEMBER_ID) OVER(PARTITION BY MEMBER_ID) AS COUNT_REVIEW
      FROM REST_REVIEW) AS R
JOIN MEMBER_PROFILE AS P
ON R.MEMBER_ID = P.MEMBER_ID
WHERE COUNT_REVIEW = (
    SELECT COUNT(MEMBER_ID) OVER(PARTITION BY MEMBER_ID) AS C
    FROM REST_REVIEW
    ORDER BY C DESC
    LIMIT 1)
ORDER BY REVIEW_DATE, REVIEW_TEXT;

71. 온라인/오프라인 데이터 통합하기

WITH full_sale AS (
  SELECT
    user_id
    , product_id
    , sales_amount
    , sales_date
  FROM
    online_sale
  WHERE
    sales_date BETWEEN '2022-03-01' AND '2022-03-31'
  UNION ALL
  SELECT
    NULL AS user_id
    , product_id
    , sales_amount
    , sales_date
  FROM
    offline_sale
  WHERE
    sales_date BETWEEN '2022-03-01' AND '2022-03-31'
)
SELECT
  DATE_FORMAT(sales_date, '%Y-%m-%d') AS sales_date
  , product_id
  , user_id
  , sales_amount
FROM
  full_sale
ORDER BY
  1, 2, 3
;

72. 조건에 부합하는 중고거래 댓글 조회하기

SELECT
  b.title
  , b.board_id
  , r.reply_id
  , r.writer_id
  , r.contents
  , DATE_FORMAT(DATE(r.created_date),'%Y-%m-%d') created_date
FROM 
  used_goods_board b 
  INNER JOIN used_goods_reply r 
  ON b.board_id = r.board_id
WHERE 
  b.created_date BETWEEN '2022-10-01' AND '2022-10-31'
ORDER BY 
  created_date
  , b.title
;

73. 입양 시각 구하기(2)

• 작성 쿼리

WITH RECURSIVE temp AS (
  SELECT 0 AS num
  UNION ALL
  SELECT num+1
  FROM temp
  WHERE num<23
),
timetable AS (
  SELECT
    HOUR(datetime) AS h
    , COUNT(HOUR(datetime)) AS cnt
  FROM
    animal_outs
  GROUP BY
    HOUR(datetime)
)
SELECT
  t.num AS hour
  , IFNULL(tt.cnt, 0) AS COUNT
FROM
  temp t
  LEFT JOIN timetable tt
  ON t.num = tt.h
ORDER BY
  t.num
;

111. Last Person to Fit in the Bus

• 작성 쿼리

WITH temp AS (
    SELECT
      person_name
      , SUM(weight) OVER (ORDER BY turn) AS bus_weight
    FROM
      queue
)
SELECT
  person_name
FROM
  temp
WHERE
  bus_weight <= 1000
ORDER BY
  bus_weight DESC
LIMIT 1
;

2. 부분합

python

21. 하샤드 수

• 작성 코드

def solution(x):
    harshad = 0
    for i in range(len(str(x))):
        harshad += int(str(x)[i])
    return x%harshad == 0

→ 그냥 for i in str(x)하고 harshad += int(i) 해도 됨

def solution(x):
    harshad = 0
    for i in str(x):
        harshad += int(i)
    return x%harshad == 0

참고할 만한 다른 풀이

• 컴프리헨션(Comprehension) 문법

def solution(n):
    return n%sum(int(x) for x in str(n)) == 0

• map() 함수

def solution(x):
    answer = True
    seat = sum(map(int, list(str(x))))
    if x % seat:
        answer = False
    return answer

def solution(x): 
	return x % sum(map(int,str(x))) == 0

SDL

컴프리헨션(Comprehension) 문법
Python: map() 함수
인라인 if절
recursive CTE

라이브 세션

3회차

QCC

1회차