585 Investments in 2016

Description

Table: Insurance

• 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.

Problem Statement

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:

Output:

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.

Solution

In order to find the solution, you need to find all the records that have the same tiv_2015. This you can do using aggregation.

1SELECT tiv_2015
2    FROM Insurance
3    GROUP BY tiv_2015
4    HAVING COUNT(1) > 1;

Similarly, you need to find all records where the policy holder’s location is unique. Again, this can be done using aggregation.

1SELECT lat, lon
2    FROM Insurance
3    GROUP BY lat, lon
4    HAVING COUNT(1) = 1;

Now, you need to find sum of tiv_2016 for all records that have the same tiv_2015 and unique location. You also need to round the result to two decimal places.

 1WITH same_tiv_2015 AS (
 2    SELECT tiv_2015
 3        FROM Insurance
 4        GROUP BY tiv_2015
 5        HAVING COUNT(1) > 1
 6), unique_location AS (
 7    SELECT lat, lon
 8        FROM Insurance
 9        GROUP BY lat, lon
10        HAVING COUNT(1) = 1
11)
12SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
13    FROM Insurance
14    WHERE tiv_2015 IN (
15        SELECT tiv_2015 FROM same_tiv_2015
16    ) AND (lat, lon) IN (
17        SELECT lat, lon FROM unique_location
18    );

In this solution, I’ve used temporary views, but you could write them as a subquery as well.

 1SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
 2    FROM Insurance
 3    WHERE tiv_2015 IN (
 4        SELECT tiv_2015
 5            FROM Insurance
 6            GROUP BY tiv_2015
 7            HAVING COUNT(1) > 1
 8    ) AND (lat, lon) IN (
 9        SELECT lat, lon
10            FROM Insurance
11            GROUP BY lat, lon
12            HAVING COUNT(1) = 1
13    );