[SQL 문제 풀이] Investments in 2016 (2016년 투자)

Jun 16, 2025

[SQL 문제 풀이] Investments in 2016 (2016년 투자)

Contents

Investments in 2016 - LeetCode

Can you solve this real interview question? Investments in 2016 - Table: Insurance +-------------+-------+ | Column Name | Type | +-------------+-------+ | pid | int | | tiv_2015 | float | | tiv_2016 | float | | lat | float | | lon | float | +-------------+-------+ pid is the primary key (column with unique values) for this table. Each row of this table contains information about one policy where: pid is the policyholder's policy ID. tiv_2015 is the total investment value in 2015 and tiv_2016 is the total investment value in 2016. lat is the latitude of the policy holder's city. It's guaranteed that lat is not NULL. lon is the longitude of the policy holder's city. It's guaranteed that lon is not NULL. 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. Example 1: Input: Insurance table: +-----+----------+----------+-----+-----+ | pid | tiv_2015 | tiv_2016 | lat | lon | +-----+----------+----------+-----+-----+ | 1 | 10 | 5 | 10 | 10 | | 2 | 20 | 20 | 20 | 20 | | 3 | 10 | 30 | 20 | 20 | | 4 | 10 | 40 | 40 | 40 | +-----+----------+----------+-----+-----+ Output: +----------+ | tiv_2016 | +----------+ | 45.00 | +----------+ Explanation: The first record in the table, like the last record, meets both of the two criteria. The tiv_2015 value 10 is the same as the third and fourth records, and its location is unique. The second record does not meet any of the two criteria. Its tiv_2015 is not like any other policyholders and its location is the same as the third record, which makes the third record fail, too. So, the result is the sum of tiv_2016 of the first and last record, which is 45.

https://leetcode.com/problems/investments-in-2016/description/


2016년 tiv_2016년에 모든 총 투자 가치의 합계를 보고하는 솔루션을 작성하세요:

* 다른 하나 이상의 보험 가입자와 동일한 tiv_2015 값을 가지며,
* 다른 보험 가입자와 같은 도시에 위치하지 않습니다(즉, (라트, 론) 속성 쌍은 고유해야 함).
tiv_2016을 소수점 두 자리로 반올림합니다.

내가 작성한 쿼리

Oracle


SELECT 
    ROUND(SUM(tiv_2016), 2) AS tiv_2016  -- 조건을 만족하는 정책의 2016 투자 금액 합계를 소수점 2자리까지 반올림
FROM 
    Insurance i
WHERE 
    EXISTS (
        -- 같은 tiv_2015를 가진 다른 사람이 존재해야 함 (즉, 중복된 값)
        SELECT 1 
        FROM Insurance 
        WHERE tiv_2015 = i.tiv_2015 
        AND pid != i.pid
    )
    AND NOT EXISTS (
        -- 같은 위치(lat, lon)를 가진 다른 사람이 없어야 함 (위치가 유일해야 함)
        SELECT 1 
        FROM Insurance 
        WHERE lat = i.lat AND lon = i.lon 
        AND pid != i.pid
    );

MySQL


SELECT 
    ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM 
    Insurance
WHERE 
    tiv_2015 IN (
		    -- 중복된 tiv_2015를 가진 pid만 포함
        SELECT tiv_2015
        FROM Insurance
        GROUP BY tiv_2015
        HAVING COUNT(*) > 1
    )
    AND (lat, lon) IN (
		    -- lat, lon이 유일한 위치만 포함 (즉, 같은 도시에 다른 policyholder가 없는 경우)
        SELECT lat, lon
        FROM Insurance
        GROUP BY lat, lon
        HAVING COUNT(*) = 1
    );

차이점

다중 컬럼 조합 조건 사용 ((lat, lon) IN (...))

MySQL : 지원
Oracle : 11g 이상에서 지원 (단, 일부 환경에서는 EXISTS 또는 조인으로 변경 필요)

See more posts

[SQL 문제 풀이] Investments in 2016 (2016년 투자)

내가 작성한 쿼리

Oracle

MySQL

차이점

More articles

[JAVA 문제 풀이] 322. 공원 산책

[SQL 문제 풀이] Customers Who Bought All Products (모든 제품을 구매한 고객)

[JAVA 문제 풀이] 321. 프로세스

[JAVA 문제 풀이] 320. 개인정보 수집 유효기간