문제

585. Investments in 2016
Write a solution to report the sum of all total investment values in 2016 tiv_2016, for all policyholders who:
(1) have the same tiv_2015 value as one or more other policyholders,
(2) 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.

서로 다른 pid들 중에서
(1) 중복된 tiv_2015가 존재하면서
(2) (lat, lon)은 유일한 (중복되지 않는) 경우의
tiv_2016의 합을 구하는 문제

2가지 조건을 처리하면서 SELF JOIN을 사용하면 아주 어려운 문제는 아니었지만,
다른 사람의 풀이법 중 윈도우 함수를 사용한 부분은 생각치도 못했던 터라 정리하게 되었다.

풀이

SELF JOIN 사용

문제에서 요구하는 조건을 CTE로 추려내고
SELF JOIN으로 조건식을 걸어서 원하는 값만 골라내는 방식

하나 주의할 점은, 보통 JOIN을 할 때는 id1 = id2로 조건을 거는 경우가 많은데,
해당 문제는 "id가 다르면서"가 전제된다는 점.

또한 그렇기에 (lat, lon)이 유일하다는 조건은 ON절에 바로 사용하기 어렵고
(lat, lon)이 유일하지 않은 경우의 pid를 뽑아내어 NOT IN 으로 연결하는 편이 수월하다

WITH policy_holders_in_same_city AS (
    SELECT
        i1.pid
    FROM
        Insurance i1
        INNER JOIN Insurance i2
            ON ((i1.lat = i2.lat) AND (i1.lon = i2.lon))
            AND (i1.pid != i2.pid)
),
find_correct_policy_holders AS (
SELECT DISTINCT
    i1.pid
    , i1.tiv_2016
FROM
    Insurance i1
    INNER JOIN Insurance i2
        ON (i1.pid != i2.pid)
        AND (i1.tiv_2015 = i2.tiv_2015)
WHERE
    i1.pid NOT IN (SELECT pid FROM policy_holders_in_same_city)
)
SELECT
    ROUND(
        SUM(tiv_2016)
        , 2
    ) AS "tiv_2016"
FROM
    find_correct_policy_holders
;

HAVING 사용

해당 문제에서 pid는 사실 크게 중요하지 않다
(1) 중복된 tiv_2015가 존재
(2) (lat, lon)은 유일한 (중복되지 않는) 경우
이 조건만 만족하면 되기 때문에, COUNT()를 사용해서 각 조건의 경우의 수를 구하는 것으로도 문제를 풀 수 있다.

그렇기에 아래와 같이 단순 HAVING 조건만으로도 문제를 해결할 수 있다.

WITH duplicated_tiv_2015 AS (
SELECT
    tiv_2015
FROM
    Insurance
GROUP BY
    tiv_2015
HAVING
    count(*) > 1
), 
only_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 * FROM duplicated_tiv_2015)
    AND (lat, lon) in (SELECT * FROM only_lat_lon)
;

윈도우 함수 사용

앞서 HAVING을 사용한 풀이법의 연장선인데
결국 COUNT()만 구할 것이라면 CTE를 사용할 것도 없이 윈도우 함수에 PARTITION BY만 잘 나눠주어도 풀 수 있다.

WITH preprocessing AS (
    SELECT
        tiv_2016
        , COUNT(*) over (partition by tiv_2015) as "tiv_2015_cnt"
        , COUNT(*) over (partition by lat, lon) AS "city_cnt"
    FROM
        Insurance
)
SELECT
    ROUND(
        SUM(tiv_2016)
        , 2
    ) AS "tiv_2016"
FROM
    preprocessing
WHERE
    tiv_2015_cnt > 1
    AND city_cnt = 1    
;