2024-01-19

SQL 코드카타

 

1174. Immediate Food Delivery II(SQL) (select 서브쿼리, inner join, 같은 테이블 with로 해서 join통해 조건으로 사용)

https://leetcode.com/problems/immediate-food-delivery-ii/description/

주문날짜와 원하는 배달날짜가 동일하면 즉시 주문이 되고 그렇지 않다면 예약이 되는데, 각 고객별로 첫 주문이 즉시 배달인 수 / 전체 고객들 수 로 하여 비율을 소수 둘째짜리 표시하도록 반올림하도록 하여 출력하는 문제이다.

WITH min_order AS(
    SELECT d_m.customer_id,
            min(order_date) first_order_date
    FROM Delivery d_m
    GROUP BY d_m.customer_id

)
SELECT ROUND(100*COUNT(d.customer_id)/
            (SELECT COUNT(DISTINCT d_s.customer_id) FROM Delivery d_s)
            ,2) immediate_percentage
FROM Delivery d
INNER JOIN min_order m ON d.customer_id = m.customer_id
WHERE m.first_order_date=d.customer_pref_delivery_date

우선 customer_id에 대하여 첫주문을 출력한 테이블 min_order을 선언해주고 원래 테이블과 customer_id에 대하여 inner join하여 첫주문날짜와 배달희망날짜가 같은 경우에 대해서 필터링해주고, 서브쿼리를 통해서 전체 customer_id의 수를 구하여 그 값으로 앞의 필터 조건에 만족하는 데이터에 대해 customer_id의 수에 대해 나누어준다. 그뒤 소수둘째자리까지 반올림하여 표현해주는 쿼리이다.

 

550. Game Play Analysis IV(SQL)(문제 뻘짓,select 서브쿼리, date_add interval 1 day, inner join, group과 distinct, 같은 테이블 with로 해서 join통해 조건으로 사용)

https://leetcode.com/problems/game-play-analysis-iv/description/

첫날 로그인하고 다음날 바로 로그인한 player_id의 갯수/ 전체 player_id 를 소수 둘째짜리까지 반올림하여 나타내라는 문제이다(첫날에 이어서 다음날이라는 부분을 제대로 못보고 잘못이해하여 더 복잡하게 문제에선 생각안하는 부분까지 체크하다가 너무 오래걸려서 힌트보고 저런 의도인 것을 파악하고 타협하여 문제의도맞게 풀어서 제출하였다(처음 이해하기로는 중간에라도 연속된 날짜가 있으면 세어줘야한다고 이해하였다))

그런데 game_played가 정확히 뭔지 모르겠음 (확인하는 게임중 플레이한 게임 수인지? 아니면 플레이 시간인지?)→문제 푸는데는 별 상관없는 내용인듯

 처음에 잘못이해한 부분을 해보다가 계속 해결못해서 막혔는데 결국 생각한대로 표현되게는 완전히는 구현을 못한듯하다(너무 오래 걸리는 듯하여 일단은 더 시도해보지 못하고 원래 문제 목적에 맞게만 해결하고 지나갔다)

WITH first_login AS(
    #연속적으로 로그인 한 경우 아이디
    SELECT a_m.player_id,
            MIN(a_m.event_date) first_date
    FROM Activity a_m
    GROUP BY a_m.player_id
) 
SELECT ROUND(COUNT(DISTINCT a.player_id)/
            (SELECT COUNT(DISTINCT a_s.player_id) FROM Activity a_s)
            ,2) fraction
FROM Activity a
INNER JOIN first_login f ON a.player_id = f.player_id
AND DATE_ADD(f.first_date, INTERVAL 1 DAY) = a.event_date;

쿼리에 대해 간단히 설명하면 먼저 각 player_id별로 가장 첫날을 가져와서 first_login테이블(원래 그냥 first로 했다가 노션에 옮겨적으니 파랗게 변해서 혹시나를 위해 수정해주었다)로 선언하고 원래 테이블에 inner join하여 player_id가 같고, 첫날을 기준으로 다음날 데이터가 있는 것에 대해서만 가져오도록하여 문제에서 설명한 계산을 해주었는데 이때 전체 player_id수는 서브쿼리를 사용하여 계산해주었다

 

2356. Number of Unique Subjects Taught by Each Teacher(SQL)(간단)

