SQL Query

리트코드 SQL 문제 풀이

✅ 585 Investments in 2016

Write a solution to report the sum of all total investment values in 2016 tiv_2016, for all policyholders who :

• have the same tiv_2015 value as one or more other policyholders, and
• are not located in the same city as any other policyholder (i.e., the (lat, lon) attribute pairs must be unique)
  Round tiv_2016 to two decimal places
  The result format is in the following example.

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM Insurance
WHERE tiv_2015 IN (
    SELECT tiv_2015
    FROM Insurance
    GROUP BY tiv_2015
    HAVING COUNT(*) > 1
) AND (lat, lon) IN (
    SELECT lat, lon
    FROM Insurance
    GROUP BY lat, lon
    HAVING COUNT(*) = 1
)

문제 풀이의 핵심은 다음과 같다.
1. tiv_2015 값이 1개보다 많아야 되는 상황 (as one or more)
2. lat, lon 이 unique한 값이어야 되는 상황
3. 최종적으로 tiv_2016 합산 값을 소수점 둘째 자리로 표현해야 되는 상황.