Qualified Policy Investment Sum

Medium

Your insurance analytics warehouse stores one row per policy in a table named Insurance: pid: unique policy id tiv_2015: total investment value for 2015 tiv_2016: total investment value for 2016 lat: city latitude lon: city longitude You need a one-row summary metric for 2016 called tiv_2016. Include only policies that satisfy BOTH filters: The policy's tiv_2015 value is shared by at least one other policy. The policy's (lat, lon) pair is unique across the entire table. Return exactly one column: tiv_2016 Additional rules: Round the final sum to two decimal places. If no row qualifies, return 0.00. Solve this in both: SQL (SQLite 3.27.2) Pandas

Example 1

Input

Insurance:
| pid | tiv_2015 | tiv_2016 | lat  | lon  |
|-----|----------|----------|------|------|
| 1   | 10.00    | 5.00     | 10.0 | 10.0 |
| 2   | 20.00    | 20.00    | 20.0 | 20.0 |
| 3   | 10.00    | 30.00    | 20.0 | 20.0 |
| 4   | 10.00    | 40.00    | 40.0 | 40.0 |

Output

[
  {"tiv_2016":45.0}
]

Explanation

Rows 1 and 4 pass both conditions. Row 2 fails shared-2015, and row 3 fails unique-location. Total is 45.00.

Example 2

Input

Insurance:
| pid | tiv_2015 | tiv_2016 | lat | lon |
|-----|----------|----------|-----|-----|
| 10  | 50.00    | 12.25    | 1.0 | 1.0 |
| 11  | 60.00    | 22.00    | 2.0 | 2.0 |
| 12  | 70.00    | 18.75    | 3.0 | 3.0 |

Output

[
  {"tiv_2016":0.0}
]

Explanation

No tiv_2015 value repeats, so no row qualifies. Return 0.00.

Example 3

Input

Insurance:
| pid | tiv_2015 | tiv_2016 | lat | lon |
|-----|----------|----------|-----|-----|
| 21  | 30.00    | 10.00    | 5.0 | 5.0 |
| 22  | 30.00    | 15.00    | 5.0 | 5.0 |
| 23  | 30.00    | 25.00    | 6.0 | 6.0 |
| 24  | 40.00    | 35.00    | 7.0 | 7.0 |
| 25  | 40.00    | 45.00    | 8.0 | 8.0 |

Output

[
  {"tiv_2016":105.0}
]

Explanation

Rows 23, 24, and 25 pass both rules, giving 25 + 35 + 45 = 105.00.

Constraints

1 <= number of rows in Insurance <= 100,000
1 <= pid <= 1,000,000,000
0.00 <= tiv_2015 <= 1,000,000.00
0.00 <= tiv_2016 <= 1,000,000.00
-90.0 <= lat <= 90.0
-180.0 <= lon <= 180.0
pid is unique
lat and lon are non-null
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a one-row DataFrame with column tiv_2016

Insurance: | pid | tiv_2015 | tiv_2016 | lat | lon | |-----|----------|----------|------|------| | 1 | 10.00 | 5.00 | 10.0 | 10.0 | | 2 | 20.00 | 20.00 | 20.0 | 20.0 | | 3 | 10.00 | 30.00 | 20.0 | 20.0 | | 4 | 10.00 | 40.00 | 40.0 | 40.0 |

Insurance: | pid | tiv_2015 | tiv_2016 | lat | lon | |-----|----------|----------|-----|-----| | 10 | 50.00 | 12.25 | 1.0 | 1.0 | | 11 | 60.00 | 22.00 | 2.0 | 2.0 | | 12 | 70.00 | 18.75 | 3.0 | 3.0 |

Insurance: | pid | tiv_2015 | tiv_2016 | lat | lon | |-----|----------|----------|-----|-----| | 21 | 30.00 | 10.00 | 5.0 | 5.0 | | 22 | 30.00 | 15.00 | 5.0 | 5.0 | | 23 | 30.00 | 25.00 | 6.0 | 6.0 | | 24 | 40.00 | 35.00 | 7.0 | 7.0 | | 25 | 40.00 | 45.00 | 8.0 | 8.0 |