Date: February 09, 2026
Category: SQL
Time Taken: 15 minutes
Difficulty: Medium

---

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.

Link: Investments in 2016

---

Approach 1 (Subquery):

We can solve this using subqueries by selecting tiv_2015 values that appear more than once and lat, lon pairs that appear only once. This means choosing those with the same tiv_2015 but different lat and lon.

Solution Code:

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
)

---

Approach 2 (Window Functions):

• A more modern approach is to use window functions. Use COUNT() and partition by tiv_2015 for tiv_count and by lat and lon for location_count. Store this in a CTE.

• Then, simply sum and round the tiv_2016 values where tiv_count is greater than 1 and location_count equals 1.

Solution Code:

WITH counts AS (
    SELECT 
        tiv_2016,
        COUNT(*) OVER(PARTITION BY tiv_2015) AS tiv_count,
        COUNT(*) OVER(PARTITION BY lat, lon) AS loc_count
    FROM Insurance
)

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM counts
WHERE tiv_count > 1 AND loc_count = 1

Pattern: Subquery | Window Functions

Mistakes I Made: Did not know how to solve it and even after I did was not able to solve it using window functions without help.

Series: 90 Days of Data Engineering Progress: 38/90 problems completed

Tags: #DEQuest #LeetCode #SQL #DataEngineering #BuildInPublic