LeetCode 585: Investments in 2016

Problem Restatement

We are given an Insurance table.

Each row represents one policyholder and includes:

1. Their policy ID.
2. Their total investment value in 2015.
3. Their total investment value in 2016.
4. Their location as latitude and longitude.

We need to report the sum of tiv_2016 for policyholders who satisfy both conditions:

1. Their tiv_2015 value is shared by at least one other policyholder.
2. Their (lat, lon) location is unique in the table.

The final sum must be rounded to two decimal places.

Table

Insurance

pid is the primary key.

Example

Input:

Output:

Policy 1 has tiv_2015 = 10, which is shared by policies 3 and 4. Its location (10, 10) is unique, so it qualifies.

Policy 2 has tiv_2015 = 20, which is not shared by another policyholder, so it does not qualify.

Policy 3 has tiv_2015 = 10, but its location (20, 20) is shared with policy 2, so it does not qualify.

Policy 4 has tiv_2015 = 10 and unique location (40, 40), so it qualifies.

The sum is:

5 + 40 = 45

So the result is:

45.00

First Thought: Sum All 2016 Investments

A first attempt might be:

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM Insurance;

This sums every policyholder’s 2016 investment.

But the problem asks for only policyholders that pass both filters:

1. Repeated tiv_2015.
2. Unique (lat, lon).

So we need to filter rows before summing.

Key Insight

The row qualifies based on two group-level facts.

First, the tiv_2015 value must appear more than once:

GROUP BY tiv_2015
HAVING COUNT(*) > 1

Second, the (lat, lon) pair must appear exactly once:

GROUP BY lat, lon
HAVING COUNT(*) = 1

Then the main query keeps only rows whose values belong to those qualifying groups.

Algorithm

1. Find all tiv_2015 values that appear more than once.
2. Find all (lat, lon) pairs that appear exactly once.
3. Select rows from Insurance that satisfy both conditions.
4. Sum their tiv_2016 values.
5. Round the result to two decimal places.

Correctness

The first subquery groups rows by tiv_2015 and keeps only groups with count greater than 1. Therefore, a row passes this condition exactly when its 2015 investment value is shared by at least one other policyholder.

The second subquery groups rows by (lat, lon) and keeps only groups with count equal to 1. Therefore, a row passes this condition exactly when no other policyholder has the same location.

The main query applies both filters with AND, so it keeps exactly the policyholders that satisfy both requirements. It then sums tiv_2016 over exactly those rows and rounds the final result to two decimal places.

Therefore, the query returns the required value.

Complexity

Let:

n = number of rows in Insurance

The exact execution plan depends on indexes and the SQL engine.

Implementation

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
);

Code Explanation

This subquery finds repeated 2015 investment values:

SELECT tiv_2015
FROM Insurance
GROUP BY tiv_2015
HAVING COUNT(*) > 1

A tiv_2015 value qualifies only if at least two policyholders have it.

This subquery finds unique locations:

SELECT lat, lon
FROM Insurance
GROUP BY lat, lon
HAVING COUNT(*) = 1

A (lat, lon) pair qualifies only if exactly one policyholder has that location.

The main WHERE clause combines both requirements:

WHERE tiv_2015 IN (...)
AND (lat, lon) IN (...)

Then the query sums the qualifying 2016 investment values:

SUM(tiv_2016)

Finally, the result is rounded:

ROUND(SUM(tiv_2016), 2) AS tiv_2016

Window Function Alternative

We can also compute the two counts with window functions.

WITH counted AS (
    SELECT
        tiv_2016,
        COUNT(*) OVER (PARTITION BY tiv_2015) AS same_tiv_2015_count,
        COUNT(*) OVER (PARTITION BY lat, lon) AS same_location_count
    FROM Insurance
)
SELECT
    ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM counted
WHERE same_tiv_2015_count > 1
  AND same_location_count = 1;

This version often reads more directly:

1. Count how many rows share the same tiv_2015.
2. Count how many rows share the same location.
3. Keep rows where the first count is greater than 1 and the second count is 1.

Testing

Sample data:

CREATE TABLE Insurance (
    pid INT PRIMARY KEY,
    tiv_2015 FLOAT,
    tiv_2016 FLOAT,
    lat FLOAT,
    lon FLOAT
);

INSERT INTO Insurance (pid, tiv_2015, tiv_2016, lat, lon) VALUES
(1, 10, 5, 10, 10),
(2, 20, 20, 20, 20),
(3, 10, 30, 20, 20),
(4, 10, 40, 40, 40);

Query:

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
);

Expected result:

Additional test cases: