[250904] 스파르타코딩 본캠프 23일차

💥  CODEKATA                                                                                                           

~ 86문제

 

 

 

팀프로젝트 시작해서 이제 당분간 코드카타 못할 듯 싶음

 

86) 1661. Average Time of Process per Machine

# 내코드
WITH start AS (
    SELECT *
    FROM activity
    WHERE activity_type = "start"
),
end AS (
    SELECT *
    FROM activity
    WHERE activity_type = "end"
)
SELECT e.machine_id, ROUND(SUM(e.timestamp - s.timestamp) / COUNT(1), 3) AS processing_time
FROM start s JOIN end e ON s.machine_id = e.machine_id AND s.process_id = e.process_id
GROUP BY e.machine_id

# 답코드
SELECT a.machine_id, 
       ROUND(AVG(b.timestamp - a.timestamp), 3) AS processing_time
FROM Activity a, Activity b
WHERE 
    a.machine_id = b.machine_id AND a.process_id = b.process_id AND 
    a.activity_type = 'start' AND b.activity_type = 'end'
GROUP BY machine_id

 

 

 

---

SQL 강의에서 나왔던 문제들을 다시 풀어보았음

 

🔰 SQL 4차 과제

https://teamsparta.notion.site/SQL-_4-2492dc3ef5148079bf4dd5fe0736de86?source=copy_link

s1.csv / s2.csv 필요

 

# [250813] 9일차 코드
select a.gb, count(distinct a.game_account_id) as usercnt
from
(
	select u.game_account_id, p.pay_amount, 
		case
			when p.pay_amount > 0 then '결제함'
			else '결제안함'
		end as gb
	from users u left join (
		select pay_amount, game_account_id 
		from payment
		) p on u.game_account_id = p.game_account_id 
	order by p.pay_amount
) a
group by a.gb

# [250903] 22일차 코드 
SELECT
	CASE
		WHEN p.pay_type IS NOT NULL THEN "결제함"
		ELSE "결제안함"
	END gb, COUNT(DISTINCT u.game_account_id) AS usercnt
FROM testdb.users u LEFT JOIN testdb.payment p ON u.game_account_id = p.game_account_id
GROUP BY
	CASE
		WHEN p.pay_type IS NOT NULL THEN "결제함"
		ELSE "결제안함"
	END

⇒

와 처음에 쓴 코드 다시 보니까 쓸데없이 서브쿼리 3개나 썼음 ㄷㄷ;

 

 

 

https://school.programmers.co.kr/learn/courses/30/lessons/59045

# [250819] 11일차 (2) 에서 푼 코드

select a.animal_id, a.animal_type, a.name
from (
    SELECT ai.animal_id, ai.animal_type, ai.name, ai.sex_upon_intake, ao.sex_upon_outcome, 
           ai.sex_upon_intake != ao.sex_upon_outcome as new_sex_upon
    from animal_ins ai left join animal_outs ao on ai.animal_id = ao.animal_id
) a
where a.new_sex_upon = 1

# 250903 22일차에 푼 코드 (1)
# 근데 너무 문자열 하드코딩인 것 같아서 바꿔보기로 함

SELECT i.animal_id, i.animal_type, i.name
FROM animal_ins i LEFT JOIN animal_outs o ON i.animal_id = o.animal_id
WHERE i.sex_upon_intake LIKE "Intact%" AND o.sex_upon_outcome NOT LIKE "Intact%"

# # 250903 22일차에 푼 코드 (2)
# 생각해보니 들어올 땐 중성화인데 나갈 때는 다시 붙일 수 없으니(?) 같지 않다로만 찾아도 됨

SELECT i.animal_id, i.animal_type, i.name
FROM animal_ins i LEFT JOIN animal_outs o ON i.animal_id = o.animal_id
WHERE i.sex_upon_intake != o.sex_upon_outcome

⇒

확실히 코드카타의 힘이 느껴진다.

처음 풀었을 때의 코드보다 훨씬 가독성있고 간결해짐