CodeKata

SQL

39번 풀고 팀 활동

• 작성한 쿼리

SELECT
  ingredient_type
  , SUM(total_order) AS total_order
FROM
  first_half fh
  JOIN icecream_info ii
  USING(flavor)
GROUP BY
  ingredient_type
ORDER BY
  SUM(total_order)
;  

84. Customer Who Visited but Did Not Make Any Transactions

• 작성한 쿼리

SELECT
  customer_id
  , COUNT(customer_id) AS count_no_trans
FROM
  visits v
  LEFT JOIN transactions t
  USING(visit_id)
WHERE
  transaction_id IS NULL
GROUP BY
  customer_id
;

참고할 만한 다른 풀이

• WHERE IN

SELECT customer_id, count(*) AS COUNT_NO_TRANS
FROM visits
WHERE visit_id NOT IN (SELECT visit_id FROM transactions)
GROUP BY customer_id;

• CTE

with temp as(
    select v.visit_id, v.customer_id, t.transaction_id
    from Visits v left join Transactions t 
    on v.visit_id = t.visit_id
)

select customer_id, count(customer_id) as count_no_trans from temp
where transaction_id is null
group by customer_id

• Inline View Subquery

select res.customer_id, count(*) count_no_trans from
(select customer_id from Visits v
left join Transactions t on v.visit_id = t.visit_id
where t.transaction_id is null) res
group by res.customer_id;

85. Rising Temperature

• 작성한 쿼리 → 13번째 tesetcase에서 fail

WITH temp AS (
  SELECT
    id
    , temperature - LAG(temperature) OVER (ORDER BY recordDate ASC) AS diff_t
  FROM
    weather
)
SELECT
  id
FROM
  temp
WHERE
  diff_t > 0

🡆 날짜가 이틀 차이일 때 잘못된 값이 나옴!

13번째 testcase weather table

id	recordDate	temperature
1	2000-12-14	3
2	2000-12-16	5

🡆 recordDate가 연속적인 값인지 검토하고 가져와야 함

• 작성한 쿼리 2

SELECT
  w1.id
FROM
  weather w1
  JOIN weather w2
  ON w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY)
WHERE
  w1.temperature > w2.temperature
;

참고할 만한 다른 풀이

• Subquery

SELECT id
FROM Weather w1
WHERE temperature > (
    SELECT temperature
    FROM Weather w2
    WHERE w2.recordDate = DATE_SUB(w1.recordDate, INTERVAL 1 DAY)
);

• DateOff Simple

SELECT w1.id
FROM Weather w1, Weather w2
WHERE DATEDIFF(w1.recordDate, w2.recordDate) = 1
  AND w1.temperature > w2.temperature;

→ CROSS JOIN 사용

• CTE + LAG

WITH q1 AS (
  SELECT 
      *,
      LAG(temperature) OVER (ORDER BY recordDate) AS previous_day_temperature,
      LAG(recordDate) OVER (ORDER BY recordDate) AS previous_Date
  FROM Weather
)
SELECT id
FROM q1
WHERE temperature > previous_day_temperature
AND DATEDIFF(recordDate, previous_Date) = 1
;

Python

27. 핸드폰 번호 가리기

def solution(phone_number):
    return '*'*(len(phone_number)-4) + phone_number[-4:]

참고할 만한 다른 풀이

def solution(phone_number):
    answer = ''
    for i in range(0, len(phone_number)-4):
        answer += '*'
    return answer + phone_number[-4:]

def solution(phone_number):
	return ''.join(['*' for _ in phone_number[:-4]]) + phone_number[-4:]

def solution(s):
    ss=list(s)
    for i in range(len(ss),-1,-1):
        if i >= (len(ss)-4):
            continue
        else :
            ss.pop(i)
            ss.insert(i,'*')
    a=''
    for i in range(len(ss)):
        a+=ss[i]
    return a

Python 라이브 세션

6회차

라이브러리 과제 해설

과제 & 풀이

회고

• pip install datapane가 계속 오류가 나서 가상 환경 만들고 python 3.11로 낮췄더니 정상적으로 돌아감
  • 참고한 내용
• 밍글 데이 재미있었음
  • 못 푼 문제가 하나 있어서 문제 다시 보고 싶었는데 아쉽다.
    • 구분자를 '+'로 바꾼 뒤 계산을 하면 되는 문제였다고 함
  • 다음에 또 했으면 좋겠다.