sql notes: leetcode#585 investments in 2016

Problem

Write a query to print the sum of all total investment values in 2016 (TIV_2016), to a scale of 2 decimal places, for all policy holders who meet the following criteria:

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 (latitude, longitude) attribute pairs must be unique).

Input Format:
The insurance table is described as follows:

where PID is the policyholder’s policy ID, TIV_2015 is the total investment value in 2015, TIV_2016 is the total investment value in 2016, LAT is the latitude of the policy holder’s city, and LON is the longitude of the policy holder’s city.

Sample Input

Sample Output

Explanation

The first record in the table, like the last record, meets both of the two criteria.
The TIV_2015 value ‘10’ is as the same as the third and forth record, and its location 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 with 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.

Analysis

Traverse each row of insurance table, check whether there are other records having the same TIV_2015 first, and then check whether the (LAT, LON) pair is unique. Finally, find sum of TIV_2016 of records satisfying two criteria.

To check whether there are other records with the same TIV_2015, we can take another insurance table and check whether there are records having same TIV_2015 but different PID:

1
2

SELECT i1.TIV_2016 FROM insurance AS i1
WHERE EXISTS (SELECT i2.PID FROM insurance AS i2 WHERE i2.PID <> i1.PID AND i2.TIV_2015 = i1.TIV_2015)

Or, we can get all duplicate TIV_2015 first and then find the records whose TIV_2015 is one of duplicates:

1
2

SELECT TIV_2016 FROM insurance WHERE
TIV_2015 IN (SELECT TIV_2015 FROM insurance GROUP BY TIV_2015 HAVING COUNT(*) > 1)

Next, to check whether (LAT, LON) is unique, find all duplicates and get those pairs that are not in duplicates:

1
2
3

SELECT i1.TIV_2016 FROM insurance AS i1
WHERE EXISTS (SELECT i2.PID FROM insurance AS i2 WHERE i2.PID <> i1.PID AND i2.TIV_2015 = i1.TIV_2015) AND
(i1.LAT, i1.LON) NOT IN (SELECT LAT, LON FROM insurance GROUP BY LAT, LON HAVING COUNT(*) > 1)

Obviously, it is a good option to find all unique (LAT, LON) pairs and then get records whose (LAT, LON) is unique:

1
2
3

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

Finally, find the sum of TIV_2016:

1
2
3

SELECT SUM(i1.TIV_2016) AS TIV_2016 FROM insurance AS i1
WHERE EXISTS (SELECT i2.PID FROM insurance AS i2 WHERE i2.PID <> i1.PID AND i2.TIV_2015 = i1.TIV_2015) AND
(i1.LAT, i1.LON) NOT IN (SELECT LAT, LON FROM insurance GROUP BY LAT, LON HAVING COUNT(*) > 1);

1
2
3

SELECT SUM(TIV_2016) 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);

Solution

Solution 1

1
2
3

SELECT SUM(i1.TIV_2016) AS TIV_2016 FROM insurance AS i1
WHERE EXISTS (SELECT i2.PID FROM insurance AS i2 WHERE i2.PID <> i1.PID AND i2.TIV_2015 = i1.TIV_2015) AND
(i1.LAT, i1.LON) NOT IN (SELECT LAT, LON FROM insurance GROUP BY LAT, LON HAVING COUNT(*) > 1);

Solution 2

1
2
3

SELECT SUM(TIV_2016) 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);

Link

585. Investment in 2016
(中文版) SQL 笔记: Leetcode#585 Investments in 2016