2024-01-16

SQL 코드카타

 

1251. Average Selling Price(SQL) (with, case when, round, left join, where, on조건으로 between사용) (굉장히 여러 번 시도한 쿼리)

https://leetcode.com/problems/average-selling-price/description/

특정 기간마다 가격이 달라지는 상품에 대하여 기간과 가격이 나와 있는 테이블과 상품이 팔린날짜와 수량이 적혀있는 테이블을 통해 상품이 팔린 평균 가격을 구하는 문제이다.

SELECT s.product_id,
         ROUND(SUM(s.units * p.price)/SUM(s.units),2) average_price
FROM UnitsSold s
LEFT JOIN Prices p ON s.product_id = p.product_id 
    AND s.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY s.product_id

#tase case2
#prices
| product_id | start_date | end_date   | price |
| ---------- | ---------- | ---------- | ----- |
| 1          | 2019-02-17 | 2019-02-28 | 5     |
| 1          | 2019-03-01 | 2019-03-22 | 20    |
| 2          | 2019-02-01 | 2019-02-20 | 15    |
| 2          | 2019-02-21 | 2019-03-31 | 30    |
| 3          | 2019-02-21 | 2019-03-31 | 30    |

#unitsSold
| product_id | purchase_date | units |
| ---------- | ------------- | ----- |
| 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01    | 15    |
| 2          | 2019-02-10    | 200   |
| 2          | 2019-03-22    | 30    |

처음 시도는 UnitsSold테이블에 Prices테이블을 product_id를 공통 컬럼으로 가지고 purchase_date가 start_date와 end_date사이에 있게 하는 조건으로 left join해준뒤 product_id를 기준으로 그룹을 지은 후 product_id와 각 sum(units*price)/sum(units)으로 각 갯수 별로 해당 기간의 대응되는 가격에 곱하여 총 팔린 액수/총 팔린 갯수 하고 소수 둘째짜리까지 반올림하여 표시하도록 하였는데

이 경우 left join해주었기에 u테이블에는 3번이 없기 때문에 따라서 id 3에 관련하여 아무것도 나오지 않아 오답처리 되었다

SELECT s.product_id,
         ROUND(SUM(s.units * p.price)/SUM(s.units),2) average_price
FROM Prices p
LEFT JOIN UnitsSold s ON s.product_id = p.product_id 
    AND s.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY s.product_id

#test case 3
#prices
| product_id | start_date | end_date   | price |
| ---------- | ---------- | ---------- | ----- |
| 1          | 2019-01-17 | 2019-01-25 | 18    |
| 1          | 2019-01-26 | 2019-02-12 | 5     |
| 2          | 2019-01-23 | 2019-01-30 | 3     |
| 2          | 2019-01-31 | 2019-02-12 | 18    |

#unitsSold
| product_id | purchase_date | units |
| ---------- | ------------- | ----- |
| 1          | 2019-01-17    | 5     |
| 1          | 2019-01-31    | 4     |
| 2          | 2019-01-29    | 1     |
| 2          | 2019-01-29    | 2     |
#Out
| product_id | average_price |
| ---------- | ------------- |
| 1          | 12.22         |
| 2          | 3             |
| null       | null          |
#Expected
| product_id | average_price |
| ---------- | ------------- |
| 1          | 12.22         |
| 2          | 3             |

이거는 그래서 혹시 left join의 순서를 바꿔주면 될려나하는 단순한 생각에 해봤던 건데

이 경우 3은 여전히 없기 때문에 행은 생겼으나 관련 값이 다 null로 되어있어 null로 다 나왔다

SELECT p.product_id,
         CASE WHEN s.units IS NULL THEN 0
         ELSE ROUND(SUM(s.units * p.price)/SUM(s.units),2) 
         END average_price
FROM Prices p
LEFT JOIN UnitsSold s ON s.product_id = p.product_id 
    AND s.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id

#test case 4
#prices
| product_id | start_date | end_date   | price |
| ---------- | ---------- | ---------- | ----- |
| 1          | 2019-01-18 | 2019-01-22 | 3     |
| 1          | 2019-01-23 | 2019-01-25 | 15    |
| 2          | 2019-01-21 | 2019-01-29 | 9     |
| 2          | 2019-01-30 | 2019-02-04 | 4     |

#unitsSold
| product_id | purchase_date | units |
| ---------- | ------------- | ----- |
| 1          | 2019-01-24    | 3     |
| 1          | 2019-01-25    | 4     |
| 2          | 2019-02-03    | 2     |
| 2          | 2019-02-03    | 5     |

이번에는 다시 처음시도 대로 join의 순서를 되돌려 주었고, 그 뒤 case when을 사용하여 units이 null이면 0이고 아니면 값을 계산해주도록 하면 되지 않을까 생각해서 그렇게 쿼리를 수정하였다. 기존에 실패했던 tc(test case)들은 다 맞게 되었지만 위 경우 주어진 한 상품당 기간별 가격중 하나의 경우에 대해서만 두 개씩의 unitssold데이터가 매칭되기에 각자 먼저있는 가격에 대해서는 units이 null이 나오게 되어 그냥 계산하지않고 바로 0으로 표시되게 되어버린다.

SELECT p.product_id,
        ROUND(
            SUM((CASE WHEN s.units IS NULL THEN 0 ELSE s.units) * p.price)
                /SUM(CASE WHEN s.units IS NULL THEN 0 ELSE s.units)
            ,2) average_price
FROM Prices p
LEFT JOIN UnitsSold s ON s.product_id = p.product_id 
    AND s.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id
#들여쓰기 이렇게 하는 건 에러나는듯?
#You have an error in your SQL syntax; check the manual that corresponds to your 
#MySQL server version for the right syntax to use near ') * p.price)
#                /SUM(CASE WHEN s.units IS NULL THEN 0 ELSE s.units)' at line 4

#몇차례 더 테스트 해보니 들여쓰기 문제가 아니라 안에 case when넣은 게 문제인듯?

그래서 sum과 곱해주기 전에 units에 대하여 case when을 사용하여 조건에 따른 값을 쓰게하면 되지 않을까해서 그렇게 해봤지만 에러가 났다. 처음에는 어디부분에서 정확히 에러가 난지 몰라서 이것 저것 없애면서 테스트해본 결론은 주석에 적어 둔대로 들여쓰기 문제가 아닌(제일 처음에는 가독성 좋게 할려고 /를 줄바꿔서 쓴 것이 문제인가 생각했다→관련하여 차마 /의 들여쓰기 문제인가는 직접적으로 테스트 해보지 못했지만, 다른 여러 테스트 결과로 생각해볼 때) case when의 사용이 잘못되었음이 문제일듯하다고 판단 내렸다

WITH update_unitsold AS(
    SELECT p.product_id, s.purchase_date,
        CASE WHEN s.units IS NULL THEN 0 ELSE s.units END units
    FROM Prices p
    LEFT JOIN UnitsSold s ON s.product_id = p.product_id 
        AND s.purchase_date BETWEEN p.start_date AND p.end_date

)

SELECT p.product_id, 
        ROUND(SUM(u.units * p.price)/SUM(u.units),2) average_price
FROM Prices p
LEFT JOIN update_unitsold u ON u.product_id = p.product_id 
    AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id

#tc2에 대한 out
| product_id | average_price |
| ---------- | ------------- |
| 1          | 6.96          |
| 2          | 16.96         |
| 3          | null          |

위와 같이 with구문을 해주어 따로 먼저 units에 관련해서 null값을 처리해주고 그뒤 그 테이블을 이용하여 left join해주어 계산을 진행하게 하였는데 이경우 다시 tc2에 대하여 오답이 나왔다 미리 null값을 다 처리해 주었는데 왜 null값으로 다시 나왔을까를 생각하면서 계산 부분을 제외하고 각 컬럼에 대해 값이 어떻게 처리되었는가를 확인하기 위해 몇몇 부분을 주석처리하여 아래 쿼리와 같이 해주었다

