CodeKata

SQL

110. Product Price at a Given Date

• 작성한 쿼리

WITH date_cond AS(
  SELECT
    *
  FROM
    Products
  WHERE
    DATE(change_date) <= '2019-08-16' 
)
, before_any_change AS (
  SELECT
    p.product_id
    , IF(d.change_date IS NULL, 10, p.new_price) AS price
    , DENSE_RANK() OVER (PARTITION BY p.product_id ORDER BY d.change_date DESC) AS rnk
  FROM
    Products p
    LEFT JOIN date_cond d
    USING(product_id, change_date)
)
SELECT
  product_id
  , price
FROM
  before_any_change
WHERE
  rnk=1
GROUP BY
  product_id
;

참고할 만한 다른 풀이

• Runtime 가장 짧은 유저

# 2019-08-16 
# change_date < 2019-08-16 , use the lastest new_price
# otherwise, 10 


# min(change_date) > 2019-08-16: 10

# xxx < change_date < 2018-08-16
# select the row where change_date <= 2018-8-16 --> max(change_date)

select
    product_id,
    new_price as price
from(
select
    product_id,
    new_price,
    rank() over (partition by product_id order by change_date DESC) as ranking
from Products
where change_date <= '2019-08-16') sub1
where ranking = 1
union
(
    select
        product_id,
        10 as price
    from Products
    group by product_id
    having min(change_date) > '2019-08-16'
)

-- select
--     *
-- from
-- (select
--     product_id,
--     new_price,
--     rank() over (partition by product_id order by change_date DESC) as ranking
-- from Products
-- where change_date <= '2019-08-16') sub1
-- where ranking=1

• UNION

select 
  distinct product_id
  , 10 as price
from 
  Products
where
  product_id not in(
    select 
      distinct product_id
    from 
      Products
    where
      change_date <='2019-08-16'
  )

union 

select 
  product_id
  , new_price as price
from 
  Products 
where
(product_id,change_date) in (
  select
    product_id
    , max(change_date) as date
  from
    Products 
  where
    change_date <='2019-08-16'
  group by 
    product_id
)

• COALESCE

SELECT p.product_id, 
       COALESCE((
           SELECT new_price 
           FROM Products 
           WHERE product_id = p.product_id AND change_date <= '2019-08-16'
           ORDER BY change_date DESC 
           LIMIT 1
       ), 10) AS price
FROM (SELECT DISTINCT product_id FROM Products) p
;

• WITH, ROW_NUMBER, UNION

WITH LatestPrices AS (
    SELECT product_id, new_price, change_date,
           ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS rn
    FROM Products
    WHERE change_date <= '2019-08-16'
)
SELECT product_id,
       CASE
           WHEN rn = 1 THEN new_price
           ELSE 10
       END AS price
FROM LatestPrices
WHERE rn = 1
UNION
SELECT product_id, 10 AS price
FROM Products
WHERE product_id NOT IN (
    SELECT product_id
    FROM LatestPrices
    WHERE change_date <= '2019-08-16'
)
ORDER BY product_id ASC;

• WITH, RANK, UNION

WITH cte AS
(SELECT *, RANK() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS r 
FROM Products
WHERE change_date<= '2019-08-16')

SELECT product_id, new_price AS price
FROM cte
WHERE r = 1
UNION
SELECT product_id, 10 AS price
FROM Products
WHERE product_id NOT IN (SELECT product_id FROM cte)

• JOIN, UNION

WITH tab AS ( SELECT  product_id, MAX(change_date) AS Max_date
    FROM Products
    WHERE change_date <= '2019-08-16'
    GROUP BY product_id
) 
SELECT P.product_id , P.new_price price
FROM Products P JOIN tab T ON P.product_id=T.product_id AND P.change_date=T.Max_date
UNION 
SELECT Product_id, 10 AS price FROM Products WHERE product_id NOT IN (SELECT product_id FROM tab);

Python

47. 문자열 내 마음대로 정렬하기

• 작성한 코드

def solution(strings, n):
    answer = []
    strings.sort()
    answer = sorted(strings, key = lambda x: x[n])
    return answer

참고할 만한 다른 풀이

from operator import itemgetter, attrgetter, methodcaller

def solution(strings, n):
    return sorted(sorted(strings), key=itemgetter(n))

→ Q. sorted를 두 번 하는 거랑 한 번 하는 거와의 차이가 무엇인가요?
A. 첫째 sorted로 사전순으로 정렬, 둘째로 글자 번호 순으로 정렬입니다.

def solution(strings, n):
    new =[]
    answer =[]
    for i in range(len(strings)):
        a = strings[i][n]
        b = a+strings[i]
        new.append(b)
    new.sort()
    for i in range(len(new)):
        c = new[i][1:]
        answer.append(c)
    return answer

def solution(strings, n):
    elements = []
    sorted_elements = []
    for i in range(len(strings)):
        elements.append((strings[i][n], strings[i]))

        elements.sort(key=lambda e: (e[0], e[1]))

    for j in range(len(elements)):
        sorted_elements.append(elements[j][1])
    return sorted_elements

def solution(strings, n):
    answer = []
    for i in strings : 
        answer.append(i[n]+i)
    answer.sort()
    return [i[1:] for i in answer]

QCC 3회차

• MySQL
  • use db_name: mysql 의 use 명령어는 데이터베이스를 선택하는 명령어로 mysql 에 쌓여있는 여러 db 중 하나를 선택하여 사용하도록 한다.

문제 내용 및 해설

Python 심화반

챌린지 5회차

머신러닝 강의

3주차
4주차

SDL

추가 공부

회고

• 사람들이 속초 여행 추천한 거 메모
  • 문우당 서점
  • 해맞이 하우스 펜션
  • 하마식당
• QCC 너무 어려워서 울고 싶었음
  • 나 자신한테 화가 너무 많이 난다…
    거의 다 풀었는데…
  • 다음 QCC에서는 시간 분배를 잘 해야겠다!
• ADsP 시험 결과 나왔는데 다행히 합격이다~
  • 1, 2과목은 다 맞았는데 3과목이 진짜 아슬아슬했다...