Abstract / Summary

Given a list of investments and a year, count how many investments were made in that year.

Note

The full problem description, including helpful examples, can be found on LeetCode.

This problem is ranked as

and has an acceptance rate of about 52% (600k+ submissions).

Typical patterns to solve this problem:

• Database

Every single line of code was written by me, 100% from scratch, between August 2023 and August 2024.
However, two years later I set up this website and used various AI tools to generate most of the walkthrough / explanations below.

See this dedicated page for more details.

Overview

The script is a single-statement query that aggregates the 2016 total insured value (tiv_2016) from the Insurance table, but only for rows that satisfy two distinct uniqueness constraints. The first constraint isolates geographic points (latitude/longitude) that occur exactly once in the dataset. The second constraint isolates policy values from 2015 (tiv_2015) that appear in more than one record. By intersecting these two filtered sets, the query returns the rounded sum of tiv_2016 for the qualifying records.

Key Steps

Defining unique geographic locations

The first common table expression, named uniq, groups rows by latitude and longitude and retains only those groups whose count equals one. This step eliminates any coordinate that is shared by multiple policies, ensuring that only singular locations are considered later.

WITH uniq AS (
    SELECT lat, lon
    FROM Insurance
    GROUP BY lat, lon
    HAVING COUNT(*) = 1
)

Identifying duplicated 2015 values

The second common table expression, same, groups rows by the 2015 insured value and keeps only those groups whose count exceeds one. This filters for policy values that are not unique, i.e., values that appear in at least two different records.

, same AS (
    SELECT tiv_2015
    FROM Insurance
    GROUP BY tiv_2015
    HAVING COUNT(*) > 1
)

Joining the filtered sets and aggregating

The main SELECT statement joins the original Insurance table with the two CTEs using inner joins expressed in the legacy comma syntax. The join predicates enforce that a row from Insurance must match a unique latitude/longitude pair from uniq and a duplicated tiv_2015 value from same. After the join, the query computes the sum of tiv_2016 across all matching rows, rounds the result to two decimal places, and returns it as a single scalar column.

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM Insurance i, uniq u, same s
WHERE i.tiv_2015 = s.tiv_2015
  AND i.lat = u.lat
  AND i.lon = u.lon

Edge Cases Handled

The use of HAVING COUNT(*) = 1 in uniq automatically excludes any latitude/longitude pair that is missing from the table (count zero) as well as any pair that appears multiple times. Consequently, rows with null latitude or longitude values are filtered out because a null group would not satisfy the equality condition in the join, and the count for a null group would not meet the exact-one requirement.

Similarly, HAVING COUNT(*) > 1 in same guarantees that a tiv_2015 value must be present in at least two rows to be considered. This eliminates solitary entries and also prevents the inclusion of rows where tiv_2015 is null, because a null group would not satisfy the count condition and would not join on the equality predicate.

The final WHERE clause combines both filters, so a row is selected only when it simultaneously belongs to a unique geographic point and a duplicated 2015 value. This logical conjunction inherently handles the case where a row meets one condition but not the other, ensuring it is excluded from the aggregation.

Performance Optimizations

The query leverages early reduction of the data set through the two CTEs. By grouping and applying HAVING clauses before the join, the engine can materialize relatively small intermediate result sets-one containing distinct latitude/longitude pairs and another containing duplicated tiv_2015 values. Joining these reduced sets to the main table limits the number of rows that participate in the final aggregation, which can significantly lower I/O and CPU consumption compared with scanning the entire Insurance table for each condition.

If appropriate indexes exist on the columns used in grouping (lat, lon, tiv_2015) and on the join predicates, the database optimizer can perform index-only scans for the CTEs and use index lookups for the join, further reducing the amount of data read. The aggregation (SUM) operates on a narrowed subset, and the ROUND function is applied after the sum, avoiding repeated rounding operations on intermediate rows.

Overall, the structure of the query-filter-first, join-later, aggregate-last-aligns with common optimization patterns that minimize the volume of data processed at each stage.

Imagine there is a big collection of building insurance cards scattered on the floor. Each card has a special address spot marked by its latitude and longitude. Some cards sit all by themselves in their own part of town, while other cards are forced to share the same spot in town with many neighbors.

The first part of the project finds the special loners. It looks at all the addresses and picks out only the ones where there is just a single house standing there. Those are the houses that do not have to share their space.

The second part of the project looks for crowds. These are the insurance cards that share the exact same financial value as other cards in the pile. It keeps track of these popular values that appear more than once.

Finally, the code puts everything together. It looks through all the houses again to see which ones are the loners that also have a popular financial value. Once the houses that fit these two rules are found, the code adds up all their values from the second year to see the final total.

Several LLMs ranked my code quality on a scale from 1 (very poor) to 10 (outstanding):

not analyzed yet

I wrote a basic crawler to find other solutions to that problem.
Obviously, I did that after I solved the problem myself.

In general, AlgoMonster and WalkCCC solutions are high-quality and always worth a look.
I haven't checked most of the other solutions, but they might be helpful as well.

And of course, the LeetCode forum is always a good place (or even the best) to look for alternative approaches.

(click on a column header to sort the table)

	author
author	solutions
www	AlgoMonster		C++	Java	Python	TS
	WalkCCC	SQL
	leetcode.ca	SQL
	LeetcodeHelp		C++	Java	Python
	Progiez		C++	Java	Python
github	everthis	SQL
	suxrobGM	SQL
AlgoMonster	C++, Python, Java, TypeScript
WalkCCC	SQL
leetcode.ca	SQL
LeetcodeHelp	C++, Java, Python
Progiez	C++, Java, Python
everthis	SQL
suxrobGM	SQL

There are a few extra pages giving you some insights into my setup:

• Tech Stack - my development environment and tools
• AI - how AI helped me create this website (NOT solving the problems)
• Progress - progression over time
• Quality - code quality