[LeetCode/SQL] 585. Investments in 2016

[LeetCode/SQL]

---

📌 585. Investments in 2016

문제

풀이

- 모든 고객들 중에서, 2016년에 투자한 총 가치 tiv_2016의 합계를 출력하라
- 같은 tiv_2015 값을 가진 하나 이상의 다른 고객이 있는 고객들을 선택
- 선택된 고객들 중에서, 도시가 동일하지 않은 (즉, (lat, lon) 속성 쌍이 고유한) 고객들을 선택

방법 I

WITH tiv_2015_counts AS (
    SELECT tiv_2015
    FROM Insurance
    GROUP BY tiv_2015
    HAVING COUNT(*) > 1
),
unique_lat_lon AS (
    SELECT lat, lon
    FROM Insurance
    GROUP BY lat, lon
    HAVING COUNT(*) = 1
)

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM Insurance
WHERE tiv_2015 IN (SELECT tiv_2015 FROM tiv_2015_counts)
    AND (lat, lon) IN (SELECT lat, lon FROM unique_lat_lon);

방법 II

SELECT ROUND(SUM(tiv_2016) AS tiv_2016
FROM 
( SELECT *
        ,COUNT(*) OVER (PARTITION BY tiv_2015) AS cnt1
        ,COUNT(*) OVER (PARTITION BY lat,lon) AS cnt2
) AS temp
WHERE cnt1>1 AND cnt2=1