WITH update_unitsold AS(
    SELECT p.product_id, s.purchase_date,
        CASE WHEN s.units IS NULL THEN 0 ELSE s.units END units
    FROM Prices p
    LEFT JOIN UnitsSold s ON s.product_id = p.product_id 
        AND s.purchase_date BETWEEN p.start_date AND p.end_date

)

SELECT p.product_id, p.price, u.units
        #ROUND(SUM(u.units * p.price)/SUM(u.units),2) average_price
FROM Prices p
LEFT JOIN update_unitsold u ON u.product_id = p.product_id 
    AND u.purchase_date BETWEEN p.start_date AND p.end_date
#GROUP BY p.product_id

#out
| product_id | price | units |
| ---------- | ----- | ----- |
| 1          | 5     | 100   |
| 1          | 20    | 15    |
| 2          | 15    | 200   |
| 2          | 30    | 30    |
| 3          | 30    | null  |

그랬더니 위와 같은 결과가 나왔고 위 쿼리 결과도 3에 대해 null이 나왔기 때문에 위에서 한 쿼리 역시 null값이 나왔음을 확인할 수 있었다.

이 쿼리가 null값이 나온 이유로는 아래 with로 선언한 임시테이블을 조회해 봄으로써 확인가능했는데

WITH update_unitsold AS(
    SELECT p.product_id, s.purchase_date,
        CASE WHEN s.units IS NULL THEN 0 ELSE s.units END units
    FROM Prices p
    LEFT JOIN UnitsSold s ON s.product_id = p.product_id 
        AND s.purchase_date BETWEEN p.start_date AND p.end_date

)

select *
FROM update_unitsold

#out
| product_id | purchase_date | units |
| ---------- | ------------- | ----- |
| 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01    | 15    |
| 2          | 2019-02-10    | 200   |
| 2          | 2019-03-22    | 30    |
| 3          | null          | 0     |

units에 대해서는 처리해주었지만 purchase_date의 값은 여전히 null값이기 때문에 아래 join하는 과정에서 조건이 만족되지않아 update_unitsold테이블의 id 3에 대한 값은 다 null로 되었을 것이고 따라서 null이 나왔을 것이라 생각된다.

하지만 만약 여기서 case when으로 purchase_date를 0 같은 값으로 처리해 주었더라도 여전히 join의 조건인 u.purchase_date BETWEEN p.start_date AND p.end_date를 만족하지 못하여 null값이 나올 것이라 판단되었다

따라서 메인쿼리문에서 join의 조건을 조금 고치고 그 조건을 where로 넣어서 필터링 해주되, units=0인 경우도 가져오도록 조건을 적어주었다

WITH update_unitsold AS(
    SELECT p.product_id, s.purchase_date,
        CASE WHEN s.units IS NULL THEN 0 ELSE s.units END units
    FROM Prices p
    LEFT JOIN UnitsSold s ON s.product_id = p.product_id 
        AND s.purchase_date BETWEEN p.start_date AND p.end_date

)

SELECT p.product_id, 
        ROUND(SUM(u.units * p.price)/SUM(u.units),2) average_price
FROM Prices p
LEFT JOIN update_unitsold u ON u.product_id = p.product_id 
WHERE (u.purchase_date BETWEEN p.start_date AND p.end_date)
        OR u.units=0
GROUP BY p.product_id

위와 같이 작성하였지만, 동일하게 내가 tc(test case)2번 통과 못함 3번 값이 0으로 나누는 것 때문에 null로 나와버렸다

WITH update_unitsold AS(
    SELECT p.product_id, s.purchase_date,
        CASE WHEN s.units IS NULL THEN 0 ELSE s.units END units
    FROM Prices p
    LEFT JOIN UnitsSold s ON s.product_id = p.product_id 
        AND s.purchase_date BETWEEN p.start_date AND p.end_date

)

