CodeKata

SQL

101. Product Sales Analysis III

• 작성한 쿼리

WITH rank_year AS(
    SELECT
      *
      , DENSE_RANK() OVER (PARTITION BY product_id ORDER BY year) AS ranking
    FROM
      sales s
      JOIN product p
      USING(product_id)
)
SELECT
  product_id
  , year AS first_year
  , quantity
  , price
FROM
  rank_year
WHERE
  ranking = 1
;

→ product 테이블이 주어져 있어서 일단 썼는데 해당 테이블 안 쓰고도 문제 해결 가능

WITH min_year AS(
    SELECT
      product_id
      , MIN(year) AS year
    FROM
      sales
    GROUP BY
      product_id
)
SELECT
  product_id
  , year as first_year
  , quantity
  , price
FROM
  sales
WHERE
  (product_id, year) IN (TABLE min_year)
;

참고할 만한 다른 풀이

WITH first_years AS (
    SELECT product_id, MIN(year) AS first_year
    FROM Sales
    GROUP BY product_id
)
SELECT S.product_id, S.year AS first_year, S.quantity, S.price
FROM Sales S
JOIN first_years F
ON S.product_id = F.product_id AND S.year = F.first_year;
;

102. Classes More Than 5 Students

• 작성한 쿼리

SELECT
  class
FROM
  courses
GROUP BY
  class
HAVING
  COUNT(*) >= 5
;

Python

40. 3진법 뒤집기

• 작성한 코드

def solution(n):
    answer = 0
    trit_reverse = ''
    while n>0:
        n, mod = divmod(n,3)
        trit_reverse += str(mod)
    answer = int(trit_reverse, 3)
    return answer

참고할 만한 다른 풀이

def solution(n):
    tmp = ''
    while n:
        tmp += str(n % 3)
        n = n // 3

    answer = int(tmp, 3)
    return answer

def solution(n):
    answer = 0
    cnt = 1
    a = ''
    while n>0:
        a+=str(n%3)
        n = n//3
    print(a)
    for b in range(len(a),0,-1):
        answer += (int(a[b-1])*cnt)
        cnt *= 3
    return answer

def solution(n):
    answer = []
    while True:
        if n < 3:
            answer.append(n)
            break
        answer.append(n % 3)
        n = n // 3
    answer.reverse()
    sum = 0
    for i in range(len(answer)):
        sum += (answer[i] * (3 ** i))
    return sum

def n_ary(n, base):
    result = []
    while n > 0:
        n, r = divmod(n, base)
        result.append(r)
    return ''.join(map(str, reversed(result)))

def solution(n):
    b3 = n_ary(n, 3)
    b3 = b3[::-1]
    return int(b3, 3)

QCC & QCC 해설

2회차

Python 심화반

2회차

회고

• QCC 2회차 재미있었음
  • 딱 소화 가능한 정도의 테이블이라 즐겁게 했다.
• 심화반 수업 정말 좋다🥰
  • 흥미로운 내용도 많고 응용할 부분이 많아서 행복
• Single and Double Quotes