https://leetcode.com/problems/number-of-unique-subjects-taught-by-each-teacher/description/

각 teacher_id별로 가르치는 과목 갯수 세는 문제이다.

SELECT t.teacher_id, count(DISTINCT t.subject_id) cnt
FROM Teacher t
GROUP BY t.teacher_id

 

1141. User Activity for the Past 30 Days I(SQL) (datediff , between)

https://leetcode.com/problems/user-activity-for-the-past-30-days-i/description/

2019-07-27까지 포함되는 30일의 기간동안 활동이 있는 user_id의 수를 각 날별로 표시하는 문제이다.

SELECT a.activity_date day, COUNT(DISTINCT a.user_id) active_users
FROM Activity a
WHERE DATEDIFF('2019-07-27',a.activity_date) BETWEEN 0 and 29
GROUP BY a.activity_date

2019-07-27을 기준일이라고 할때 기준일 2019-07-27까지 포함하여 30일이니 기준일과 차이가 0인 당일만 생각하면 1day만 취급하는 것이니 30 day를 취급할려면 기준일로 부터 29일 차이나는 날짜까지 고려를 해야한다(단 기준일보다 이전의 날짜여야함) datediff는 datediff(B,A)하면 B-A를 하여 차를 구해주는 방식이므로 기준일을 B자리에 적고 A자리에 데이터값을 적어주어 차이를 확인하면 되며 그 차이의 가능한 범위는 0~29가 된다 따라서 그렇게 쿼리를 작성해주었다

간단히 쿼리에 대해 설명하면 activity테이블에서 기준일과 차이가 0~29일 차이나는 데이터들에 대해서만 일(activity_date)에 대해 그룹을 지어서 각 일별로 조건에 만족하는 user_id의 갯수를 세어주는 쿼리이다.

사실 datediff에 컬럼명을 넣지않고 직접 값을 넣은 것은 처음이였는데 그냥 숫자로 적으면 제대로 조건이 안되었고 ‘’을 달고 하니 제대로 되었다 이점은 기억해두면 좋을 듯하다.

 

1070. Product Sales Analysis III(SQL)(group 쓰임 혼동, 같은 테이블 with로 해서 join통해 조건으로 사용)

https://leetcode.com/problems/product-sales-analysis-iii/ 

매 상품에 대해 상품아이디와 첫해, 첫해의 수량과 금액을 출력하는 문제이다.

SELECT s.product_id, MIN(s.year) first_year, s.quantity, s.price
FROM Sales s
GROUP BY s.product_id

처음에는 매우 단순하게 그냥 문제에서 말하는 것을 아무 생각없이 작성하였는데 기본tc를 통과했길래 응? 간단한건가 하고 냈다가 바로 1case만 통과하고 바로 불통하였는데 그래서 아 역시 first_year에 대응되는 데이터들이 나온게 아니라 그냥 각 그룹별 순서대로 제일 먼저 있는게 나왔겠구나 생각되어서 with를 활용하여 다음과 같이 작성해주어 join을 통해 매칭시켜주었다  

WITH first_sale AS(
    SELECT s_s.product_id, MIN(s_s.year) first_year
    FROM Sales s_s
    GROUP BY s_S.product_id
)
SELECT s.product_id, f.first_year, s.quantity, s.price
FROM Sales s
JOIN first_sale f ON s.product_id = f.product_id 
AND f.first_year = s.year
GROUP BY s.product_id

하지만 여전히 한 케이스만 통과하고 다 불통이였는데 왜 그렇지하며 제출 결과에 나온 틀린 케이스의 경우를 분석해보면(tc가 너무 길어서 블로그에선 생략하도록 하고 분석통해 확인한 부분에 대해서만 얘기하겠다) 대부분은 결과에 맞게 나왔지만 4개정도 더 길게 나와야하는데 적게 나왔으며, 같은 product_id에 대해 확인해보니 값이 좀 작길래(분명 슥 봤을 때는 일치하는 행이 있어서 맞는 것 같은데 이때까지만 해도 혹시하는 정도였다) product_id=1인 경우가 그렇길래 바로 tc2에 대해 모든 열 출력하는데 product=1인 데이터만 출력하는데 year를 기준으로 오름차순 정렬시켜 확인해본 결과 가장 빠른 연도인 1802이 2개였고, 둘중 먼저 나온 행은 내 쿼리가 출력한 값과일치 했고, 내 출력에서 빠진 행과 나머지 하나가 일치하였다(각 product_id에 대해 같은 연도여도 여러 데이터가 존재할 수 있고 그것을 개별적으로 표시해야하는 듯 했다) 원인은 알았으나 이것을 해결해주기 위해서 어떻게 해야할지 조금 고민이 되었다