SELECT p.product_id, 
        CASE WHEN SUM(u.units) =0 THEN 0
        ELSE ROUND(SUM(u.units * p.price)/SUM(u.units),2)
        END average_price
FROM Prices p
LEFT JOIN update_unitsold u ON u.product_id = p.product_id 
WHERE (u.purchase_date BETWEEN p.start_date AND p.end_date)
        OR u.units=0
GROUP BY p.product_id

 그래서 최종적으로 추가로 메인 쿼리문에도 case when을 추가하여 SUM(u.units) =0를 기준으로 하여(u.units=0으로 할 경우 또 다시 위의 시도중 하나처럼 상품에대해 통채로 0으로 되어버릴 수 있기 때문에 모든 구매갯수가 0일 때로 해주었다) 그럴 경우 0으로 처리하고 아닐경우 계산하여 계산 값을 표시하도록해주었다. 그랬더니 드디어 무사히 제출완료되었다

그리고 추가 여담으로 미리 설명 적어둘 시간이 부족해서 밤에 다시 적을려고 실패했던 쿼리들 다시 넣어보고 결과보면서 적을려고 했는데 테스트케이스는 저장되지 않는 것인지 날라가고 없었다 이점을 기억해둬야 할듯하다(그리고 가능한 남겨두고 싶은 테스트 케이스는 따로 복붙해둬야겠다)

 

1075. Project Employees I(SQL) (round,avg, left join, group by)

https://leetcode.com/problems/project-employees-i/description/

각 프로젝트 별로 참여하는 모든 직원들의 경력의 평균을 프로젝트id당으로 소수 둘째짜리까지 표시해서 반올림한 값으로 출력하는 문제이다.

SELECT p.project_id, ROUND(AVG(e.experience_years),2) average_years
FROM Project p LEFT JOIN Employee e ON p.employee_id = e.employee_id
GROUP BY p.project_id

문제대로 작성해주었더니 별 문제없이 해결되는 간단한 문제였다

 

1633. Percentage of Users Attended a Contest(SQL) (select 서브쿼리, round)

https://leetcode.com/problems/percentage-of-users-attended-a-contest/description/

각 콘테스트에 이용자가 전체 이용자중 몇퍼센트 등록했는지 출력해주는 문제이다(퍼센트는 소수 둘째자리까지 표시하여 반올림)

SELECT r.contest_id, 
    ROUND(100*COUNT(r.user_id)/(SELECT COUNT(user_id) FROM Users u),2) percentage
FROM Register r
GROUP BY r.contest_id
ORDER BY percentage DESC, r.contest_id;

마찬가지로 문제대로 작성해주었더니 별 문제없이 해결되는 간단한 문제였다

오늘도 SQL코드카타만 3문제 정도 풀고 특별한 개인공부없이 주로 프로젝트 관련하여 필요한 부분 공부하거나 프로젝트 관련 내용 토의, 토론, 고민을 하였다

오늘은 프로젝트 진도 진행도가 예상보다 너무 나가지 못하였는 듯하다 중간, 중간 예상한 것보다 더 고려해줘야하는 부분이 있었다거나, 관련하여 의견이 다르게 나와서 서로 조율하는 과정에서 시간을 더 소비하였고, 그러다 보니 계획보다 더 진행하지 못하였는듯하다. 뭔가 그래도 이것 저것 하는 법 알아본다고 공부한 내용은 적지 않아서 적을까 고민하기도 했으나, 어제와 마찬가지로 따로 TIL에 올릴 생각으로 간단히라도 적어두지않아서(따로 필요할 때 다시 볼려고 노션에 스크랩형식?느낌정도로 참고한 곳 링크, 간단한 메모정도는 되어있지만 이정도를 굳이 바로 블로그에 올리기엔 굳이라는 생각과 올리기위해 다시 정리해서 올리자니 시간이 너무 많이 소모될듯하여) 차후 여유가되면 고민해볼듯하다