Investments in 2016 | Leetcode 585 | SQL Interview Solutions

This chapter covers LeetCode Problem 585: Investments in 2016, a classic SQL interview question that tests your ability to combine aggregate functions, multi-column condition matching (IN with subqueries), grouping/filtering (GROUP BY and HAVING), and arithmetic rounding (ROUND).

1. Problem Statement and Schema Overview

We are given a single table named insurance containing policyholder information, investment values across different years, and geographic coordinates.

Table Schema: insurance

Objective

Write a SQL query to report the sum of all total investment values in 2016 (tiv_2016) for all policyholders who satisfy both of the following conditions:

1. Have the same tiv_2015 value as one or more other policyholders (i.e., tiv_2015 must not be unique; it must appear at least twice in the table).
2. Are not located in the same city as any other policyholder. Location is determined by the latitude (lat) and longitude (lon) pair. This pair must be completely unique across the entire table (i.e., the (lat, lon) combination must appear exactly once).

The final resulting sum of tiv_2016 must be rounded to 2 decimal places.

2. Core Mental Model & Real-World Analogy

Real-Life Analogy: The Real Estate Investment Club

Imagine you run an investment fund auditing real estate portfolios across different cities. You are looking for a very specific subset of clients to calculate a aggregate risk-metric payout for 2016:

• Condition 1 (Shared Financial Background): You want clients whose 2015 investment amount matches someone else in the database. They share financial company profiles with peers.
• Condition 2 (Isolated Geographic Footprint): Despite sharing 2015 investment sizes, their physical real estate location (lat, lon) must be completely solitary. No other client in the entire database can share that exact coordinate pair.

If a client satisfies both filters, their 2016 investment value (tiv_2016) is pooled into the final payout sum.

3. Step-by-Step Sample Walkthrough

Let's examine a sample dataset to see how rows get filtered and evaluated.

Sample Input Table: insurance

Evaluating Condition 1: Unique Location (lat, lon) Check

We look for coordinate pairs that appear only once (count = 1):

• (10, 10) appears 1 time (Row 1) $\rightarrow$ Unique (Valid)
• (20, 20) appears 2 times (Rows 2 & 3) $\rightarrow$ Duplicate (Excluded)
• (40, 40) appears 1 time (Row 4) $\rightarrow$ Unique (Valid)

Remaining candidate rows after Condition 1: Row 1 (pid = 1) and Row 4 (pid = 4).

Evaluating Condition 2: Shared 2015 Investment (tiv_2015) Check

Among our remaining candidates (Row 1 and Row 4), their tiv_2015 value is 10. We must verify that 10 appears more than once in the overall table:

• In the original table, tiv_2015 = 10 appears in Row 1, Row 3, and Row 4 (Total count = 3 > 1).
• Therefore, the condition is satisfied.

Final Calculation

Both Row 1 and Row 4 satisfy both conditions. We sum their tiv_2016 values:

Rounded to 2 decimal places: 45.00.

4. Naive Approach vs. Production-Grade Subquery Strategy

The Naive Approach Pitfalls

Trying to solve this with complex self-joins (JOIN insurance i2 ON i.lat = i2.lat...) leads to combinatorial explosions, bloated query plans, and incorrect duplicate row counting when matching multiple conditions simultaneously.

The Clean Subquery & Filtering Strategy

Instead of self-joins, we isolate each rule into independent subqueries using the IN operator combined with GROUP BY and HAVING:

1. Financial Subquery: Identifies tiv_2015 values with a frequency greater than 1.
2. Geographic Subquery: Identifies coordinate pairs (lat, lon) with a frequency equal to 1.
3. Main Query: Computes the rounded sum of tiv_2016 where rows match both subquery sets.

5. Complete SQL Query Implementation

Here is the complete, optimized, and verified MySQL 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
    );

6. Detailed Query Breakdown & Execution Flow

Let's dissect how the database engine executes this statement step-by-step:

1. The Outer Selection & Aggregation

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM insurance

The database scans the insurance table, filters rows based on the WHERE clause, aggregates tiv_2016 using the SUM() function, and formats the numeric output using ROUND(..., 2).

2. Condition 1: Shared 2015 Investments

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

• Grouping: Groups rows by their 2015 investment values.
• Filtering: HAVING COUNT(*) > 1 retains only those tiv_2015 values that appear multiple times across the table.
• Matching: The outer query keeps only rows whose tiv_2015 exists inside this resulting list.

3. Condition 2: Unique Locations

AND (lat, lon) IN (
    SELECT lat, lon
    FROM insurance
    GROUP BY lat, lon
    HAVING COUNT(*) = 1
)

• Grouping: Groups rows by the composite pair (lat, lon).
• Filtering: HAVING COUNT(*) = 1 retains strictly unique geographic coordinates.
• Matching: Combines with the first condition using AND, ensuring only policyholders meeting both independent criteria make it to the summation step.

7. Execution Flow Diagram

Summary and Key Takeaways

• Multi-Condition Filtering via Subqueries: When filtering a table based on two completely different aggregated properties, separate them into distinct IN subqueries connected by logical operators (AND).
• Composite Column Matching: SQL supports multi-column row tuples in IN clauses, such as (lat, lon) IN (SELECT lat, lon ...), making coordinate and composite key evaluations clean and concise.
• GROUP BY with HAVING: Use GROUP BY paired with HAVING COUNT(*) > 1 for finding duplicates, and HAVING COUNT(*) = 1 for isolating unique rows.
• Aggregation Formatting: Always wrap floating-point financial sums in ROUND(val, 2) to match expected precision standards in reporting and interview test suites.
• Primary Key Safety: Remember that pid guarantees row-level distinction, but aggregations must group strictly by the target business attributes (tiv_2015 or coordinate pairs).