틀린 반례가 너무 길어서 내가 직접 틀린 부분을 반영하여 만들어준 tc를 적어두겠다

#sales
| sale_id | product_id | year | quantity | price |
| ------- | ---------- | ---- | -------- | ----- |
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
| 8       | 100        | 2008 | 30       | 8000  |

#product
| product_id | product_name |
| ---------- | ------------ |
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |

WITH first_sale AS(
    SELECT s_s.product_id, MIN(s_s.year) first_year
    FROM Sales s_s
    GROUP BY s_s.product_id
)
SELECT s.product_id, f.first_year, s.quantity, s.price
FROM Sales s
JOIN first_sale f ON s.product_id = f.product_id 
WHERE f.first_year = s.year
GROUP BY s.product_id

이렇게 해줘도 별 차이가 없다 느낌이 group 없애면 될 것 같기는 한데, 왜 이건 변화가 없는지 좀 더 생각해봐야겠다

tc3에 대해서 간단히 테스트해보면

SELECT s.product_id, s.year, s.quantity, s.price
FROM Sales s
GROUP BY s.product_id

#out
| product_id | year | quantity | price |
| ---------- | ---- | -------- | ----- |
| 100        | 2008 | 10       | 5000  |
| 200        | 2011 | 15       | 9000  |

아무래도 계속 group하는 과정에서 내 생각과 다르게 나오는 부분이 많이 생기는데 group에 대해서 좀 더 제대로 이해해야할 필요가 있을 것 같다. 추가로 group 기준에 추가하지 않는다면, 하위 컬럼들에 대해서 무조건 각 그룹의 첫 행만 나오게 되는 것 같다

 

아래 쿼리가 최종적으로 제출한 쿼리이다

WITH first_sale AS(
    SELECT s_s.product_id, MIN(s_s.year) first_year
    FROM Sales s_s
    GROUP BY s_s.product_id
)
SELECT s.product_id, f.first_year, s.quantity, s.price
FROM Sales s
JOIN first_sale f ON s.product_id = f.product_id 
WHERE f.first_year = s.year

일단 이렇게 하니 해결되었다

쿼리에 대해서 설명하면 Sales 테이블에 대해 product_id로 그룹을 지은 후 product_id와 첫 연도(가장 작은 연도)를 출력한 테이블 first_sale을 선언해주었고, 그것과 Sales 테이블을 product_id 를 공통 컬럼으로 하여 매칭시켜 (inner)join해주었다 그 뒤 최소연도와 같은 연도에 대한 데이터만을 필터링하여 s.product_id, f.first_year, s.quantity, s.price를 출력하는 쿼리이다(조건을 걸어주었기 때문에 f.first_year대신 s.year을 써도 무관하다 대신 표시될 컬럼명 신경써줘야 한다)

여담으로 처음 f.first_year로 해서 제출 했을 때 runtime 1782ms이였고 s.year first_year로 바꿔서 제출했을 때는 3315ms였으나, 그러고 바로 다시 제출을 한번 더 누르니 1438ms로 거의 최상위권 바로 아래정도의 runtime이 나왔다

그런데 보통 runtime보다는 beats어떻다고 얘기를 하면서 자신 쿼리가 좋다고 소개를 해두던데 beats가 정확히 무엇인지 모르겠다(여담으로 위에 언급한 순서대로 beats는 57.32% /5.37% /98.91%이다)

같은 테이블 with로 해서 join으로 사용하기(조건으로써 이용됨) 이 근처 문제들 이런 문제 많았던 듯(이런 부분 적을 생각을 못했어가지고 우선은 여기에 적어둠)

 

596. Classes More Than 5 Students(SQL)(having count)

https://leetcode.com/problems/classes-more-than-5-students/description/

듣는 학생수가 5이상인 class를 모두 출력하는 문제이다.

