Calc total TIV_2016 for all policyholders who have the same TIV_2015 as one or more other policyholders, and are not located in the same city (lat, lon) as any other policyholder. Round to 2 decimals.
This challenge now meets the current minimum standard for sitemap inclusion and independent indexing.
Calc total TIV_2016 for all policyholders who have the same TIV_2015 as one or more other policyholders, and are not located in the same city (lat, lon) as any other policyholder. Round to 2 decimals.
This problem combines two different set filters on the same table: duplicate prior-year value and unique location. The same pattern appears in fraud screening, policy analysis, and entity quality checks where an item must satisfy several population-level rules before it contributes to a final metric.
pidtiv_2015tiv_2016latlonSELECT
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
)This is the standard solution. One grouped subquery finds prior-year values shared by multiple policyholders, and the other identifies locations that appear exactly once.
WITH shared_tiv AS (
SELECT tiv_2015
FROM insurance
GROUP BY tiv_2015
HAVING COUNT(*) > 1
),
unique_locations AS (
SELECT lat, lon
FROM insurance
GROUP BY lat, lon
HAVING COUNT(*) = 1
)
SELECT
ROUND(SUM(i.tiv_2016), 2) AS tiv_2016
FROM insurance i
JOIN shared_tiv s
ON i.tiv_2015 = s.tiv_2015
JOIN unique_locations u
ON i.lat = u.lat
AND i.lon = u.lonThe CTE version makes the two eligibility rules easier to inspect separately. That is helpful when you need to debug which condition excludes a specific row in a larger production dataset.
The subtle bug is mixing up duplicate and unique criteria. `tiv_2015` must be shared with at least one other row, while `(lat, lon)` must be unique. Reversing either condition changes the business meaning completely.
Because location uniqueness is defined by the full coordinate pair. Either column alone is not enough to identify whether two policyholders occupy the same place in this schema.
It teaches multi-stage eligibility filtering: using grouped subsets to define row populations before applying a final aggregation.
Use these guides when you want the fuller explanation behind the SQL pattern this challenge is testing.
Learn how GROUP BY transforms rows into summaries. Watch data collapse into groups with animations and master COUNT, SUM, AVG, and HAVING.
Turn multiple queries into one with conditional aggregation. Learn how CASE WHEN inside aggregate functions creates powerful single-query reports and pivot tables.
Unlock the power of nested queries. Learn when to use subqueries in SELECT, WHERE, and FROM clauses with practical, runnable examples.
If this problem exposed a deeper weakness, use a topic hub to move through a curated cluster instead of random article hopping.
Analytics
A practical cluster for cohorts, funnels, customer value, and the kinds of reporting questions teams actually ask every week.
Advanced SQL
Use this path when you are moving beyond beginner SELECT queries into CTEs, window functions, and conditional logic.
If you want the rotating practice flow, open this same question from the daily stream. If you want interview-style packaging, continue into the interview pool after this rep.
Return only the customers whose distinct purchased product set covers every product listed in the catalog.
Calculate overall friend request acceptance rate while separating request volume from accepted pairs cleanly.
Calculate contest participation rate by dividing each contest’s registrations by the total user base, not by contest-local counts.