SELECT c.class
FROM Courses c
GROUP BY c.class
HAVING COUNT(c.student)>=5

 

1729. Find Followers Count(SQL) (간단)

https://leetcode.com/problems/find-followers-count/

각 이용자에 대하여 이용자의 팔로우 수를 출력하는 문제이다

SELECT f.user_id, count(f.follower_id) followers_count
FROM Followers f
GROUP BY f.user_id
ORDER BY f.user_id;

오늘은 프로젝트가 끝나고 새로운 주차에 대한 발제가 있던 날이다. 앞으로 약 한주동안은 통계학 기초내용과 데이터 전처리&시각화를 배우게 된다. 그외는 내 기준에서 그리 특별한 내용은 없었던 것 같다.

그리고 오늘은 내배캠 신청할 쯤 같이 신청하였던 국민 취업 지원 제도가 1유형은 탈락하고 2유형은 선정되었다고 수요일날 연락와서 오늘 상담하기로 일정을 잡아 처음으로 내배캠 참여하고 공부시간 중에 외출을 다녀오게 되었다. 가서 약 한시간동안 이것 저것 질문에 답하면서 상담을 하고 돌아와서(생각보다 상세하게 파악하는 듯 했고 나도 내 상황에 최대한 자세히 알려드리고 나한테 맞는 조언과 도움을 받기위해서 내 나름대로 최대한 설명을 드렸다)

통계학 기초 강의 일부와 SQL 코드카타를 마저 풀었다. 통계학은 역시 예상대로 제대로 이해할려면 조금 어려울 것 같다는 느낌이 들지만 한편으로는 내가 물리학과 출신이라 아예 처음 접하는 것은 아니고 일부라도 겉핧기식으로 접한 적이 있어서 그런 점도 있는 것 같고 비록 용어는 통계쪽 관련 되어있다면 생소한 용어들이 좀 많은듯 했지만 그래도 생각보다는 아직까지는 흥미는 생기는 듯하다(아직 제대로 깊이 안들어가서 그럴 확률도 농후하지만 그래도 기회 닿고 능력이 닿는대로 통계쪽도 공부를 해둔다면 나의 능력이 될 것이기 때문에 한번 열심히 해볼 생각이다.)

그리고 코드카타에 대해서 조금 적자면 두번째 문제가 좀 시간이 많이 걸렸다(오늘 평소 외출을 거의 하지 않다가 상담때문에 나갔다가 왔기도 하고 프로젝트 기간동안의 피로 + 나가서 이동하는 과정에서 갑자기 뛰고 경보하고 하는 과정이 있어서 그런지 매우 피곤하고 살짝 두통이 와서 더 머리가 안돌아가서 그랬을 가능성도 조금 있다) 결과적으로는 내가 문제를 조금 잘못이해서 더 복잡한 방식에 대해 시도해보고 있었던 것이긴 했는데 이전 프로젝트 기간동안 틈틈히 풀었던 문제들도 한문제씩은 시간이 꽤 많이 소비되던 문제들이 있어 SQL코드카타 하루 평균 제출양이 3문제를 유지 시키고 싶었는데 안되지 않을까 생각하던 와중 다행히 그다음 문제들은 시간이 그렇게 오래걸리지 않는 간단한 문제들이 다수 있어서 평균 3문제를 회복시킬 수 있게 되었다(프로젝트 기간 2일 동안 한문제씩 밖에 못 풀어서 평균을 회복시킬려면 오늘 7문제를 풀어야했었는데 힘들거나 하지 못하고 주말에 회복시킬 줄 알았다.) 그래서 어렵지 않을까 했던 작은 목표가 달성되어서 기분이 좋았다.

그리고 팀원분들과 따로 소소한 뒷풀이를 추가적으로하면서 더욱 친분을 다지며 회포를 푸는 시간을 짧다면 짧게나마 가졌는데 새로운 인맥이 이렇게 다져져 가는구나라고 생각되어 좋았다. 뭔가 더 적을 만한 내용이 있었던 것 같은데 좀 피곤한 상태이기도 하고 이리저리 신경쓸게 많아져서 조금 정신 없던 날이기도 하여서 기억이 안나는 관계로 오늘은 여기서 마무리 짓도록 하겠다